Fire AfterUpdate On Load

[jwleonard]

I have two forms, FORM1 & FORM2 (not their real names). FORM1 is a standard bound form and FORM2 is an unbound form. On FORM1 I have a command button that opens FORM2 and passes values from numerous fields on FORM1 to FORM2 for printing. On FORM2 I have a combo box that is used to set numberous properties/values on FORM2 via the AfterUpdate event. When FORM2 is opened from the command button on FORM1 a value is passed to the combo box on FORM2. Since the value is set from VBA it doesn't fire the AfterUpdate event of the combo box. I have tried numerous events to call the AfterUpdate after the form is opened and haven't been able to get one to work yet. Does anyone know of a way to run the AfterUpdate event after the form opens and the value is in the combo box?

 

[boblarson]

Revised Answer:

whatever code you want to fire after the combo box is updated (whatever is in the AfterUpdate event of the combo box) you could put in a standard module in a sub and then call it from the AfterUpdate event of the combo and then you can also call the sub when you do your other bit.

 

[jwleonard]

Bob, I just tried that and it seems since it is an unbound form acCmdSaveRecord has no effect.

 

[boblarson]

Yeah, I misread the first part of the question. Take a look at my revised answer above.

 

[jwleonard]

Thanks, I was just about to ask if you thought that might work. I will give it a try now.

 

[jwleonard]

I got it to call the public sub on the AfterUpdate event but still can't find an event that will work when the form is loaded via the command button on FORM1. I tried OnLoad, OnActivate, OnGotFocus, & AfterFinalRender; any ideas?

 

[boblarson]

Just fire the sub from the Form2 On Load event:
in a standard (not form) module:

Public Sub MySubNameHere
 DoWhatever
End Sub

then, from the AfterUpdate Event of the combo box:

Call MySubNameHere

And from the On Load Event of the Form2

Call MySubNameHere

 

[jwleonard]

That is what I have been doing, it works calling it from the combo box AfterUpdate on FORM2. However, it doesn't seem to work when I try it from any event on FORM2 itself. I don't get any errors or anything, it seems like the form is just loading normally. Believe it or not though I haven't tried OnOpen but just about everything else. I will give it a try.

 

[boblarson]

Can you post the actual code for the sub too? Also what code are you using to open the form and assign the combo value?

 

[jwleonard]

It doesn't seem to work with the OnOpen either. I wonder if it is a problem with the combo box value getting set after all of the form events have happened. The value is set when the command button is clicked by something like this
{Code}
Forms!FORM2.ComboBox = Me.txtStatus
{/Code}
FORM2 opens and then whatever event will work should call the public sub in the standard module.
If the form loads up and all events are run before the value goes in the field this wouldn't work. Do you know if the values get passed before or after the form opens (loads, gets focus, activates, renders, final renders, or whatever)?

 

[boblarson]

Try setting the combo box and opening the form like this:

DoCmd.OpenForm "FORM2", acNormal
Forms!FORM2.ComboBoxNameHere = Me.txtStatus
Call MySubNameHere

Now, the question I have is you show Forms!FORM2.ComboBox but I hope your combo box is not named ComboBox. It should be named something meaningful like cboStatus or something like that. ComboBox is a reserved word in Access so that could cause you problems.

 

[boblarson]

If we can't get it this time, maybe you can post your database so I can take a look and see what's going on.

 

[jwleonard]

Here is the code for the command button, notice it is a select case that really only has one case right now but will get more.
{Code}
Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String
Dim stLinkCriteria As String
Select Case Me.cboPrintSelect
Case "Condition Tag"
stDocName = "FRM1574Tag"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!FRM1574Tag.PartNumber1 = Me.PartNumber.Column(1)
Forms!FRM1574Tag.StockNumber1 = Me.StockNumber.Column(1)
Forms!FRM1574Tag.Nomenclature1 = Me.Nomenclature.Column(1)
Forms!FRM1574Tag.SerialNumber1 = Me.SerialNumber
Forms!FRM1574Tag.DocumentNumber1 = Me.DocumentNumber
Forms!FRM1574Tag.cboSelectTag = Me!sfrmCurrentStatus.Form!txtCurrentStatus

