I have a main form with 4 sub forms attached. One of the sub forms is from a table that has calculations based on variables from the main form and 2 other tables. I have built queries and bundled them together into a macro to update this calculations table. This macro runs fine on it’s own.
I would like to have the macro run OnTab or OnClick etc. from the main form. When I set those procedures to run I get an Action Failed message. When I press Halt, I get an error box that says; ‘ The database engine could not lock table ‘Costs and Proceeds Calcs’ because it is already in use by another person of process.” I know this is happening because I have the table open by way of the sub form.
There has to be a way to disable the table while the macro is running so that the updates are visible on the sub form after the macro runs. I have looked everywhere and tried many things to get this to work but I am stuck.
Can anyone point me in the right direction? Once I get over this hurdle, this project is done.
OpenQuery Invoice Sums Query Step 1
OpenQuery Recovery Sum Query Step 2
OpenQuery Calc Query Step 3
Close Closes Calc Query Step 3
OpenQuery Calc Query Step 4 (this is the update query that updates the table attached to the subform)
Close Closes Closes Calc Query Step 4
OpenQuery Dispo Query (this query also updates another table on another subform linked to the main form)
Close Dispo Query
Is there any other action I can insert somwhere that will let this run while the associated tables are open?
docmd.OpenQuery "Invoice Sums Query Step 1"
docmd.OpenQuery "Recovery Sum Query Step 2"
docmd.OpenQuery "Calc Query Step 3 "
' Close Closes Calc Query Step 3 'I don't think these closes are needed if they are closing update queries
docmd.OpenQuery "Calc Query Step 4" ' (this is the update query that updates the table attached to the subform)
'Close Closes Closes Calc Query Step 4
docmd.OpenQuery "Dispo Query" ' (this query also updates another table on another subform linked to the main form)
'Close Dispo Query
Just some input from a rookie here also. But I did something in my database that helped me out and wonder if it will help out mpm32. I took my Macros, a group at a time, coverted them to Code (wizard does that for you), then created a function from it and called it from a procedure. Reason: I had several macros processing my data for me and was a bit hairy, so since that was something that was needed in 2 other situations in my database I created a SubProcedure to ProcessData and then just called in the Function 'Processing'. And it really does work well. Also you can creat the Sub and have it all processed right there but instead of having to use the "SetWarnings" thingy, you can do this: CurrentDb.Execute "QueryName" That takes care of the warnings without having to remember to reset them when done.