Run afterupdate on subform afterupdate on mainform? (1 Viewer)

[rainman89]

Run afterupdate on subform by running afterupdate on mainform?

Hi all,
Is it possible to fire the afterupdate event of a control on a subform by firing it from an afterupdate of a textbox control in the mainform?

i have a combobox on my subform that runs an update based on values in the mainform which is all fine and dandy until someone wants to edit an entry. they edit their changes in the main form and pffttt nothing happens to show the update in the subform. requerying doesnt work, because its not based on something that needs to be queried.

is there a docmd.runcommand firethisevent or something like that?

or am i going to have to create a macro which i dont know how to do?

 

[rainman89]

Anyone know if this is possible?

 

[rainman89]

still stuck... anyone?

 

[boblarson]

Ray, you will probably want to requery the combo box on the subform but you will probably need to use the after update event of each control that affects the combo. So, if you have two text boxes, let's say, that affect the combo then you would need in the after update event of each text box:

Me.YourSubformContainerName.Form.YourComboBoxName.Requery

 

[rainman89]

Requerying does not work because the afterupdate of the combo is where the code is run to calculate some time and divide it between cases.

theres no docmd.runcommand dothisevent then is there?

 

[boblarson]

Put the code you need in the combo's after update event in a public standard module and call it from the combo but then you can also call it from your other events.

 

[rainman89]

Can i use ME in modules? or am i going to have to be explicit in my control references?

 

[rainman89]

Here is the code... ive never created a module before so im at a loss when it comes to these. ive been getting by on all VB up until now... sad.

DoCmd.RunCommand acCmdSaveRecord
Dim intervalminutes As Double
Dim intervalhourminutes As Double
Dim casecount As Integer
Dim strsql As String
If (Me.Parent.StartTime & "" <> "" And Me.Parent.EndTime & "" <> "") Or (Me.Parent.ExpenseHour & "" <> "" And Me.Parent.ExpenseMinute & "" <> "") Then
         casecount = Nz(DCount("*", "tbl_ExpenseCase", "[ExpenseID]=" & Nz(Me.ExpenseID, 0)), 0)
        If casecount > 0 Then
            'MsgBox "Case Count is " & casecount, vbInformation
            Me.Dirty = False
            intervalminutes = (Nz(Me.Parent.EndTime) - Nz(Me.Parent.StartTime)) * 24 * 60
            intervalhourminutes = Nz(Me.Parent.ExpenseHour) + Nz(Me.Parent.ExpenseMinute / 60)
            MsgBox "Total Minutes is " & intervalminutes, vbInformation
            MsgBox "Total Hours and Minutes is " & intervalhourminutes, vbInformation
            strsql = "Update tbl_ExpenseCase Set ExpenseCaseMinutes= " & ((intervalminutes / casecount) / 60) + (intervalhourminutes / casecount) & " WHERE [ExpenseID]=" & Me.ExpenseID & ";"
            DoCmd.SetWarnings False
            DoCmd.RunSQL strsql
            DoCmd.SetWarnings True
            Me.Refresh
        End If
Else
    MsgBox "Cannot enter minutes. You must input the times first.", vbInformation
    DoCmd.RunCommand acCmdDeleteRecord
End If

 

[boblarson]

Can i use ME in modules? or am i going to have to be explicit in my control references?

No, but you can pass the form name to the function. I'll have to address this after work if nobody else jumps in beforehand.

 

[rainman89]

got a reference on creating modules?

 

[boblarson]

Ray -

Sorry it has taken so long to get back to you. I had to wait until I was off work.

1. Go to the database window

2. click on MODULES

3. Click NEW

4 Go to the name and name it something like basMain or modMain (or anything else you would want to name it).

5. Go to just below Option Compare Database (or Option Explicit, if you have it in there).

6. Start typing your function:

      Public  Function DoUpdates(strForm As String) As Boolean
          ' Put your code that needs to be run here
          ' You can refer to the form by using Forms(strForm)
          ' Set the function equal to True if it process correctly ( DoUpdates = True)
      End Function

7. Call the function from the form by using

Call DoUpdates(Me.Name)

8. Call the function from another form by using

Call DoUpdates(Forms("YourFormNameWithComboHere"))

Hope that helps

 

[rainman89]

im getting a type mistmatch error on

Call DoUpdates(Forms("frmTimesheetSub2"))

so i removed the call and now im getting a

