By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

Require ComboBox Selection when TextBox data changes

P: 3
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 [Qtr1Date1Changer]. 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> ?
Sep 14 '17 #1

✓ answered by 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_AfterUpdate() 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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Qtr1Date1_AfterUpdate()
  2.     'Anything else you need
  3.     Call Me.Qtr1Date1Reason.SetFocus
  4. End Sub
  5.  
  6. Private Sub Qtr1Date1Reason_Exit(Cancel As Integer)
  7.     Cancel = IsNull(Me.Qtr1Date1Reason.Value)
  8. End Sub

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,487
If the Form's bound you can use the Form_BeforeUpdate() 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.
Sep 15 '17 #2

P: 3
Hey Neo, I am not currently using BeforeUpdate - I have in the text field, an after update with the following:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Qtr1Date1_AfterUpdate()
  2.     Call LogChanges(StoreCode)
  3.     Qtr1Date1Reason = "Accounting Error"
  4.     Qtr1Date1Changer = "Accounting"
  5.     Qtr1Date1Reason.BackColor = RGB(244, 66, 113)
  6.     Qtr1Date1Changer.BackColor = RGB(244, 66, 113)
  7. 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.
Sep 15 '17 #3

NeoPa
Expert Mod 15k+
P: 31,487
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.
Sep 15 '17 #4

P: 3
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.
Sep 15 '17 #5

NeoPa
Expert Mod 15k+
P: 31,487
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_AfterUpdate() 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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Qtr1Date1_AfterUpdate()
  2.     'Anything else you need
  3.     Call Me.Qtr1Date1Reason.SetFocus
  4. End Sub
  5.  
  6. Private Sub Qtr1Date1Reason_Exit(Cancel As Integer)
  7.     Cancel = IsNull(Me.Qtr1Date1Reason.Value)
  8. End Sub
Sep 16 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.