Case "350 Tag"

Case "Turnover"

End Select
Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub
{/Code}

Here is the code in the public module
{Code}
Public Sub Update1574Tag()
Select Case Forms!FRM1574Tag.cboSelectTag
Case "COM"
Forms!FRM1574Tag.Block1.BackColor = 65535
Forms!FRM1574Tag.Block2.BackColor = 65535
Forms!FRM1574Tag.txtTitle = "SERVICEABLE TAG - MATERIAL"
Forms!FRM1574Tag.txtCondition1 = "A"
Forms!FRM1574Tag.txtLine1 = "NEXT INSPECTION" & vbCrLf & "DUE/OVERAGE DATE"
Forms!FRM1574Tag.txtLine2 = "INSPECTION ACTIVITY" & vbCrLf & "60TH CMS, TRAVIS AFB, CA"
Case "NRTS-1"
Forms!FRM1574Tag.Block1.BackColor = 6723891
Forms!FRM1574Tag.Block2.BackColor = 6723891
Forms!FRM1574Tag.txtTitle = "UNSERVICEABLE (REPAIRABLE) TAG-MATERIAL"
Forms!FRM1574Tag.txtCondition1 = "F"
Forms!FRM1574Tag.txtLine1 = "INSPECTION ACTIVITY" & vbCrLf & "60TH CMS, TRAVIS AFB, CA"
Forms!FRM1574Tag.txtLine2 = "REASON OR AUTHORITY" & vbCrLf & "NRTS-1"
Case "NRTS-2"
Forms!FRM1574Tag.Block1.BackColor = 6723891
Forms!FRM1574Tag.Block2.BackColor = 6723891
Forms!FRM1574Tag.txtTitle = "UNSERVICEABLE (REPAIRABLE) TAG-MATERIAL"
Forms!FRM1574Tag.txtCondition1 = "F"
Forms!FRM1574Tag.txtLine1 = "INSPECTION ACTIVITY" & vbCrLf & "60TH CMS, TRAVIS AFB, CA"
Forms!FRM1574Tag.txtLine2 = "REASON OR AUTHORITY" & vbCrLf & "NRTS-2"
Case "NRTS-3"
Forms!FRM1574Tag.Block1.BackColor = 6723891
Forms!FRM1574Tag.Block2.BackColor = 6723891
Forms!FRM1574Tag.txtTitle = "UNSERVICEABLE (REPAIRABLE) TAG-MATERIAL"
Forms!FRM1574Tag.txtCondition1 = "F"
Forms!FRM1574Tag.txtLine1 = "INSPECTION ACTIVITY" & vbCrLf & "60TH CMS, TRAVIS AFB, CA"
Forms!FRM1574Tag.txtLine2 = "REASON OR AUTHORITY" & vbCrLf & "NRTS-3"
Case "NRTS-4"
Forms!FRM1574Tag.Block1.BackColor = 6723891
Forms!FRM1574Tag.Block2.BackColor = 6723891
Forms!FRM1574Tag.txtTitle = "UNSERVICEABLE (REPAIRABLE) TAG-MATERIAL"
Forms!FRM1574Tag.txtCondition1 = "F"
Forms!FRM1574Tag.txtLine1 = "INSPECTION ACTIVITY" & vbCrLf & "60TH CMS, TRAVIS AFB, CA"
Forms!FRM1574Tag.txtLine2 = "REASON OR AUTHORITY" & vbCrLf & "NRTS-4"
Case "NRTS-5"
Forms!FRM1574Tag.Block1.BackColor = 6723891
Forms!FRM1574Tag.Block2.BackColor = 6723891
Forms!FRM1574Tag.txtTitle = "UNSERVICEABLE (REPAIRABLE) TAG-MATERIAL"
Forms!FRM1574Tag.txtCondition1 = "F"
Forms!FRM1574Tag.txtLine1 = "INSPECTION ACTIVITY" & vbCrLf & "60TH CMS, TRAVIS AFB, CA"
Forms!FRM1574Tag.txtLine2 = "REASON OR AUTHORITY" & vbCrLf & "NRTS-5"
Case "NRTS-6"
Forms!FRM1574Tag.Block1.BackColor = 6723891
Forms!FRM1574Tag.Block2.BackColor = 6723891
Forms!FRM1574Tag.txtTitle = "UNSERVICEABLE (REPAIRABLE) TAG-MATERIAL"
Forms!FRM1574Tag.txtCondition1 = "F"
Forms!FRM1574Tag.txtLine1 = "INSPECTION ACTIVITY" & vbCrLf & "60TH CMS, TRAVIS AFB, CA"
Forms!FRM1574Tag.txtLine2 = "REASON OR AUTHORITY" & vbCrLf & "NRTS-6"
Case "NRTS-7"
Forms!FRM1574Tag.Block1.BackColor = 6723891
Forms!FRM1574Tag.Block2.BackColor = 6723891
Forms!FRM1574Tag.txtTitle = "UNSERVICEABLE (REPAIRABLE) TAG-MATERIAL"
Forms!FRM1574Tag.txtCondition1 = "F"
Forms!FRM1574Tag.txtLine1 = "INSPECTION ACTIVITY" & vbCrLf & "60TH CMS, TRAVIS AFB, CA"
Forms!FRM1574Tag.txtLine2 = "REASON OR AUTHORITY" & vbCrLf & "NRTS-7"
Case "NRTS-8"
Forms!FRM1574Tag.Block1.BackColor = 6723891
Forms!FRM1574Tag.Block2.BackColor = 6723891
Forms!FRM1574Tag.txtTitle = "UNSERVICEABLE (REPAIRABLE) TAG-MATERIAL"
Forms!FRM1574Tag.txtCondition1 = "F"
Forms!FRM1574Tag.txtLine1 = "INSPECTION ACTIVITY" & vbCrLf & "60TH CMS, TRAVIS AFB, CA"
Forms!FRM1574Tag.txtLine2 = "REASON OR AUTHORITY" & vbCrLf & "NRTS-8"
Case "NRTS-9"
Forms!FRM1574Tag.Block1.BackColor = 255
Forms!FRM1574Tag.Block2.BackColor = 255
Forms!FRM1574Tag.txtTitle = "UNSERVICEABLE (CONDEMNED) TAG-MATERIAL"
Forms!FRM1574Tag.txtCondition1 = "H"
Forms!FRM1574Tag.txtLine1 = "INSPECTION ACTIVITY" & vbCrLf & "60TH CMS, TRAVIS AFB, CA"
Forms!FRM1574Tag.txtLine2 = "REASON OR AUTHORITY" & vbCrLf & "NRTS-9"
End Select
End Sub
{/Code}

