Validate combo box

Question

I have set up cascading combo boxes as described in another thread - many thanks for that.  My 2nd combo has a default value of "Select one".

However there is nothing to stop a user just tabbing through the 2nd combo and leaving "Select one", or, having selected a 2nd combo value going back to change the 1st combo and then tabbing through the 2nd one without selecting from the new list.

How do I validate that 2nd combo?

Answer 1

I'd ditch the 'Select one' default value from the second combo box, but regardless of that, in the AfterUpdate procedure of the first combo box you should not only requery the second, but set it to Null.  If a value has been selected in it and the user then goes back and selects another value in the first combo box, that in the second will probably be inappropriate to that in the first.  Moreover, if the bound column of the second combo box is hidden it will appear empty, but will still have the inappropriate value.

You should validate the the second combo box is not Null in the form's BeforeUpdate event procedure.  This has a Cancel argument whose return value can be set to true if the second combo box is Null, forcing the user to enter a value before the record can be saved.

More fundamentally, if both combo boxes are bound controls consider whether storing both values introduces redundancy, e.g storing both CityID and State values does this as the former determines the latter.  You'll find a demo of ways of handling this at:

http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=23626&webtag=ws-msdevapps

using the local administrative areas off parish, district and county in my neck of the woods.

---

Ken Sheridan, Stafford, England

Answer 2

or, having selected a 2nd combo value going back to change the 1st combo and then tabbing through the 2nd one without selecting from the new list.

Not sure that would work Roger.  If a user tabbed through combgos 1 and 2 to 3 (if there is one) or somewhere else then went back to combo 1 (as the OP suggests) and changed it, the "if Select One" would fail - maybe, depending on the AfterUpdate event of the 1st combo.

Mike - can you post the code you have for the 1st and 2nd combos, then you may be able to get a full answer

Answer 3

If you use the Lost Focus event, you'd have to set the focus to some other control and set it back.  Something like this:

Private Sub Combo22_LostFocus()

If Combo22 = "select one" Then

    MsgBox "please select a value"

    Textbox1.SetFocus

    Combo22.SetFocus

End If

End Sub

This is because the LostFocus event doesn't have a Cancel and it won't set back to itself without first being set to another.  The danger in this, of course, is if the other control is deleted or renamed, this code will break.

---

-- Roger Carlson

MS Access MVP 2006-2010

www.rogersaccesslibrary.com

Answer 4

Use the control's Exit event to test for its value:

Private Sub Combo22_Exit(Cancel As Integer)

If Combo22 = "select one" Then

    MsgBox "please select a value"

    Cancel = True

End If

End Sub

You can also leave out the message box and just have it basically stay on that control until another value is selected.

---

-- Roger Carlson

MS Access MVP 2006-2010

www.rogersaccesslibrary.com

Answer 5

Best bet would be jsut to delete the default "Select one". Leave it bank until the 1st combo is actioned.

You will have already re-query the 2nd combo AfterUpdate of the 1st, so there is no reason to have a default text in the 2nd combo If you really want to leave the default as "select one" you could use

Private Sub comboname_LostFocus()

If Me.comboname = "Select one" Then

'do something here - like a message to go back and change it

End Sub

Another good reason not to use Select One is that if the user goes back to the 1st combo and changes it, it should requery the 2nd - if you set the 1st option to be Null then you good use the Null to stop a user tabbing over it

If IsNull (comboname) Then

etc

etc