run query/SQL in the background

[Guest]

Hi. I have created two SELECT queries and one APPEND query, and I want all of
them to run behind the Command Button of a form automatically. So far I have
used the code listed below:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryItemsInStock"
DoCmd.OpenQuery "qryPercentageOfStock"
DoCmd.OpenQuery "qryOrder"
DoCmd.SetWarnings True
DoCmd.GoToRecord , , acNewRec

It opens "qryItemsInStock" and "qryPercentageOfStock" but then a message box
appears "You can't go to specified record". Any idea why this happens?
Or what about RunSQL, is that better to use?

 

[Brendan Reynolds]

Because you haven't supplied any values for the optional first and second
arguments of the GoToRecord method (the ObjectType and ObjectName arguments)
it will attempt to go to the new record of the active data item, which in
this scenario will be the query 'qryPercentageOfStock'. I'm guessing that
this is a totals query, and therefore does not have any new record row,
hence the 'can't go to specified record' message.

If you want to position the *form* at the new row, assign values to those
optional arguments like so ...

DoCmd.GoToRecord acDataForm, Me.Name

I can't comment on the OpenQuery vs. RunSQL question, as I never use either
of them. I use forms or reports to display data, users never see select
queries, and I use the DAO or ADO Execute methods to execute action queries.

 

[Brendan Reynolds]

I forgot the third argument in my example ...

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec