Validating combo box entries before insert

[rasher]

I have four combo boxes (EmployeeID, Skill, Certification, Training), three of which must wait for an entry in the first to become enabled. The user is able to enter data on any of the last three, (e.g. a skill and a cert, just a skill, or any combination) in order to make a record.

I have restrained the form so that the user must enter an EmployeeID before creating a record, but how do I check for an entry in at least one of the combo boxes before entry? I have tried macros and if statements but don't know the syntax for checking all three combo boxes.

FYI, their names are cboCertification, cboSkillName, and cboTraining.

Thanks for any help you can provide.

 

[pbaldy]

I'd use the before update event of the form:

http://www.baldyweb.com/BeforeUpdate.htm

Your test would be similar, but test all 3 controls with AND between them:

If A And B And C Then

 

[SOS]

In the form's BEFORE UPDATE event you put in:

If Len(Me.cboCertification & "") = 0 And Len(Me.cboSkillName & "") = 0 And Len(Me.cboTraining & "") = 0 Then
   If MsgBox ("You need to select either a Certification, Skill, or Training." & vbCrlf & _
"Do you wish to continue with this entry?", vbQuestion + vbYesNo, _ 
"Error") = vbYes Then
       Cancel = True
   Else
       Cancel = True
       Me.Undo
   End If
End If

 

[pbaldy]

I doubt you want Cancel = True in both cases.

In the form's BEFORE UPDATE event you put in:

If Len(Me.cboCertification & "") = 0 And Len(Me.cboSkillName & "") = 0 And Len(Me.cboTraining & "") = 0 Then
   If MsgBox ("You need to select either a Certification, Skill, or Training." & vbCrlf & _
"Do you wish to continue with this entry?", vbQuestion + vbYesNo, _ 
"Error") = vbYes Then
       Cancel = True
   Else
       Cancel = True
       Me.Undo
   End If
End If

 

[SOS]

I doubt you want Cancel = True in both cases.

Actually you do. You want to cancel the update if they want to continue editing the record (because by the time it got to that part of the code it failed the validation on the 3 combo boxes) and if you want to cancel the record completely you still need the cancel = true part.

If it passed validation by one of the combo's having something in it, it won't go into that code and then the update is not canceled. But for both of the other stuff you do want to cancel the update. It is just that in the second case you want to undo the record whereas if they want to fix it you are just canceling the update to go fix it.

 

[SOS]

Although I guess you could just insert one at the beginning after the validation:

If Len(Me.cboCertification & "") = 0 And Len(Me.cboSkillName & "") = 0 And Len(Me.cboTraining & "") = 0 Then
   [COLOR=red][B]Cancel = True[/B][/COLOR]
   If MsgBox ("You need to select either a Certification, Skill, or Training." & vbCrlf & _
"Do you wish to continue with this entry?", vbQuestion + vbYesNo, _ 
"Error") = vbYes Then
       Me.Undo
   End If
End If

 

[pbaldy]

I see where you're going. I interpreted that as asking the user whether they wanted to save the record anyway (even though none of the 3 was filled out). In that instance, you wouldn't want it in the first case. Given a scenario that such an entry can't be allowed, you are correct.

 

[SOS]

I see where you're going. I interpreted that as asking the user whether they wanted to save the record anyway (even though none of the 3 was filled out). In that instance, you wouldn't want it in the first case. Given a scenario that such an entry can't be allowed, you are correct.

And I actually just changed it so it would be at the point at which the validation failed (so it doesn't really have two of them but in my prior code it was inefficient and redundant). Now it should be more efficient and easier to identify with.

 

[rasher]

That worked great, thank you very much.

 

[SOS]

Glad Paul and I could both assist you.

 

[Rx_]

This is really good. Have modified it for many uses.
If anyone has a combo box with a change that will affect other controls, look into this solution.
Thanks! You saved me a lot of time!