Data Validation

  • Thread starter Thread starter Jonathan Smith
  • Start date Start date
J

Jonathan Smith

In an Access 2002 environment, with Access 2000 File Format:

Table has two fields:

ActivityCode (Required)
ClientName

Form has two Controls:

cmbACtivityCode
cmbClientName

Validation Need:

I need to check the value of cmbActivityCode, if the value is any
of 12 (possible 17 choices), then cmbClientName is required; otherwise
cmbClientName is not required.

PseudoCode:

If cmbActivityCode.Value = 6001 Then cmbClientName Cannot be NULL

I would appreciate any suggestions on Actual Code.
 
Jonathan, try this changing the field/control names as needed:


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngCode As Long
lngCode = Me.cmbACtivityCode

If Len(Me.cmbClientName & "") = 0 Then
'The cmbClientName field is null or an empty string
'So run code to check the value of the cmbACtivityCode field

Select Case lngCode
Case 6001
MsgBox "Input required"
Cancel = True
Case 6002
MsgBox "Input required"
Cancel = True
Case 6002
MsgBox "Input required"
Cancel = True
Case Else
Cancel = False
End Select
End If
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me!cmbClientName) Then
Select Case Me!cmbActivityCode
Case "something1astext", "st2at", "st3at"
Cancel = True
Msgbox "Select a client name."
Me!cmbClientName.SetFocus
End Select
End If

End Sub

look at the Select Case Statement topic in VBA Help, for details on writing
the Case expression that best suits your needs...i only showed you one
example, and only text - there are others in the Help topic.

hth
 
Tina,

Thank you very much, it works great.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me!cmbClientName) Then
Select Case Me!cmbActivityCode
Case "something1astext", "st2at", "st3at"
Cancel = True
Msgbox "Select a client name."
Me!cmbClientName.SetFocus
End Select
End If

End Sub

look at the Select Case Statement topic in VBA Help, for details on
writing the Case expression that best suits your needs...i only showed
you one example, and only text - there are others in the Help topic.

hth
Click to expand...
 
Reggie,

Thanks for the in-depth answer ... it is a great approach.

I have utilized it, and it works very well.

Thank you again.
 
Back
Top