Brook said:
I do have a couple of questions. Where would I put the
code that you have suggested? And in what parts of the
code you suggested would I update with my updatequery and
or tblemaster?
Hi Brook,
Forms (and controls) have specific events
that fire off within the life of its "being."
For example, a command button has a click
event that fires when a user clicks on the button.
I understood (rightly or wrongly) that you wanted
to run an update query when you opened a form.
In the database window, select the Forms object,
then select your form, then click on Design.
Click on the Properties icon in menu (looks like a
sheet of paper being held by a hand). You should
now be looking at all the Properties of the form.
If you scroll down to bottom, you will see all the
events that can fire when you run this form....
On Current...
Before Insert...
After Insert....
Before Update...
After Update...
.....
On Open..... <--****
On Load....
On Resize...
On Close...
......
On Click....
On Dbl Click....
....
I assumed you wanted to run this update
query in the "On Open" event of your form.
Find this event's row and double-click in the
white space of the row. You should get the text
"[Event Procedure]" in the white space, plus a
"Build button" at the far right (a square button with
3 lower dots, or elipses, sp?).
Click on the Build button and you will be shifted to
the "Microsoft Visual Basic" window with preliminary
code that might look like:
Option Compare Database
Private Sub Form_Open(Cancel As Integer)
End Sub
If this is the first code for your form, the first thing
I would always do is type in the following under "Option..."
Option Explicit
this will insure that all your variables are properly
declared (won't let you misspell a variable, for
instance).
I gave you several choices of how you might
execute your query. If you choose to use DAO,
click on Tools/References in the top menu, and
verify (or scroll down and select) selection of
Microsoft DAO x.xx Object Library
If your update query was a stored query
whose name was "qryupdf1", this might be
the code for your Form_Open event
(change "qryupdf1" to name of *your* query):
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
CurrentDb.Execute "qryupdf1", dbFailOnError
Exit_Form_Open:
Exit Sub
Err_Form_Open:
MsgBox Err.Description
Resume Exit_Form_Open
End Sub
When done, click on Save icon in menu
(looks like floppy), then click on Debug/Compile..
in the top menu. Hopefully it will compile w/o
errors.
Then close the Visual Basic window.
Now, whenever you open this form,
the update query will be run.
Please respond back if I have misunderstood
or was not clear about something.
Good luck,
Gary Walter
:
Is there a way to run an update query upon opening a form
without the system confirming that you are about to run an
update query and asking if you want to proceed with update
(xxx) number of rows?
Hi Brook,
I don't recommend changing the settings above.
Instead, run your update query through Execute method
(using "dbFailOnError" so it doesn't "fail silently")
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
Dim strSQL As String
strSQL = "UPDATE MyTable SET [f1] = 1"
'DAO execute
CurrentDb.Execute strSQL, dbFailOnError
'ADO execute
'CurrentProject.Connection.Execute strSQL, dbFailOnError
'or if update query is a stored query (say "qryupdf1")
'CurrentDb.Execute "qryupdf1", dbFailOnError
Exit_Form_Open:
Exit Sub
Err_Form_Open:
MsgBox Err.Description
Resume Exit_Form_Open
End Sub
