Data Validation Problem

[StacyStacy]

I have the following code to validate data to ensure it's correctly keyed in and not a duplicate.

I have keyed in the county code of 014 and I get a run-time error of 3464 - Data Mismatch. Why?

Here's the code place in the "Before Update event:

Dim MyVar As Variant
Dim County_Exists As Boolean
County_Exists = False

MyVar = DLookup("[COUNTY_NO]", "[COUNTY]", "[COUNTY_NO] =" & Me.COUNTY_NO)
If MyVar = Me.COUNTY_NO Then
County_Exists = True

End If

Thanks for your help.

 

[StacyStacy]

Is it because of my single and double quotes?

 

[WayneRyan]

Stacy,

Try:

Dim MyVar As Variant
Dim County_Exists As Boolean
County_Exists = False

MyVar = DLookup("[COUNTY_NO]", "[COUNTY]", "[COUNTY_NO] =" & Me.COUNTY_NO)
If IsNull(MyVar) Then
  County_Exists = False
Else
  County_Exists = True
End If

However, County_Exists is only viable when your code
is run. Your form can't reference it.

Wayne

 

[StacyStacy]

Thanks. I will try it and reply back.

 

[StacyStacy]

In this situation, how do I cancel the Update event? I am checking to ensure that the county code, which is a 3 digit number with field properties of text is not a duplicate entry.

 

[billyr]

Try this. The only output from this routine is a boolean; so, you don't need to save the results of the look up. All you need to know is, did it hit or not.

Dim County_Exists As Boolean
County_Exists = true
if isnull(DLookup("[COUNTY_NO]", "COUNTY", "[COUNTY_NO] = '" & Me.COUNTY_NO & "'")) then
County_Exists = False
End If