Re: Best Practices on updating tables
- From: John W. Vinson <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 30 Jul 2007 23:51:01 -0600
On Mon, 30 Jul 2007 19:50:01 -0700, rich <rich@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
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]
.
- References:
- Re: Best Practices on updating tables
- From: John W . Vinson
- Re: Best Practices on updating tables
- Prev by Date: First of a series of questions. 1. Table vs. Questions
- Next by Date: Re: Best Practices on updating tables
- Previous by thread: Re: Best Practices on updating tables
- Next by thread: Re: Best Practices on updating tables
- Index(es):
Relevant Pages
|
