Error message after deleting query by vba

[zx3]

Hi all,



I am working on this project using a form to search for a member, in form 1 when putting in memno (table1) in the box next to the search button and click search, the respective member's name will appear on the listbox below and the details of that member will be listed in the detail section of the form. When user click the member's name in the listbox, the subform will show its score and allow user to put notes which in turn update Table:sports.

So far everything is working as expected, but when user try to close the form, a message will pop up because during the update of the subform by vba a temporary query is deleted, and access does not seem to notice that, here's the screen shot of that message.

Please note that the message does not allow user to close the form, to close the form i go to the vba of that form, comment out a line and save it, come back to close the form again, the message show again then it will close.

Please note also if you enter a 4 in the search box, which is one of the values in memno column of table1, the error message will be exactly the same as shown above.

I tried so many things to get rid of that but i just can't, please help.

And here is the file myDb.accdb

[davegri]

zx3-davegri-v01.zip

See if this works for you. You don't need a different named query for each member. Delete the query and recreate as necessary for each member.
I added OPTION EXPLICIT to modules and dimmed all orphan variables. Added DAO. to file related objects.
Added a form_open event to clear out some control values.
Commented out some unnecessary code.

[zx3]

zx3-davegri-v01.zip

See if this works for you. You don't need a different named query for each member. Delete the query and recreate as necessary for each member.
I added OPTION EXPLICIT to modules and dimmed all orphan variables. Added DAO. to file related objects.
Added a form_open event to clear out some control values.
Commented out some unnecessary code.

Thanks davegri for your quick reply, it works great!!!

And i improved it a bit to get rid of leftover tmpqy query and the layout saving message prompted at the closing of the form.

Here's the improved version based on davegri's suggestions/improvements zx3-davegri-v02.zip

It is really great that i can get to this point so quick and easy, thanks again.

[davegri]

Glad to help. Good luck with the rest of your project!