Require ComboBox Selection when TextBox data changes

[Erika In FL]

Ok guys! My brain is just swirling after reading other questions and answers. I have a form that is used to enter quarterly inventory dates. Each quarterly date (a text box - [Qtr1Date1] for example) also has 2 corresponding combo boxes - a change reason [Qtr1Date1Reason] and a change initiator [Qtr1Date1Change r]. I've made an AfterUpdate event that changes the value of the two comboBoxes to a default value - "<Select>" and it also changes the color to direct the user's attention there (but we all know that I need a requirement of data entry).

The piece that I cannot wrap my head around - if the user does not drop down to the first combobox and then the second and make an entry - how and where do I enter the validation requirement of choosing values? I only want a msgbox to pop up if they don't enter a choice in each drop down (I don't care which order they enter the choices). Do I need a validation save button? Or can this be done when user attempts to close out of form? Maybe SetFocus if value is = <Select> ?

[NeoPa]

If the Form's bound you can use the Form_BeforeUpda te() procedure. If it's not then put it wherever you're going to trigger the process that uses the data.

Unfortunately it's not clear what you're using it for or how, so I can only answer generically for now.

[Erika In FL]

Hey Neo, I am not currently using BeforeUpdate - I have in the text field, an after update with the following:

Private Sub Qtr1Date1_AfterUpdate()
    Call LogChanges(StoreCode)
    Qtr1Date1Reason = "Accounting Error"
    Qtr1Date1Changer = "Accounting"
    Qtr1Date1Reason.BackColor = RGB(244, 66, 113)
    Qtr1Date1Changer.BackColor = RGB(244, 66, 113)
End Sub

So I kind of make the user "want" to change the reason and initiator from accounting so they themselves as the accountant don't show up on the audit report - but I would ideally like the form not allow them to navigate away from the first dropdown (would say accounting error) without making a new selection, and then drop down to 2nd combo (would say Accounting) and then they can navigate away to other fields. I also tried blanking out the combo selections in the afterupdate and entering a validation rule as Not Null, but it doesn't recognize that until I click a new selection and blank it out secondarily so that didn't work.

[NeoPa]

Hi Erika.

Your latest post tells me very little about the question. It's information. I can see that, but not information that seems to lead anywhere.

You don't seem to be directing your response to what was said in my post - except somewhat obliquely perhaps. I still have no idea about most of your circumstances. Circumstances which are germane to how one would go about doing what you ask for. What you have seems quite unusual to me. I can't guess at why what you have would make sense.

So, if I'm to be any help at all to you, I need you to think carefully about what you're doing and share that info with me in such a way that it makes sense to someone who neither has your database in front of them, nor an understanding of what job it is you're working on.

[Erika In FL]

I will try again. I have a text box called Qtr1Date1. When the user changes the data in the text box, I want them be forced to select a reason of why the data was changed. The reasons are in a list called Qtr1Date1Reason . I don't want them to be able to navigate anywhere except to the reason list until they make a selection.

[NeoPa]

Ah Erika, that makes it a lot clearer thank you.

In general words then, the idea is to move the focus to the Qtr1Date1Reason ComboBox control in your Qtr1Date1_After Update() event procedure to start with. As well as that you need a Qtr1Date1Reason _Exit() event procedure in which you check that it has a .Value set. If not then it sets its Cancel parameter to True, possibly with a MsgBox() call to indicate why.

That would look something like :

Private Sub Qtr1Date1_AfterUpdate()
    'Anything else you need
    Call Me.Qtr1Date1Reason.SetFocus
End Sub

Private Sub Qtr1Date1Reason_Exit(Cancel As Integer)
    Cancel = IsNull(Me.Qtr1Date1Reason.Value)
End Sub