Form Validation help

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

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andy" wrote in message
...
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

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

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andy" wrote in message
...
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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andy" wrote in message
news
Thanks for the reply, though my VBA knowledge is limited. Is there any web
pages you could recommend?

"Allen Browne" wrote:

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" wrote in message
...
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.