Best Practices on updating tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rich - 30 Jul 2007 22:38 GMT

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

Reply to this Message

mscertified - 30 Jul 2007 23:38 GMT

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

> Hello All,
>
[quoted text clipped - 11 lines]
> TIA,
> Rich

Reply to this Message

John W. Vinson - 30 Jul 2007 23:52 GMT

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

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]

Reply to this Message

rich - 31 Jul 2007 03:12 GMT

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

> >. When Save/Submit is clicked upon, I would like an
> >insert query to execute.
[quoted text clipped - 7 lines]
>
> John W. Vinson [MVP]

Reply to this Message

rich - 31 Jul 2007 03:50 GMT

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

> 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
[quoted text clipped - 22 lines]
> >
> > John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 31 Jul 2007 06:51 GMT

>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?

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?

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]

Reply to this Message

John W. Vinson - 31 Jul 2007 06:53 GMT

>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?

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]

Reply to this Message

David W. Fenton - 31 Jul 2007 20:51 GMT

> you can (if you need the reassurance of the
> extra step of clicking a button) use
[quoted text clipped - 3 lines]
> to explicitly save the record to disk, but it's generally not
> needed.

I much prefer:

Me.Dirty = False

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message