Access run update query in form

docmd.setwarnings = false
 docmd.runquery "Qerey1"
docmd.setwarnings = true

put it in the event you want it to run from.

Mike

Or, use:

CurrentDB.Execute "Update...."
                                  ^-- enter your SQL here

instead of docmd.runquery "Qerey1"

Mike

You need to decid when yyou want the query to be executed, i am assuming after the form is closed in which case you can use the docmd as specified above in the OnClose Event of the forms property page.

re:> Form 1 queries and returns times.

By 'returns' do you mean it updates a text box? If so, OnChange event of the text box is where you will be putting your code so your "Update query updates time table with times" and "Form 2 pulls data from time table and displays".

If not, the other option is using timer even of the form with timer set at 2000 (2 second). An in order for the code is fire only when it is needed, you need to add:

Public bolDontRun As Boolean

in the timer:

If bolDontRun = False Then
bolDontRun = True
CurrentDB.Execute "Update...."
End If

And somewhere in your form 1, you need to set bolDontRun = False to signal timer to fire the code again.

Mike

BTW, put following in a module, under module tab:

Public bolDontRun As Boolean

The form and a subform is open.  Once the user types in the last time (time out), the subform requeries and applies the new values.  At that time, on it's refresh, I would like the update query to run.  This query will update a table that another form will be pulling values from.

So, I guess I would like the update query to run when the user clicks on the subform and after the subform is updated.

I hope this helps....I give the above suggestions a try.

Happy New Year to everyone.  I appreciate the help!

Heather

In that case use the onAfterUpdate Event for your subform, then requery the main form to see the latest resultset.  

Going over parts of your last post:

re:> Once the user types in the last time (time out), the subform requeries and applies the new values.

The key part her is "subform requeries". The event I suspect you need to try is the On Current event of the subform. And, if the user enters the date to the subform:

CurrentDB.Execute "Update...."    'in On Current event of the subform

It will be nice for you post the SQL of your query to see if it needs update using a value from the form or the subform.

Happy New Year to you as well.

Mike

Here is the sql scripts...by the way, I tried the above code in various event locations and nothing worked.  Not sure if I was using the correct info.  For the following:

docmd.setwarnings = false
 docmd.runquery "Qerey1" (I used the name of the query "Update TimeUsed Query"
docmd.setwarnings = true

Maybe I should have used the control name...not sure if the query would have one.

Here are the queries:

Time Used: Main Form
SELECT ParentInfo_TB.ParentID, TimeUsed_TB.ParentID, ParentInfo_TB.ParentID, ParentInfo_TB.Parent_LastName, ParentInfo_TB.Parent_LastName, TimeUsed_TB.Time_In, TimeUsed_TB.Time_Out, ([Minutes]\60 & Format([Minutes] Mod 60,"\:00")) AS Total, TimeSerial(Hour([Total]),(CInt(Minute([Total])/15)*15),0) AS RoundTotal, DateDiff("n",[Time_In],IIf([Time_Out]<[Time_In],1,0)+[Time_Out]) AS Minutes, Format(([Minutes]\60)+(CInt(([Minutes]-(60*([Minutes]\60)))/15)*15/60),"Fixed") AS TimeFRMT, *
FROM ParentInfo_TB INNER JOIN TimeUsed_TB ON ParentInfo_TB.ParentID = TimeUsed_TB.ParentID
ORDER BY TimeUsed_TB.ParentID;


Time Query: Subform
SELECT [Time Query].TimeUsed_TB.ParentID, [Time Query].Parent_LastName, [Time Query].TimeFRMT, Payment_tbl.PaidTotal_Hours, [TotalHours_Left]-[TimeFRMT] AS [Left], Payment_tbl.TotalHours_Left
FROM [Time Query] INNER JOIN Payment_tbl ON [Time Query].ParentInfo_TB.ParentID = Payment_tbl.ParentID;

Update Query (Background Query):
UPDATE (ParentInfo_TB INNER JOIN [Used Time Query] ON ParentInfo_TB.ParentID = [Used Time Query].ParentID) INNER JOIN Payment_tbl ON ParentInfo_TB.ParentID = Payment_tbl.ParentID SET Payment_tbl.TotalHours_Left = [Left];

Thanks again,

Heather

In the events you try, have

MsgBox "subform events fired"   'using the event name as it applies

This is to make sure, in requerying of the subform's on current event it fires. If it doesn't fire (which it should in requeryin it) we have to see what the problem is.

After we located the event, use

'docmd.setwarnings = false
 docmd.runquery "Qerey1" (I used the name of the query "Update TimeUsed Query"
'docmd.setwarnings = true

to see if there is some kind of error with the running of the query. We can remove the single qoutes later.

Mike
 

Hi Mike,

I did the above suggestion and received an error in the code.  It stopped at the docmd.runquery, highlighting the runquery word.  I noticed that when I typed in the code earlier, it did not show up as an option.

Thanks,

Heather

Hi Mike,

I got the message box to pop up and fire the query.  I had to replace the runquery with the "Update query statement."  That worked!! BUT, I it is firing the update query when the form is open.  I tried to move the update query around to something other than CURRENT like on click, or after update, etc; but it doesn't seem to be firing the update query when clicked.

Any suggestions as to where I can put the query and why it is on only working with the on Current event?

Thanks,

Heather

Thanks so much!  That worked.  I have other issues now with one of my queries.

Have a great day!

Heather

Thank you fot the points and the grade. If will be happy to help out wiht any other question I could handle it.

Mike