Data type mismatch in criteria expression

[tomanddani via AccessMonster.com]

Im having a problem with some vba that checks for duplicate addresses. Im
getting a run time error #62506. Data type mismatch in criteria expression.
This is the vb code:

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

If (Me.StreetNumber = Me.StreetNumber.OldValue) And (Me.Direction = Me.
Direction.OldValue) And (Me.StreetName = Me.StreetName.OldValue) Then
'do nothing
Else
strWhere = "([StreetNumber] = """ & Me.StreetNumber & """) AND (
[Direction] = """ & Me.Direction & """) AND ([StreetName] = """ & Me.
StreetName & """)"
varResult = DLookup("AddressID", "tblAddresses", strWhere)
If Not IsNull(varResult) Then
strMsg = "Duplicate of address " & varResult & vbCrLf & vbCrLf &
"CONTINUE ANYWAY?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Warning") <> vbYes
Then
Cancel = True
Me.Undo
End If
End If
End If
End Sub

The error is in the:
varResult = DLookup("AddressID", "tblAddresses", strWhere)
line.
Im using a two tables called tblAddresses and tblBldgAndSystems. The form was
built by using a query called QAddressesAndBldgAndSystems. Im thinking that
Im just not refering to the objects the right way. Can anyone help.

Thanks

 

[John W. Vinson]

Im having a problem with some vba that checks for duplicate addresses. Im
getting a run time error #62506. Data type mismatch in criteria expression.
This is the vb code:

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

If (Me.StreetNumber = Me.StreetNumber.OldValue) And (Me.Direction = Me.
Direction.OldValue) And (Me.StreetName = Me.StreetName.OldValue) Then
'do nothing
Else
strWhere = "([StreetNumber] = """ & Me.StreetNumber & """) AND (
[Direction] = """ & Me.Direction & """) AND ([StreetName] = """ & Me.
StreetName & """)"
varResult = DLookup("AddressID", "tblAddresses", strWhere)
If Not IsNull(varResult) Then
strMsg = "Duplicate of address " & varResult & vbCrLf & vbCrLf &
"CONTINUE ANYWAY?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Warning") <> vbYes
Then
Cancel = True
Me.Undo
End If
End If
End If
End Sub

The error is in the:
varResult = DLookup("AddressID", "tblAddresses", strWhere)
line.

What is the actual value of strWhere (try setting a Breakpoint in the code by
clicking in the vertical grey bar to the left of the code window next to the
varResult= line)? What are the datatypes of the fields? Are any of them <yuck,
ptooie!!) Lookup Fields?

 

[tomanddani via AccessMonster.com]

I think that I have found that the problem lies in the fact that the
StreetNumber field is a number and not a text field. When I changed it to
text, it worked. How would I write it in order to keep it a number field?

Thanks

Im having a problem with some vba that checks for duplicate addresses. Im
getting a run time error #62506. Data type mismatch in criteria expression.
This is the vb code:

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

If (Me.StreetNumber = Me.StreetNumber.OldValue) And (Me.Direction = Me.
Direction.OldValue) And (Me.StreetName = Me.StreetName.OldValue) Then
'do nothing
Else
strWhere = "([StreetNumber] = """ & Me.StreetNumber & """) AND (
[Direction] = """ & Me.Direction & """) AND ([StreetName] = """ & Me.
StreetName & """)"
varResult = DLookup("AddressID", "tblAddresses", strWhere)
If Not IsNull(varResult) Then
strMsg = "Duplicate of address " & varResult & vbCrLf & vbCrLf &
"CONTINUE ANYWAY?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Warning") <> vbYes
Then
Cancel = True
Me.Undo
End If
End If
End If
End Sub

The error is in the:
varResult = DLookup("AddressID", "tblAddresses", strWhere)
line.
Im using a two tables called tblAddresses and tblBldgAndSystems. The form was
built by using a query called QAddressesAndBldgAndSystems. Im thinking that
Im just not refering to the objects the right way. Can anyone help.

Thanks

 

[tomanddani via AccessMonster.com]

Thanks for your response,
I tried what you suggested. I put a 'breakpoint' next to the varResult = line.
It stoped it at the line and put a red box with a yellow arrow next to the
line. (Sorry, I have no idea what that means. lol) The fields are all text
fields except the StreetNumber field. I wanted that one to be a number in
case I needed to find say, all the street numbers over 100. I think I just
need to express the field correctly.

Thanks again.

Im having a problem with some vba that checks for duplicate addresses. Im
getting a run time error #62506. Data type mismatch in criteria expression.

[quoted text clipped - 28 lines]

varResult = DLookup("AddressID", "tblAddresses", strWhere)
line.

What is the actual value of strWhere (try setting a Breakpoint in the code by
clicking in the vertical grey bar to the left of the code window next to the
varResult= line)? What are the datatypes of the fields? Are any of them <yuck,
ptooie!!) Lookup Fields?

 

[John W. Vinson]

I think that I have found that the problem lies in the fact that the
StreetNumber field is a number and not a text field. When I changed it to
text, it worked. How would I write it in order to keep it a number field?

What you would do is just remove the quotemarks. Text fields must be delimited
by ' or ", Number fields get no delimiter at all:

strWhere = "([StreetNumber] = " & Me.StreetNumber & ") AND (
[Direction] = """ & Me.Direction & """) AND ([StreetName] = """ & Me.
StreetName & """)"

HOWEVER - I'd strongly suggest that you leave the field as Text. What about
addresses like 1812 1/2 Columbia Pl., or 22B Baker St.? The need to sort
street addresses sequentially is pretty rare and unlikely - if you want to
help out the post office use Zip+4 and sort THAT.

 

[tomanddani via AccessMonster.com]

Wow! You have no idea how many times your posts have saved me. You make it
seem so clear. I see your point with the number field, I had not thought of
that. I also didnt know that about the quote marks. Thanks.

Now Im wondering, is there a simple way to give users the option of going
directly to the old duplicate record with the message box as well as
continuing or not continuing.

Thanks again, you really have made my day.

I think that I have found that the problem lies in the fact that the
StreetNumber field is a number and not a text field. When I changed it to
text, it worked. How would I write it in order to keep it a number field?

What you would do is just remove the quotemarks. Text fields must be delimited
by ' or ", Number fields get no delimiter at all:

strWhere = "([StreetNumber] = " & Me.StreetNumber & ") AND (
[Direction] = """ & Me.Direction & """) AND ([StreetName] = """ & Me.
StreetName & """)"

HOWEVER - I'd strongly suggest that you leave the field as Text. What about
addresses like 1812 1/2 Columbia Pl., or 22B Baker St.? The need to sort
street addresses sequentially is pretty rare and unlikely - if you want to
help out the post office use Zip+4 and sort THAT.

 

[John W. Vinson]

Wow! You have no idea how many times your posts have saved me. You make it
seem so clear. I see your point with the number field, I had not thought of
that. I also didnt know that about the quote marks. Thanks.

Now Im wondering, is there a simple way to give users the option of going
directly to the old duplicate record with the message box as well as
continuing or not continuing.

Thanks again, you really have made my day.

And you've made my day! Thanks for the kind words.

You can go to the found duplicate record pretty easily if you use a Recordset
rather than a DLookUp. Something like

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strWhere As String
Dim strMsg As String
Dim iAns As Integer
strWhere = "([StreetNumber] = """ & Me.StreetNumber & _
& """) AND ([Direction] = """ & Me.Direction & _
& """) AND ([StreetName] = """ & Me.StreetName & """)"
Set rs = Me.RecordsetClone ' assume all addresses are in this form's recordset
rs.FindFirst strWhere
If Not rs.NoMatch Then ' was a record found?
strMsg = "Duplicate of address; add it? " vbCrLf & vbCrLf &
"Click Yes to continue, No to jump to the found record, Cancel to start over."
iAns = MsgBox(strMsg, vbYesNoCancel + vbDefaultButton2, "Warning")
Select Case iAns
Case vbYes ' do nothing, just add the record
Case vbNo
Cancel = True
Me.Bookmark = rs.Bookmark ' jump to the record
Case vbCancel ' cancel, start over
Cancel = True
Me.Undo
End Select
End If
Set rs = Nothing
End Sub

 

[tomanddani via AccessMonster.com]

Thanks John,
It all seems to work great, except that when you try to go to the found
record it by clicking no go to found record it has a run time error and says
that it cant go to the specified record. It highlights the Me.Bookmark = line

Thanks

Wow! You have no idea how many times your posts have saved me. You make it
seem so clear. I see your point with the number field, I had not thought of

[quoted text clipped - 5 lines]

Thanks again, you really have made my day.

And you've made my day! Thanks for the kind words.

You can go to the found duplicate record pretty easily if you use a Recordset
rather than a DLookUp. Something like

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strWhere As String
Dim strMsg As String
Dim iAns As Integer
strWhere = "([StreetNumber] = """ & Me.StreetNumber & _
& """) AND ([Direction] = """ & Me.Direction & _
& """) AND ([StreetName] = """ & Me.StreetName & """)"
Set rs = Me.RecordsetClone ' assume all addresses are in this form's recordset
rs.FindFirst strWhere
If Not rs.NoMatch Then ' was a record found?
strMsg = "Duplicate of address; add it? " vbCrLf & vbCrLf &
"Click Yes to continue, No to jump to the found record, Cancel to start over."
iAns = MsgBox(strMsg, vbYesNoCancel + vbDefaultButton2, "Warning")
Select Case iAns
Case vbYes ' do nothing, just add the record
Case vbNo
Cancel = True
Me.Bookmark = rs.Bookmark ' jump to the record
Case vbCancel ' cancel, start over
Cancel = True
Me.Undo
End Select
End If
Set rs = Nothing
End Sub

 

[John W. Vinson]

Thanks John,
It all seems to work great, except that when you try to go to the found
record it by clicking no go to found record it has a run time error and says
that it cant go to the specified record. It highlights the Me.Bookmark = line

What in fact is the Recordsource for the form? Is this a mainform (which
should work) or a Subform (which almost surely won't)?

 

[tomanddani via AccessMonster.com]

This is the frmAddresses form. It is the mainform. Its record source is
tblAddresses. The subform is frmBldgAndSystems, its record source is
tblBldgAndSystems. They are linked on AddressID. The StreetNumber and
Direction and StreetName fields are all on the main form.

 

[John W. Vinson]

This is the frmAddresses form. It is the mainform. Its record source is
tblAddresses. The subform is frmBldgAndSystems, its record source is
tblBldgAndSystems. They are linked on AddressID. The StreetNumber and
Direction and StreetName fields are all on the main form.

Hrm. The code I suggested (perhaps with some adaptation) should work. Could
you post the actual code you're using, and the SQL view of the Recordsource
(let Access convert it to a query if it asks)?

 

[tomanddani via AccessMonster.com]

This is the code I have on the form...

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strWhere As String
Dim strMsg As String
Dim iAns As Integer
strWhere = "([StreetNumber] = """ & Me.StreetNumber & """) AND (
[Direction] = """ & Me.Direction & """) AND ([StreetName] = """ & Me.
StreetName & """)"
Set rs = Me.RecordsetClone ' assume all addresses are in this form's
recordset
rs.FindFirst strWhere
If Not rs.NoMatch Then ' was a record found?
strMsg = "Duplicate of address; add it? " & vbCrLf & vbCrLf &
"Click Yes to continue, No to jump to the found record, Cancel to start over.
"
iAns = MsgBox(strMsg, vbYesNoCancel + vbDefaultButton2, "Warning")
Select Case iAns
Case vbYes ' do nothing, just add the record
Case vbNo
Cancel = True
Me.Bookmark = rs.Bookmark ' jump to the record
Case vbCancel ' cancel, start over
Cancel = True
Me.Undo
End Select
End If
Set rs = Nothing
End Sub

And this is the Record Source...

SELECT
FROM tblAddresses;

Somehow this doesnt seem right to me. When it had me make a query, it just
showed the query window with the tblAddresses table and nothing in the fields.

 

[John W. Vinson]

And this is the Record Source...

SELECT
FROM tblAddresses;

Somehow this doesnt seem right to me. When it had me make a query, it just
showed the query window with the tblAddresses table and nothing in the fields.

Odd - I'd at leaste expect SELECT * FROM tblAddresses. If you step through the
code (using F8) you can use the Immediate Window - type Ctrl-G to see it if
it's not visible - to check the values of variables to see if they're what you
intend. Is the form Filtered? when you open the recordset, type

?rs.RecordCount

in the immediate window - does it get a reasonable number of records?

 

[tomanddani via AccessMonster.com]

Im sorry you lost me on a few of those steps. The form is not based on a
query. It was made using the wizard and choosing all the fields in
tblAddresses and all the fields in tblBldgAndSystems. The form is not
filtered, and it shows all the records that are in the table. Im not sure how
to use the immediate window, It comes up but Im lost after that.
Sorry to be taking up so much of your time.

And this is the Record Source...

[quoted text clipped - 3 lines]

Somehow this doesnt seem right to me. When it had me make a query, it just
showed the query window with the tblAddresses table and nothing in the fields.

Odd - I'd at leaste expect SELECT * FROM tblAddresses. If you step through the
code (using F8) you can use the Immediate Window - type Ctrl-G to see it if
it's not visible - to check the values of variables to see if they're what you
intend. Is the form Filtered? when you open the recordset, type

?rs.RecordCount

in the immediate window - does it get a reasonable number of records?

 

[tomanddani via AccessMonster.com]

I think I figured out what you had asked me to do with the Immediate Window.
When I typed ?rs.RecordCount and hit enter it came up with 5 records which is
all the records in the table.

Im sorry you lost me on a few of those steps. The form is not based on a
query. It was made using the wizard and choosing all the fields in
tblAddresses and all the fields in tblBldgAndSystems. The form is not
filtered, and it shows all the records that are in the table. Im not sure how
to use the immediate window, It comes up but Im lost after that.
Sorry to be taking up so much of your time.
[quoted text clipped - 10 lines]

in the immediate window - does it get a reasonable number of records?

 

[Rick Brandt]

Im sorry you lost me on a few of those steps. The form is not based on a
query.

Yes it is.

It was made using the wizard and choosing all the fields in
tblAddresses and all the fields in tblBldgAndSystems.

A bound form is either bound to a table or a query. The only way to show
data from more than one table is with a query. In this case the wizard
built the query for you, but you are still using a query.

 

[tomanddani via AccessMonster.com]

Im sure your right. I guess what I had meant to say was that when the wizard
asked for a record source I selected a table as opposed to a previously built
query.