Best Practices on updating tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,

Although there are several ways to accomplish, I am looking for a best
practice and understanding of why.

I have text boxes and combo boxes on a form. Combo boxes will pull data
from lookup tables and user will enter new data into the text boxes. All the
data on the form will be inserted into 1 or more tables. I would like to not
have record navigation buttons on the form but have only a Reset/Clear fields
and Save/Submit buttons. When Save/Submit is clicked upon, I would like an
insert query to execute. How is this best accomplished? The books I have
don't really seem to address this as they are too novice for me.


TIA,
Rich
 
What you are saying can be done, but......
The best practice is to have your form be bound to a table and the controls
be bound to columns in the table. For related tables, use sub-forms on the
main form. This is the way Access was designed to work since version 1 (a
long time ago).
If you try to use unbound fields and do your own INSERTS there are a host of
problems you will encounter (been there done that).
Also, where possible, avoid having users enter any data that could be chosen
from lookup lists. When users enter data, they make errors and you will end
up with du-plicates with all kinds of mispellings. Never allow users to enter
a date, always allow picking from a calendar.

Dorian
 
. When Save/Submit is clicked upon, I would like an
insert query to execute.
Click to expand...

Why?

This "unbound form" technique can be used if necessary, but it's far more
complex than simply using bound forms. Do you have some particular reason to
want to do things the hard way? If so, please let us know - the insert query
can certainly be done with a bit of work.

John W. Vinson [MVP]
 
Thank you both very much. You're being a great help. I did not realize that
using bound forms/controls was the best or easiest way. I am reading
"Microsoft Office Access 2003 from MS Press but it is not really showing me
the kind of things that I'm looking for. It focuses on displaying existing
data but not much (at least not yet) on inserting or updating.

So, on the form I've been discussing, I've got to insert the data into 1
table (inserting 2 values). The problem is that the cbo selects a value from
a lookup table so the DoCmd.Save will not work because I don't have it
pointing to the table/field that I need to save it to. Can I have a hidden
object on the form, set its value to that of the cbo, and point it at the
table/column that will be updated? Then issue tghe DoCmd.Save?

--Rich
 
I added a hidden text box and for the After Update event for the
cboCompanyName, I set the text box = to the cbo and then issue the
DoCmd.Save. Opening the table confirms that the data was saved to the table.
Is this generally a good approach? What's a good best practice or method
for validating the success of a query (update, insert, and delete)? In VBS,
you can check the values in Err.code, does the same exist in Access VB?

Thanks,
Rich
 
I added a hidden text box and for the After Update event for the
cboCompanyName, I set the text box = to the cbo and then issue the
DoCmd.Save. Opening the table confirms that the data was saved to the table.
Is this generally a good approach?
Click to expand...

Frankly... NO.

Your table should have a CompanyID field, bound to the combo box which will
store the CompanyID. It is neither necessary nor appropriate to also store the
company name. The company name should exist in the company table, and *only*
in the company table; you can *display* it on the form by simply using a combo
box with the company name as the first (or only) visible field. There's no
need to display the companyID unless it's meaningful to the user.

You can use unbound textboxes on the form, if you wish, to display other
fields from the combo - set their control source to =combobox.Column(n) where
n is the zero based index of the field. But you wouldn't ordinarily store this
data redundantly.
What's a good best practice or method
for validating the success of a query (update, insert, and delete)? In VBS,
you can check the values in Err.code, does the same exist in Access VB?
Click to expand...

If you're running the query from VBA code, yes:

Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
' create your action query
strSQL = "INSERT INTO <whatever>"
Set db = CurrentDb
' create an unnamed querydef object
Set qd = db.CreateQuerydef("", strSQL)
' execute the querydef object; if there's an error, raise the module's
' Error condition and jump to proc_error
qd.Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox Err.Number, Err.Description
Resume Proc_Exit

HOWEVER - with a bound form you do not *need* this; you don't need any code at
all; the form *will display what has been put into the table*. I never have
felt the need to put verification code to check that what's on the form is
actually in the table; after all, the form is just a window which displays
what's in the table (often with combos or other tools to display it in a
human-friendly way). What you see is what you got!

Honestly - *it is easier than you're making it*. If you're used to having to
do everything yourself in code, relax - let Access do the gruntwork; that's
what it's designed to do, and it does it quite well.

John W. Vinson [MVP]
 
So, on the form I've been discussing, I've got to insert the data into 1
table (inserting 2 values). The problem is that the cbo selects a value from
a lookup table so the DoCmd.Save will not work because I don't have it
pointing to the table/field that I need to save it to. Can I have a hidden
object on the form, set its value to that of the cbo, and point it at the
table/column that will be updated? Then issue tghe DoCmd.Save?
Click to expand...

See my response to your other message, but... just set the Control Source of
the combo box to the field into which you wish its Bound Column to be stored.
DoCmd.Save is *INAPPROPRIATE* and wrong - it saves design changes to the
structure of the form. The record will be saved automatically when you move
off the record, or close the form; you can (if you need the reassurance of the
extra step of clicking a button) use

DoCmd.RunCommand acCmdSaveRecord

to explicitly save the record to disk, but it's generally not needed.

John W. Vinson [MVP]
 
you can (if you need the reassurance of the
extra step of clicking a button) use

DoCmd.RunCommand acCmdSaveRecord

to explicitly save the record to disk, but it's generally not
needed.
Click to expand...

I much prefer:

Me.Dirty = False
 
Back
Top