Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: Forms, Subforms and update queries

    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.

    Thanks

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    My solution would be to change the macro to vba. vba modules usually are easier to debug. I only use macros when a toolbar is involved.

  3. #3
    Join Date
    Feb 2004
    Posts
    6
    I do not know VB. I can read code and determine what it is doing. And, maybe make changes and copy existing code. So, I am lost there.

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Well then what does your macro do? How many steps are there? Can you list the first few functions?

  5. #5
    Join Date
    Feb 2004
    Posts
    6

    Macro Functions

    SetWarnings
    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?

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Changed to vba:

    docmd.SetWarnings 0
    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
    docmd.setwarnings -1

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Wink Macro Functions

    jmrSudbury,

    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.

    Bud (on a clear day I can see.......)

  8. #8
    Join Date
    Feb 2004
    Posts
    6
    Thanks, Bud. That may help me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •