Multiple actions on one event

[dbar10]

I am going brain dead. Can you please help? I have two update queries that need to run AfterUpdate on my form. The queries work great when I open them manually but I need to run them both on the "afterupdate" event on my subform. I also need the subform to requery to update the data on the form. So there is 3 actions 2 Update queries and Requery of subform named "Units Worked Billing Query subform". Below is what I tried but it isn't working at all:

Private Sub Hours_Worked_AfterUpdate()
On Error GoTo Err_Hours_Worked_AfterUpdate

Dim stDocName As String

stDocName = "Units Worked Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "Units Worked Query2"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Forms![Units Worked Billing Query subform].Requery

Exit_Hours_Worked_AfterUpdate:

Exit Sub

Err_Hours_Worked_AfterUpdate:
MsgBox Err.Description
Resume Exit_Hours_Worked_AfterUpdate


End Sub

The second query depends on the results of the first one.

As you have probably guesssed, I don't know much about writing VB Code. Thanks again, Doug

 

[dhookom]

I'm not sure about your update queries since you didn't provide the sql view and we don't know if they actually ran or if there was an error.

I do know that a subform on another form can't be referenced like:
Forms!sfrmMySubform
You would need to fully qualify the subform and its parent I believe like:
Forms!frmMainForm!sfrmCtrlName.Form.Requery
or
Forms!frmMainForm!sfrmCtrlName.Requery


Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Thanks for your response Duane. I have included the two queries that have to run.

Units Worked Query1
UPDATE [Rates Billing Query] INNER JOIN ([Worker table] INNER JOIN (Services INNER JOIN [Units Worked Table] ON Services.ServiceID = [Units Worked Table].Service) ON [Worker table].WorkerID = [Units Worked Table].WorkerID) ON [Rates Billing Query].ServiceID = Services.ServiceID SET [Units Worked Table].Units = [Hours Worked]/[UnitConv]
WHERE ((([Units Worked Table].Units)=0) AND (([Units Worked Table].Date)>=[Forms]![Billing Work Form]![MOStart] And ([Units Worked Table].Date)<=[Forms]![Billing Work Form]![MOEnd]) AND (([Rates Billing Query].EffecDate)<=[Forms]![Billing Work Form]![MOEnd]) AND (([Rates Billing Query].[Expire Date])>=[Forms]![Billing Work Form]![MOStart]) AND (([Rates Billing Query].Program)=[Forms]![Billing Work Form]![ProgChoice]));

Units Worked Query2

UPDATE [Rates Billing Query] INNER JOIN ([Worker table] INNER JOIN (Services INNER JOIN [Units Worked Table] ON Services.ServiceID = [Units Worked Table].Service) ON [Worker table].WorkerID = [Units Worked Table].WorkerID) ON [Rates Billing Query].ServiceID = Services.ServiceID SET [Units Worked Table].AmtBilled = [DisplayChg]*[Units], [Units Worked Table].AmtPaid = [DisplayPaid]*[Units]
WHERE ((([Units Worked Table].AmtBilled)=0) AND (([Units Worked Table].AmtPaid)=0) AND (([Units Worked Table].Date)>=[Forms]![Billing Work Form]![MOStart] And ([Units Worked Table].Date)<=[Forms]![Billing Work Form]![MOEnd]) AND (([Rates Billing Query].EffecDate)<=[Forms]![Billing Work Form]![MOEnd]) AND (([Rates Billing Query].[Expire Date])>=[Forms]![Billing Work Form]![MOStart]) AND (([Rates Billing Query].Program)=[Forms]![Billing Work Form]![ProgChoice]));

Query2 needs the results of Query1 to function. It seems as though Query2 is running before Query1 is done. When I run these Queries in order manually, I get the desired results. But I need them to run from the AfterUpdate event when I add the Hours worked. Can you help me? Thanks

 

[dbar10]

I changed the Form requery line like you suggested. But now I get an error message that says "You must save the current field before you run the Requery action". I don't know what it means.

 

[dhookom]

I'm not sure what is happening. I would try put a msgbox between the first and second update queries. Are your update queries updating the record source of the subform?

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Yes they are updating the Units Worked table. The Units Worked Billing Query displays the results of that table. What about the save the message: "You must save the current field before you run the Requery action". How do I do this?

 

[dhookom]

I'm not sure why you are using a query to update a record in your subform. You should be able to just clone the subform's recordset and update it.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

I tried what you said ( at least the best I understood with no difference). But I think I have figured out that I have to leave the Hours Worked field and changed the query to run when it has GotFocus of the next field. Now my problem is that I have to requery the main form before the Update query will do its thing. I have written this statement:

Private Sub Hours_Worked_AfterUpdate()
Forms![Billing Work Form].Requery
Me.[Units Worked Billing Query subform].SetFocus
DoCmd.GoToRecord acActiveDataObject, , acLast

I get error that program can't find the field in my expression. When the requery happens my curser ends up at the first field in the the main form. I need it to return to the subform "Units Worked Billing Query subform" at the last record and in the "Units" field. Can you help? Thanks

 

[dbar10]

Duane, I just realized that by requering the main form I also revert back to the 1st record in that form. How can I requery the main form and return back to the record I was working on previous to the requery? I am sorry I am being a pain. I really appreciate your help more than you know. Doug

 

[dhookom]

Am I correct in assuming that most of what you need to update is in the current forms that are open? If so, I guess I would try to use the forms' recordsets in some code to do all the updating. There wouldn't be any update queries and/or requerying of forms.

I don't understand fully what your specifications are so it's difficult to suggest a solution.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

I am stuck. What can I get to you so that you can help me? Please!!! The database is complicated as I am entering billing records based on the hours that workers work then that has to be checked against rates that apply from the Rates Billing query for that time period. then the units price entered in the subform * the units worked. that's what I am updating. The units based on the hours worked and the price per unit * the units worked. The units are different depending on the service. Some are 15 min. Others are hours or days. I am having a lot of fun with tnis. I can send you what ever you need to help. Thanks again. Doug

 

[dbar10]

Another issue is that the main form has to be requeried because other subforms on the main form are updated according to data entered on the [Units Worked Billing Query subform]. I don't know any way around it. there are 15 subforms on the main form. The [Units Worked Billing Query subform] effects the results of these other subforms. Can you please tell me how I could requery the [Billing Work Form](Main form) and get back to the main record that I was working on and the last record of the [Units Worked Billing Query subform] at the units field? I know that usually this could be handled different but there are too many other factors involved, I think. I would be willing to send you the entire database if need be. Doug.