Requery subform from new datatbl

[samonwalkabout]

I have a complex Form with subform. The subform is based on a qry which pulls data from a temptbl. I want to be able to re-create the temptbl and then requery the subform. Access wont allow me to delete the old temp table as its locked in use on the subform.

Is there some way of disconnecting the SQL connection of the subform to the table for a few seconds so i can run my code and recreate the table?

Many thanks

Sam

 

[Rob.Mills]

Do you really need to delete the table and recreate it? Can you just delete the data and then insert new data?

If you have to delete the table then use code to first delete the recordsource of the subform, delete the table, recreate the table, and then set the recordsource of the subform again.

 

[samonwalkabout]

Yep , im pulling a filtered set of data from a server table to a local table. This local table is the source for the form, so that the user is filtering less information on the form itself (some speed issues for networks in some sites). Deleting the table seems to be a neater solution as some data formating is done at the create table stage.

Can you point me in the direction of the code to delete the record source from the subform?

 

[Rob.Mills]

If you've got some button on the main form that's calling this code then do this:

Me!subformName.Form!RecordSource=""

Update the table

Me!subformName.Form!Recordsource = "TableName"