How to run make table query on an active table used by a form?

[dpatel1682]

Can you please provide an example? I am still bit lost as to how do I do this. What I tried to do after the update is click on the "Refresh" button which will close the form, run the make table qry, and re open the form. Should I put the above steps in module and run that module on click ?

[dsatino]

Yes, move all the code from the click event(except the form close command) into a module.

On_Click close the form
In the forms on_close event, call the procedure that you moved to the new module.

I think this will work, but I've never tried it

[dpatel1682]

ok I tried that but it didn't work. I created a module with a function called mktbl which had maketable qry followed by open form command. It gave me the same error when it tries to execute maketable qry

Any other idea?

Thanks for your help

[dsatino]

Clear the on click event from the button and paste this into it:

Me.tbl_Raw_Data_by_Account_subform.Form.RecordSource = ""
DoCmd.RunMacro "mcr_mktbl_Raw_Data_by_Account"
Me.tbl_Raw_Data_by_Account_subform.Form.RecordSource = "tbl_Raw_Data_by_Account"
Me.Refresh
Me.Requery

It will get you buy the error, but this is really frowned upon.

[dpatel1682]

It worked. I used the following Code

tbl_Raw_Data_by_Account_subform.Form.RecordSource = ""

DoCmd.RunMacro "mcr_mktbl_Raw_Data_by_Account"

tbl_Raw_Data_by_Account_subform.Form.RecordSource = "tbl_Raw_Data_by_Account"

tbl_Raw_Data_by_Account_subform.Form.Refresh

tbl_Raw_Data_by_Account_subform.Form.Refresh