Unexpected type mismatch error

[jaman57]

I have a form where the field "tpdr" is the primary key of the "data" table,
and is a text field. In order to prevent someone entering a duplicate value
without getting the default Access error message (which would just confuse
them, especially as it would not come until they had finished entering the
other fields farther on in the form when they tried to go to the next
record), I added the following to the "after update" property of the field:

If DLookup("[tpdr]", "data", "[tpdr]=Forms!Input1!tpdr") Then
[tpdr] = ""
MsgBox "This name has already been used, and cannot be duplicated.
Please choose another name."
DoCmd.CancelEvent
DoCmd.GoToControl "tpdr"
End If

This works fine if the attempted duplicate entry in "tpdr" is a number - it
gives the message and retuens the user to the "tpdr" field. But if the
attempted duplicate entry is text, then I get a "Run-time error13. Type
Mismatch." Again the field is a text field, so I don't understand why I am
getting this error. Since the field is a text field it should be treating the
number entry as text, and since it is a text field how can a text entry cause
a type mismatch?

Any ideas?

By the way, this does not work at all if the code is attached to the "Before
Update" property - then it errors out saying that saying the code is
preventing the entry from being saved.

Thanks,
Jeff

 

[Terry Kreft]

You're getting the error because you are not delimiting the text value, but
you're using the wrong event, you should be using the BeforeUpdate event.


Your code (in the BeforeUpdate event) should look something like:-

If DLookup("[tpdr]", "data", "[tpdr]='" & Me.tpdr.Text & "'") Then
Cancel= True
MsgBox "This name has already been used, and cannot be duplicated.
Please choose another name."
End If

 

[jaman57]

Thank you. I thought it had to involve the "Before Update" property from what
I had seen but couldn't get it to work. I see it was I didn't have the
criteria stated right.

Jeff

You're getting the error because you are not delimiting the text value, but
you're using the wrong event, you should be using the BeforeUpdate event.


Your code (in the BeforeUpdate event) should look something like:-

If DLookup("[tpdr]", "data", "[tpdr]='" & Me.tpdr.Text & "'") Then
Cancel= True
MsgBox "This name has already been used, and cannot be duplicated.
Please choose another name."
End If


--

Terry Kreft

I have a form where the field "tpdr" is the primary key of the "data" table,
and is a text field. In order to prevent someone entering a duplicate value
without getting the default Access error message (which would just confuse
them, especially as it would not come until they had finished entering the
other fields farther on in the form when they tried to go to the next
record), I added the following to the "after update" property of the field:

If DLookup("[tpdr]", "data", "[tpdr]=Forms!Input1!tpdr") Then
[tpdr] = ""
MsgBox "This name has already been used, and cannot be duplicated.
Please choose another name."
DoCmd.CancelEvent
DoCmd.GoToControl "tpdr"
End If

This works fine if the attempted duplicate entry in "tpdr" is a number - it
gives the message and retuens the user to the "tpdr" field. But if the
attempted duplicate entry is text, then I get a "Run-time error13. Type
Mismatch." Again the field is a text field, so I don't understand why I am
getting this error. Since the field is a text field it should be treating the
number entry as text, and since it is a text field how can a text entry cause
a type mismatch?

Any ideas?

By the way, this does not work at all if the code is attached to the "Before
Update" property - then it errors out saying that saying the code is
preventing the entry from being saved.

Thanks,
Jeff