compile error: argument not optional

here is the code from the function.

Public Function DoUpdates(strForm As String) As Boolean
DoCmd.RunCommand acCmdSaveRecord
Dim intervalminutes As Double
Dim intervalhourminutes As Double
Dim casecount As Integer
Dim strsql As String
If (Forms!strForm.StartTime & "" <> "" And Forms!strForm.EndTime & "" <> "") Or (Forms!strForm.ExpenseHour & "" <> "" And Forms!strForm.ExpenseMinute & "" <> "") Then
         casecount = Nz(DCount("*", "tbl_ExpenseCase", "[ExpenseID]=" & Nz(Forms!strForm.ExpenseID, 0)), 0)
        If casecount > 0 Then
            'MsgBox "Case Count is " & casecount, vbInformation
            'Me.Dirty = False
            intervalminutes = (Nz(Forms!strForm.EndTime) - Nz(Forms!strForm.StartTime)) * 24 * 60
            intervalhourminutes = Nz(Forms!strForm.ExpenseHour) + Nz(Forms!strForm.ExpenseMinute / 60)
            'MsgBox "Total Minutes is " & intervalminutes, vbInformation
            'MsgBox "Total Hours and Minutes is " & intervalhourminutes, vbInformation
            strsql = "Update tbl_ExpenseCase Set ExpenseCaseMinutes= " & ((intervalminutes / casecount) / 60) + (intervalhourminutes / casecount) & " WHERE [ExpenseID]=" & Forms!strForm.ExpenseID & ";"
            DoCmd.SetWarnings False
            DoCmd.RunSQL strsql
            DoCmd.SetWarnings True
        End If
Else
    MsgBox "Cannot enter minutes. You must input the times first.", vbInformation
    DoCmd.RunCommand acCmdDeleteRecord
End If

 

[boblarson]

I can't run through the code today (I'll be at a musical practice after work) so I'll start you off by sayhing you're calling the function wrong. It isn't

Call DoUpdates(Forms("frmTimesheetSub2"))

it would be this:

If called from the form where the code is on it would be:
Call DoUpdates(Me.Name) ' no change to that necessary it will get the name of the form

or, if you are passing the name of the subform, but the code is on the main form it would be:

Call DoUpdates("frmTimesheetSub2")) or maybe
Call DoUpdates("Forms!frmMain.frmTimesheetSub2"))


Where the value in Controls

 

[rainman89]

ok so the combo box is on the subform and i want to call the code from the afterupdate on the mainform so i should do

because it is dependent on the boxes in the main form

so i think i should do
Call DoUpdates(Me.Name)

but when u say "the code is on the main form it would be:" i thought that since it was a module it isnt considered to be on any form?

see i really dont understand modules....

and do you play the TUBA??? or the xylophone??
or is this a rockband?

 

[boblarson]

ok so the combo box is on the subform and i want to call the code from the afterupdate on the mainform so i should do

because it is dependent on the boxes in the main form

so i think i should do
Call DoUpdates(Me.Name)

but when u say "the code is on the main form it would be:" i thought that since it was a module it isnt considered to be on any form?

see i really dont understand modules....

Ray - check out this here:
http://www.functionx.com/vbaccess/index.htm
Especially lesson 5. Also, just an FYI http://www.functionx.com has a whole bunch of great tutorials. You might pack that away for your toolbox.

and do you play the TUBA??? or the xylophone?? or is this a rockband?[/

actually, I do play the tuba, but not for my church's worship team (which is what the rehearsal is for). I will be playing Electric Bass this weekend for church, but I also play guitar, keyboards, drums, trombone, trumpet, baritone, and I am working on learning the banjo and the flute.

 

[rainman89]

Ray - check out this here:
http://www.functionx.com/vbaccess/index.htm
Especially lesson 5. Also, just an FYI http://www.functionx.com has a whole bunch of great tutorials. You might pack that away for your toolbox.

thanks for that bob ill check it out

actually, I do play the tuba, but not for my church's worship team (which is what the rehearsal is for). I will be playing Electric Bass this weekend for church, but I also play guitar, keyboards, drums, trombone, trumpet, baritone, and I am working on learning the banjo and the flute.

shoulda known you were a master boB!!!

 

[rainman89]

just alittle update here
finally figured it out.

ran it using call doupdates(me.name)

but referenced everything with

forms!frmname.control

thanks boB!!