Trying to validate a combo box

[3587ch]

I have a form with a combo box that I want to make sure a user always selects from, for new or existing records. I tried using the Validation properties of the control but that does nothing.

I used Is Not Null in the Validation Rule and a message in the Vlaidation Text.

Is there a better way to make sure they don't leave the form without making a selection? I would like the validation on the form and not the table.

Thank you,

 

[jrbarnett]

Use some VBA code in the combobox's BeforeUpdate event to check for a non null value in the control.

John

 

[timhans]

3587ch, I have used this to good effect, to use for combobox place a c in the tag property.

Private Sub Form_AfterUpdate()
'Place an asterisk (*) in the Tag Property of the text
'boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:


Dim Msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control


nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
Msg = "Data Required for '" & ctl.Name & "' field!" & nl & "You can't save this record until this data is provided!" & nl & "Enter the data and try again ... "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox Msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
If ctl.ControlType = acComboBox Then
If ctl.Tag = "c" And ctl.ListIndex = -1 Then
MsgBox "Please make a selection from each drop-down list."
ctl.SetFocus
End If
End If
Next

End Sub

 

[TheAceMan1]

How are ya 3587ch . . .

In the [blue]After Update[/blue] event of the combobox, copy/paste the following ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim CBx As ComboBox, DL As String
   
   Set CBx = Me![[purple][b]ComboboxName[/b][/purple]]
   DL = vbNewLine & vbNewLine
   
   If CBx.ListIndex = -1 Then [green]'No selection made!![/green]
      MsgBox "'[purple][b]ComboboxName[/b][/purple]' is a required field!" & DL & _
             "You'll have to go back and make a selection ...", _
             vbInformation + vbOKOnly, _
             "Missing Data Error! . . ."
      CBx.SetFocus
      Cancel = True
   End If
   
   Set CBx = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]