Close Table on Form Close

[Jaxxamercy]

Hope someone can help this is drving me mad.

I have a form that is based upon a table when I close the table I wish to run
a make table query that overwrites the table on which the form is based. Then
I want to reopen the form to display the new records. This is fine is I do
each step manually. The problem occurs when I try to automate these actions
through macros or code. As follows

I close the form with DoCmd and open the make table query in the same way.
However it would appear that the recordset for the form doesn't close when
the form does as it always returns RT 3211 error.

This really anoying. Can anyone advise how to avoid this? I don't want to use
a second unbound form and I have tried delaying the code but to no avail.

Any help really appreciated!

 

[Linq Adams via AccessMonster.com]

We need to see the code you're trying to use to automate the process.

 

[Dirk Goldgar]

Hope someone can help this is drving me mad.

I have a form that is based upon a table when I close the table I wish to
run
a make table query that overwrites the table on which the form is based.
Then
I want to reopen the form to display the new records. This is fine is I do
each step manually. The problem occurs when I try to automate these
actions
through macros or code. As follows

I close the form with DoCmd and open the make table query in the same way.
However it would appear that the recordset for the form doesn't close when
the form does as it always returns RT 3211 error.

This really anoying. Can anyone advise how to avoid this? I don't want to
use
a second unbound form and I have tried delaying the code but to no avail.

Have you tried DoEvents:

DoCmd.Close, acForm, "YourFormName", acSaveNo
DoEvents
DoCmd.OpenQuery "YourMakeTableQuery"
DoCmd.OpenForm "YourFormName"

?
If that doesn't work, you might try adding a second DoEvents, or running a
little delay loop before running the query. Or you could detach the form
from its recordsource before closing it:

Forms!YourFormName.RecordSource = ""
DoCmd.Close, acForm, "YourFormName", acSaveNo
DoCmd.OpenQuery "YourMakeTableQuery"
DoCmd.OpenForm "YourFormName"

HOWEVER, is it really necessary to run this make-table query over and over
again? Couldn't you run a delete query, followed by an append query,
instead? For example:

With CurrentDb
.Execute _
"DELETE FROM YourTableName", _
dbFailOnError
.Execute _
"INSERT INTO YourTableName " & _
"SELECT * FROM SomeOtherQuery", _
dbFailOnError
End With

Forms!YourFormName.Requery

 

[Jeff Boyce]

You did say "...how to avoid this..." <g>

You've described how you are trying to do something (i.e., close a form,
make a table, open a form ...).

You've not described "what" you are trying to do. What will having a new
(made) table as a source for your form allow you (or your users) to do?

Why do you believe you need a (new) made table? Is there a chance a query
can provide the information you need? What information do you need?! What
information are you starting with?

More specific description may lead to more specific suggestions...

Regards

Jeff Boyce
Microsoft Access MVP