One control with two "afterupdate" requirements.

[athomas8251]

I have an Order Form using cascading combo boxes using very simple VBA.

• The first combo box called "PayorType", filters the second combo box called "PayorID".
• There are four PayorType value selections: Insurance, Employer, Self, Other

Private Sub cboPayorType_AfterUpdate()

cboPayorID.RowSource = "Select tblPayors.PayorID, ..... etc etc. "
"FROM tblPayors " & _
"WHERE tblPayors.PayorType = '" & cboPayorType.Value & "' " & _
"ORDER BY tblPayors.PayorID;"

End Sub

Now that I've got that working..... my next step is to make sure users fill out the four fields of health insurance information (InsuredName, RelationshipTo, PolicyNumber, GroupNumber) if "Insurance" PayorType is chosen.

From what I can gather after researching, this appears to be an AfterUpdate action on the same PayorType control.



Is it possible to have two actions on one control AfterUpdate? If so, I would first run the VBA to filter the cascading combo box. Second, I would want the focus to go to the InsuredName field if PayorType = "Insurance" - otherwise, skip to the Order Detail subform.

If this isn't impossible, I'd just be happy if there is a way to pop up a message box based on PayorType ="Insurance" before the form closes.

Thank you for your attention!

Andi

[RuralGuy]

There is no limit on how much you can do in the AfterUpdate event of a control. That includes moving the focus to the next control on *your* choice.

[athomas8251]

Thanks RG! That's great to know. I've not done this before (although I've been pretty good at looking at other code and adapting it).

If cboPayorType = "Insurance", focus on the InsuredName control and require data in InsuredName, RelationshipTo, PolicyNumber, and GroupNumber fields.

If cboPayorType = is not "Insurance", skip.

Help? I'd be happy to send you the DB. Please let me know.

Best,

Andi

[RuralGuy]

You will need to enforce the required fields in the BeforeUpdate event of the Form but moving the focus is as simple as:
If Me.cboPayorType = "Insurance" Then
Me.InsuredName.SetFocus
Else
Me.OtherControl.SetFocus
End If