Type mismatch in ADO Recordset Open

  • Thread starter Thread starter Chuckp
  • Start date Start date
C

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
 
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

Graham R Seach said:
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


Chuckp said:
I'm entirely new to ADO in Access 2000 and I'm simply trying to
Click to expand...
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
Click to expand...
Click to expand...
 
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


Chuck said:
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

Graham R Seach said:
I haven't looked at the rest of your code, but you should enclose
Click to expand...
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


Chuckp said:
I'm entirely new to ADO in Access 2000 and I'm simply trying to
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top