Form Validation help

[Andy]

I have a form where users can select items from a drop-down box as well as
manually enter data if they wish. I want to build in a validation rule where
a warning is displayed if the user tries to enter data where the first 4
characters do not exist in another table...

Presume some form of left([Data],4) and Iif and lookup functions, but can't
get anything to work properly.

Thanks

 

[Allen Browne]

You use a combo for this field, provided its bound column is not hidden
(i.e. the value you see is actually the value being saved into the field.)

You could then use the BeforeUpdate event of the combo to DLookup() the
field in the other table.

This kind of thing:

Private Sub Combo1_BeforeUpate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

With Me.[Combo1]
If Not IsNull(.Value) Then
strWhere = "[MyLookupField] Like """ & Left(.Value, 4) &
"*"""
varResult = DLookup("MyLookupField", "MyTable", strWhere)
If IsNull(varResult) Then
strMsg = "Doesn't match." & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) = vbNo Then
Cancel = True
End If
End If
End If
End With
End Sub

 

[Andy]

Thanks for the reply, though my VBA knowledge is limited. Is there any web
pages you could recommend?

You use a combo for this field, provided its bound column is not hidden
(i.e. the value you see is actually the value being saved into the field.)

You could then use the BeforeUpdate event of the combo to DLookup() the
field in the other table.

This kind of thing:

Private Sub Combo1_BeforeUpate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

With Me.[Combo1]
If Not IsNull(.Value) Then
strWhere = "[MyLookupField] Like """ & Left(.Value, 4) &
"*"""
varResult = DLookup("MyLookupField", "MyTable", strWhere)
If IsNull(varResult) Then
strMsg = "Doesn't match." & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) = vbNo Then
Cancel = True
End If
End If
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form where users can select items from a drop-down box as well as
manually enter data if they wish. I want to build in a validation rule
where
a warning is displayed if the user tries to enter data where the first 4
characters do not exist in another table...

Presume some form of left([Data],4) and Iif and lookup functions, but
can't
get anything to work properly.

Thanks

 

[Allen Browne]

If you need help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply, though my VBA knowledge is limited. Is there any web
pages you could recommend?

You use a combo for this field, provided its bound column is not hidden
(i.e. the value you see is actually the value being saved into the
field.)

You could then use the BeforeUpdate event of the combo to DLookup() the
field in the other table.

This kind of thing:

Private Sub Combo1_BeforeUpate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

With Me.[Combo1]
If Not IsNull(.Value) Then
strWhere = "[MyLookupField] Like """ & Left(.Value, 4) &
"*"""
varResult = DLookup("MyLookupField", "MyTable", strWhere)
If IsNull(varResult) Then
strMsg = "Doesn't match." & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) = vbNo
Then
Cancel = True
End If
End If
End If
End With
End Sub

I have a form where users can select items from a drop-down box as well
as
manually enter data if they wish. I want to build in a validation rule
where
a warning is displayed if the user tries to enter data where the first
4
characters do not exist in another table...

Presume some form of left([Data],4) and Iif and lookup functions, but
can't
get anything to work properly.