1st VBA sequence

[Jeff C]

I am trying to build my first automatic sequence in VB. I will have two or
three update queries following a delete query and finish with an append
query. My question: Is the following the correct way to enter the code in
the “on click†event property of a command button? Thanks for your help.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "qry_delete"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "qry_update1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "qry_update2"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "qry_update3"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "qry_append1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[Brian]

Looks good. I usually do a couple of other things:

1. Bypass the setting of the variable and pass the query name through
directly to the OpenQuery (that's just preferential, though - my choice as a
programmer)
2. Leave out the optional arguments unless you need them
3. Enter DoCmd.SetWarnings False before the queries & True afterward to
ensure Access does not wait for user to approve each action query.

Something like this:

Button1_Click
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_delete"
DoCmd.OpenQuery "qry_udpate1"
DoCmd.OpenQuery "qry_udpate2"
DoCmd.OpenQuery "qry_udpate3"
DoCmd.OpenQuery "qry_udpate4"
DoCmd.OpenQuery "qry_append"
DoCmd.SetWarnings True

 

[Jeff C]

Is there a need for...and a method of pausing between these functions to
allow time for the actions to occur?

 

[Jeff C]

By the way, Thank you Brian, I am self taught with this stuff only with the
help of this discussion group and the others like you who generously offer
guidence. Making a command button open a function or query using the wizard
and then trying to copy what the wizard does in VB is how I have been
approaching this, thus the extra statements in my example, which I have now
learned from you are not necessary.

I am guessing that these are the unnecessary line in your opinion?

Dim stDocName As String
stDocName = "qry_delete"

What I am buiding is a command button to retrieve a text file generated by
another program, import it, reformat it, and then append the results to an
exisiting table...and it is just amazing that Access will do this.....with
the click of a button....AMAZING

Thanks again.

 

[Guest]

Another way to do that is like this:

CodeDB.Execute "qry_update1"
CodeDB.Execute "qry_update2"
CodeDB.Execute "qry_update3"
CodeDB.Execute "qry_update4"

Using CodeDB is faster than DoCmd, sometimes much faster,
because CodeDB does not use Access except to connect to
the database. It also does not have any Warnings or Error
Messages for the same reason, and also does not allow you
to directly reference Control values for the same reason.

To get Error Messages, you would normally write like this
if you have a DAO reference (Access 2003):

CodeDB.Execute "qry_update1", dao.dbFailOnError

or like this if you don't have a DAO reference (A2K):

CodeDB.Execute "qry_update1", 128


Errors will now through to your error handler
(Err_Command0_Click), which is often what you want.

When you start to write more complex subroutines, you may
wish to move them out of the Click Event, so that you can
share the code between multiple forms and controls, and so
that you can origins your code differently from the way you
organise your forms:

Private Sub Command0_Click()
Call subAppend()
End Sub

Notice I have moved all the other error handling to the sub, so
I've simplified the form by also deleting the error handling. In
more complex situations you may need error handling code in
both places.

(david)

 

[Ian B]

The groups and all the contributors are a marvellous aid.
I've worked with Access since V1.1 and never knew until today there was such
a command as CodeDB!.
Probably wasn't in V1!

Thanks all

Ian B

 

[Brian]

Yes. Those are the two lines. I usually do not declare a variable unless I
will be using it more than once. I guess I look at it more in terms of
efficiency of programming than I do in efficiency at runtime, sometimes.
There is probably an Access/VB guru out there who could explain why it might
be better to always declare a variable & pass it through (which, as you
pointed out, is the way the lion's share of Help file examples and forum
posts are structured). I'm just a self-taught Access programmer also, so some
of the finer (or perhaps I should say more advanced) points escape me until
an expet speaks on the subject.

In response to your intermediate post, I have never found a need to provide
any pause between action queries, so I assume Access runs its queries
synchronously - each one being executed before the next begins.

Having said this, the same is not true of Access in relation to other
programs it calls. If you shell out to Excel (for example, to have an
autoexec Excel macro pre-format a delimited file before a TransferText that
will import the results into Access), Access & Excel run asynchronously. The
times I have used this, I had the Access code shell to Excel, then loop
through a "Do While ..." checking for the existence of the file before
proceeding to the import portion of the code (TransferText). Otherwise,
Access moves on to the import process immediately after shelling to Excel and
before Excel is done creating the import file.