Force User to select value from combo box

[data808]

I been trying this code on a button to quit the database. When the user clicks the button to quit, it will check for a required combo box I have. In this combo box is a list of values that I want the user to select from. However, even if the user selects from the value list it will still prompt them to select subject from drop down list. What am I missing here? Here is the code:

If Me.cmbSubject <> Me.cmbSubject.LimitToList Then


MsgBox "Please Select Subject From Drop Down List", vbInformation, "Requirements"
Me.cmbSubject.SetFocus
Exit Sub
End If

I also tried this but couldn't get it to work either. This one will give a run-time error 13 type mismatch error and it points to the first line of code:

If Me.cmbSubject <> "Item 1" OR "Item 2" Then

Here is the entire code:

If Me.cmbSubject <> "Item 1" OR "Item 2" Then
MsgBox "Please Select Subject From Drop Down List", vbInformation, "Requirements"
Me.cmbSubject.SetFocus
Exit Sub
End If

[June7]

Why are you testing combobox value against the LimitToList property?

If IsNull(Me.cmbSubject) Then

[Micron]

If Me.cmbSubject <> "Item 1" OR "Item 2" Then

You cannot write code like you talk/think.

If Me.cmbSubject <> "Item 1" OR Me.cmbSubject <> "Item 2" Then - and that assumes the bound field of your combo contains those values.
LimitToList is a property so the control value will likely never equal that property value, which is boolean (T/F)

[data808]

I seen the LimitToList property as I was typing the code and figured I just give it a shot. I had a feeling I wasn't using it for the correct scenario.

If IsNull is something I am already using however, I am having a another problem with the hover color property for a button. When I use the mouse to click the button, of course it triggers the hover color property and makes the button change colors (blue), however, Access will then prompt the user if their value in the combo box is not from the list. This causes my button's backcolor to get stuck in blue because now the cmb.Subject combo box has focus but not in the VBA sense but rather Access' built in prompts. I've tested this because if I can stop the user with VBA prompts, the button color does not get stuck. It changes back.

I have tried correcting this with all the got/lost focus events, before/after update events, on current event, on keydown event, etc... and it has not made the problem go away. All I want is for the button to return back to its normal color if this scenario happens but it keeps getting stuck with hover color. So what I am trying to do now is prompt the user with VBA instead of Access' built in default prompts for combo boxes. Does this make sense?

Here is the scenario:

User fills our form then wants to quit the database. User uses the mouse to click the quit button. While the mouse cursor is hovering over this quit button, it changes it from yellow to blue. While this is happening, the user did not fill out the combo box correctly. They entered their own value instead of selecting a value from the list. So now when the user clicks the quit button, Access prompts them that the combo box does not have a value from the list. The focus/cursor now goes to the combo box so the user can fill in the correct value but my quit button remains blue unless I hover my mouse cursor over it to reset it back to yellow. The prompt that comes up for letting the user know that the combo box is not filled out correctly is not from my VBA and so my theory is that this is what is causing the problem. If I can handle this through VBA, I will have a better chance at making that button go back to yellow when my VBA prompt comes up for the user filling out the combo box wrong.

[data808]

You cannot write code like you talk/think.

If Me.cmbSubject <> "Item 1" OR Me.cmbSubject <> "Item 2" Then - and that assumes the bound field of your combo contains those values.
LimitToList is a property so the control value will likely never equal that property value, which is boolean (T/F)

Ok I just tried this method and it did not work. It just keeps prompting to select a value from the list, regardless if I choose "Item 1" or "Item 2".

So this brings me to you mentioning about my bound field containing those values. What exactly did you mean by this? I have the values written out in the Property Sheet under the Data Tab for the Row Source property. Is this not what you meant? Do I have to do something for this combo box in the backend file? Is that where these values need to be listed?

[June7]

Perhaps you should provide file for analysis.

[data808]

Unless, what if I don't allow the user to type into the combo box? Is there a property or VBA for that? User has no other option but to select from the combo box list of values. That would prevent the user from typing into the box creating a non-valid value and I think that might solve the problem.

[data808]

Perhaps you should provide file for analysis.

I think my suggestion solved the problem. I found some VBA in the keydown event for the combo box and now because the VBA prompts the user and also doesn't allow them to type whatever they want, the hover color for the button seems to deactivate once the focus goes to the combo box. Thanks for the help. Here is the code I used to solve the problem:

Private Sub cmbSubject_KeyDown(KeyCode As Integer, Shift As Integer)


Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyShift, vbKeyDown, vbKeyUp
'Do nothing
Case Else
KeyCode = 0
MsgBox "Please Select Subject From Drop Down List!", vbInformation, "Requirement"
End Select


End Sub

Let me know if you see anything I missed as I will probably need to test this a bit to make absolutely sure that this will cover all scenarios. Thanks again.

[Welshgasman]

If you are going to use a <> then you would need an AND ?

Otherwise if if you select 2 it is not 1 and vice versa ?

Aleternative check for = and put the code in the Else part of the If block?