Just so you know this is to show/print a tag and it will be used from different forms and with data from different tables. I also want the user to be able to manually type the required information if the form is opened with no values passed. That is the reason I am attempting to do this the way I am.

Thanks

 

[boblarson]

I'm looking at it and trying to see if I can see it. By the way, to put code tags for the forum in it is Square brackets [] and not {} with the word code in between.

 

[jwleonard]

I was simplifing the form/control names and I didn't catch I was holding the shift key whe trying to type [], sorry.
Good news though, running the Call MyPublicSub at the bottom of the command button code fixed it. I guess that means the values are passed after the form loads not before or during. It just seems like the command button code would have to complete before loading the form, but apparently not.

 

[boblarson]

Try simplifying this and go with:

Public Sub Update1574Tag(strStatus as String)
Select Case strStatus

You can pass the status to the sub when you call it from the combo:

Call Update1574Tag(Me.cboSelectTag)

and you can call it from the command button:

Call Update1574Tag(Me!sfrmCurrentStatus.Form!txtCurrentStatus)

You can still set the combo to the status, but that way you can pass the sub the parameters without worrying about having to deal with it's update.

 

[boblarson]

Well, if you got it, then nevermind

 

[jwleonard]

No, YOU got it! Thank you very much for your time and effort. It is great for someone like me to have a place to go for help when needed and I really appreciate all the help provided on this forum.