Re: Type mismatch in ADO Recordset Open

From: Chuck (chuckp_at_hnoc.org)
Date: 06/24/04 

Date: Thu, 24 Jun 2004 10:29:20 -0500

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" <gseach@NOSPAMpacificdb.com.au> wrote in message
news:OAt74#ZWEHA.212@TK2MSFTNGP11.phx.gbl...
> 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" <chuckp@hnoc.org> wrote in message
> news:OzHXdlZWEHA.1356@TK2MSFTNGP09.phx.gbl...
> > 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
> >
> >
>
>

Relevant Pages

  • Re: HANDY FARM DEVICES
    ... >>That's because you didn't enclose the string in quotes. ... >>Try the same phrase in quotes and you'll find it returns 4 (Yes, ...
    (rec.woodworking)
  • Re: Type mismatch in ADO Recordset Open
    ... Enclosing the variable in quotes as I suggest won't sorce Access to search ... Dim strISBN As String ... Why would I enclose it in quotes? ...
    (microsoft.public.access.formscoding)
  • Re: INSERT records
    ... Don't enclose integer values between quotes. ... Dim sql As String ... INSERT INTO (TypeID, OrderID, ClientID) ...
    (microsoft.public.access.adp.sqlserver)
  • Re: VBA code for Excel File Save As
    ... If mRateName and Validade are strings, you need to enclose them with double ... quotes. ... > Dim NewName As String ...
    (microsoft.public.excel.programming)
  • Re: Update existing values incrementally w/UPDATE SQL
    ... I think it would be safe to say, use the Dim statement any time you are ... Dim basically tells vba that you want to initiate a new variable. ... As far as quotes go, this was one of the trickiest ... Consider vba's interpretation of a string: ...
    (microsoft.public.access.modulesdaovba)
Loading