Type mismatch in ADO Recordset Open

[Chuckp]

I'm entirely new to ADO in Access 2000 and I'm simply trying to reposition a
cursor back to the correct record in a form following a requery. I'm going
bit by bit (haven't gotten to the part where I actually have to display the
record in the form) but the I get a "Data type mismatch in criteria
expression" error when I try to open the recordset with the query below:

Dim cnn As ADODB.Connection
Dim rstview As ADODB.Recordset
Dim strISBN As String

Set rstview = New ADODB.Recordset

If Me.[Order: #] <> "" Then
Forms!frmBook![Order#] = Me.[Order: #]
strISBN = Forms!frmBook![ISBN: #]
Forms!frmBook.Requery
rstview.ActiveConnection = CurrentProject.Connection
rstview.CursorType = adOpenStatic
rstview.Source = "Select [Book Information].* from [Book Information]
Where [Book Information].[ISBN: #] = " & strISBN
^ this is the line it doesn't like
rstview.Open
rstview.Close
Set rstview = Nothing
Else
MsgBox ("You must put a value in the Order Number field")
End If

The idea is to get frmBook to display the updated [ISBN: #] as well as to
get a subform on the calling form to show updates.

Chuck

 

[Graham R Seach]

I haven't looked at the rest of your code, but you should enclose strISBN in
quotes (three on the left and four on the right), so Access knows to treat
it as a string.

Where [Book Information].[ISBN: #] = """ & strISBN & """"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Chuck]

Thanks Graham,

I'm not sure I understand what your suggesting -- strISBN is a variable of
type string. Why would I enclose it in quotes? I set the variable by using
the value in a bound textbox on the form. I then run a SQL query as a
parameter in the Open method for the recordset. That query is in quotes,
although it may have broken across lines in the post to the group. I was
just appending the variable to the explicit string. If I enclose the
variable name in quotes, aren't I just telling Access to search the ISBN: #
field of the Book Information table for the literal value "strISBN"? Also,
why the three quotes and the four? In any case, when I give it to Access
that way I get a compile error. (Sorry about the field names -- I inherited
this database from someone else.)

Chuck

I haven't looked at the rest of your code, but you should enclose strISBN in
quotes (three on the left and four on the right), so Access knows to treat
it as a string.

Where [Book Information].[ISBN: #] = """ & strISBN & """"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

I'm entirely new to ADO in Access 2000 and I'm simply trying to

reposition

a
cursor back to the correct record in a form following a requery. I'm going
bit by bit (haven't gotten to the part where I actually have to display the
record in the form) but the I get a "Data type mismatch in criteria
expression" error when I try to open the recordset with the query below:

Dim cnn As ADODB.Connection
Dim rstview As ADODB.Recordset
Dim strISBN As String

Set rstview = New ADODB.Recordset

If Me.[Order: #] <> "" Then
Forms!frmBook![Order#] = Me.[Order: #]
strISBN = Forms!frmBook![ISBN: #]
Forms!frmBook.Requery
rstview.ActiveConnection = CurrentProject.Connection
rstview.CursorType = adOpenStatic
rstview.Source = "Select [Book Information].* from [Book Information]
Where [Book Information].[ISBN: #] = " & strISBN
^ this is the line it doesn't like
rstview.Open
rstview.Close
Set rstview = Nothing
Else
MsgBox ("You must put a value in the Order Number field")
End If

The idea is to get frmBook to display the updated [ISBN: #] as well as to
get a subform on the calling form to show updates.

Chuck

 

[Graham R Seach]

Chuck,

Enclosing the variable in quotes as I suggest won't sorce Access to search
for the literal value "strISBN".

Run this test, and you'll see what I mean:
Dim strSQL As String
Dim strISBN As String

strISBN = "ABC"
strSQL = "Where [Book Information].[ISBN: #] = " & strISBN
Debug.Print strSQL
strSQL = "Where [Book Information].[ISBN: #] = """ & strISBN & """"
Debug.Print strSQL

In the first Debug.Print, you can see that the string value supplied by
strISBN is not explicitly declared in the SQL clause as a string, because it
is not enclosed in quotes as it is in the second Debug.Print.

You put 3 quotes on the left and 4 on the right, because whenever you want
to literally use the quote character in a string, you must declare it twice.
Access knows that if it's declared twice, it should be represented as a
literal character. The third " (on the left) closes the string as normal.
You use four " characters on the right for the same reason; the first one
opens the string, the next two declare a literal quote character, and the
last one closes the string.

If you're getting a compile error, it's not because of the literal strings.
It's because there is a problem elsewhere. Specifically what is the compile
error pointing to?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Thanks Graham,

I'm not sure I understand what your suggesting -- strISBN is a variable of
type string. Why would I enclose it in quotes? I set the variable by using
the value in a bound textbox on the form. I then run a SQL query as a
parameter in the Open method for the recordset. That query is in quotes,
although it may have broken across lines in the post to the group. I was
just appending the variable to the explicit string. If I enclose the
variable name in quotes, aren't I just telling Access to search the ISBN: #
field of the Book Information table for the literal value "strISBN"? Also,
why the three quotes and the four? In any case, when I give it to Access
that way I get a compile error. (Sorry about the field names -- I inherited
this database from someone else.)

Chuck

I haven't looked at the rest of your code, but you should enclose

strISBN

in
quotes (three on the left and four on the right), so Access knows to treat
it as a string.

Where [Book Information].[ISBN: #] = """ & strISBN & """"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

I'm entirely new to ADO in Access 2000 and I'm simply trying to

reposition

a
cursor back to the correct record in a form following a requery. I'm going
bit by bit (haven't gotten to the part where I actually have to

display
the

record in the form) but the I get a "Data type mismatch in criteria
expression" error when I try to open the recordset with the query below:

Dim cnn As ADODB.Connection
Dim rstview As ADODB.Recordset
Dim strISBN As String

Set rstview = New ADODB.Recordset

If Me.[Order: #] <> "" Then
Forms!frmBook![Order#] = Me.[Order: #]
strISBN = Forms!frmBook![ISBN: #]
Forms!frmBook.Requery
rstview.ActiveConnection = CurrentProject.Connection
rstview.CursorType = adOpenStatic
rstview.Source = "Select [Book Information].* from [Book Information]
Where [Book Information].[ISBN: #] = " & strISBN
^ this is the line it doesn't like
rstview.Open
rstview.Close
Set rstview = Nothing
Else
MsgBox ("You must put a value in the Order Number field")
End If

The idea is to get frmBook to display the updated [ISBN: #] as well as to
get a subform on the calling form to show updates.

Chuck