not too sure where to place OpenQuery code

[graeme34 via AccessMonster.com]

Hi could anybody help a newbie out!
I have a form that has a child form whose control source is a temp table
based on a make table query. Originally I tried to have the control source a
cross tab query but it turned out these are not editable.
I have the sub form accepting data now, my only problem is when exiting the
form I want to Docmd.OpenQuery on the make table query thus updating its
contents.
I have tried placing the code in all of the forms events, but it keeps coming
back with the runtime error "3211" The databaseengine could not lock the
table "tblTempGoodsRecieved" as it is in use by another person. I'm assuming
this is because the Form "frmReceivePurchaseOrder" is still open.
The code I'm trying to implement is:
DoCmd.OpenQuery "qryMakeReceiveTable"
DoCmd.Close acQuery, "qryMakeReceiveTable"
I have tried placing this in the Onclose, on deactivate events, on lost focus
but none seem to prevent the run time error, any suggestions??
Also is there away to prevent the warning message coming up about old temp
table being deleted before running the make table query??
Thanks.

 

[Douglas J. Steele]

If qryMakeReceiveTable is an Action query (INSERT INTO, UPDATE, DELETE or
SELECT ... INTO), you don't want to use OpenQuery on it.

Instead, use the Execute method of the Query object:

CurrentDb.QueryDefs("qryMakeReceiveTable").Execute, dbFailOnError

If you're using Access 2000 or 2002, you might have to add a reference to
DAO to make this work. With any code module open, select Tools | References
from the menu bar, scroll through the list of available references until you
find the one for Microsoft DAO 3.6 Object Library, and select it.

 

[graeme34 via AccessMonster.com]

hi Douglas
Thank you for your help, I must be doing something wrong still...
I have placed the code in the On close event...but I'm getting a compile
error
"Wrong number of arguments or invalid property assignment"
Do I need to declare anything anywhere for the code to work??

If qryMakeReceiveTable is an Action query (INSERT INTO, UPDATE, DELETE or
SELECT ... INTO), you don't want to use OpenQuery on it.

Instead, use the Execute method of the Query object:

CurrentDb.QueryDefs("qryMakeReceiveTable").Execute, dbFailOnError

If you're using Access 2000 or 2002, you might have to add a reference to
DAO to make this work. With any code module open, select Tools | References
from the menu bar, scroll through the list of available references until you
find the one for Microsoft DAO 3.6 Object Library, and select it.

Hi could anybody help a newbie out!
I have a form that has a child form whose control source is a temp table

[quoted text clipped - 20 lines]

table being deleted before running the make table query??
Thanks.

 

[graeme34 via AccessMonster.com]

Hi Douglas

Ive managed to use your code, had to alter it to

CurrentDb.QueryDefs("qryMakeReceiveTable").Execute (dbFailOnError)

you'll have to excuse me if that is what you meant but like I said still a
newbie to this

But when I ran this code I got the message along the lines of table already
exists.
Therefore I had to use the line
DoCmd.DeleteObject acTable, "tblTempGoodsReceived"
Before your suggested code, but that left me back to square one as the code
module is behind the form that uses this as its control source. Are there any
events that can be used when the form is completely inactive??
Or am I going to haveto place this code on the command button that opens the
form, not ideal as I wanted to update temp table on closing the form.

I'm open to all suggestions....

hi Douglas
Thank you for your help, I must be doing something wrong still...
I have placed the code in the On close event...but I'm getting a compile
error
"Wrong number of arguments or invalid property assignment"
Do I need to declare anything anywhere for the code to work??

If qryMakeReceiveTable is an Action query (INSERT INTO, UPDATE, DELETE or
SELECT ... INTO), you don't want to use OpenQuery on it.

[quoted text clipped - 13 lines]