Re: DoCmd.RunSQL

From: JV (JV_at_discussions.microsoft.com)
Date: 07/28/04 

Date: Wed, 28 Jul 2004 07:49:09 -0700

Thanks for the help, Douglas.

It did not allow me to use DAO.Recordset as a data type. So I used recordset as a data type and kept everything else the same. I get an error message now which says Type mismatch. What should I do now?

"Douglas J. Steele" wrote:

> You need to open a recordset, check that there's something in the recordset,
> then retrieve the value from that recordset.
>
> Using DAO, this would be something like:
>
> Private Sub cmd_password_Click()
> On Error GoTo Err_cmd_password_Click
>
> Dim Passw As String
> Dim MasterP As Integer
> Dim rs As DAO.Recordset
>
> Passw = "SELECT tbl_2004_Library.Reference, tbl_2004_Library.rc, " & _
> "tbl_2004_Library.job, tbl_2004_Library.prime, " & _
> "tbl_2004_Library.account, tbl_2004_Library.desc, " & _
> "tbl_2004_Library.us_amt, tbl_2004_Library.MP " & _
> "FROM tbl_2004_Library " & _
> "WHERE tbl_2004_Library.MP=" & MasP
>
> Set rs = CurrentDb().OpenRecordset(Passw)
> If Not rs.BOF And Not rs.EOF Then
> ' You can get the value of the matching fields using
> ' rs!Reference, rs!rc, rs!job, etc.
> Else
> ' No record matches your criteria
> End If
>
> rs.Close
> Set rs = Nothing
>
> Exit_cmd_password_Click:
> Exit Sub
>
> Err_cmd_password_Click:
> MsgBox Err.DESCRIPTION
> Resume Exit_cmd_password_Click
>
> End Sub
>
> Note that the WHERE clause that's being set assumes MP is numeric. If it's
> text, you need to enclose the value in quotes:
>
> "WHERE tbl_2004_Library.MP=" & Chr$(34) & MasP & Chr$(34)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>

Relevant Pages

  • ADO Recordset truncating memo field
    ... dates for weekends and holidays) from our corporate Oracle database so it can ... The recordset data is displayed in more unbound controls on the ... Dim rst As ADODB.Recordset, frm As Form ... Exit Sub ...
    (microsoft.public.access.modulesdaovba)
  • Re: Type Mismatch
    ... recordset then you need to close it and then destroy the reference to it. ... I am not fond of using "Exit Sub" to control logic (except for error ... Dim stDocName As String ... Dim stLinkCriteria As String ...
    (microsoft.public.access.forms)
  • Validating data using VB code in an unbound form
    ... Dim dbs as database ... >code before I add the recordset... ... >Set db = CurrentDb() ... > Exit Sub ...
    (microsoft.public.access.modulesdaovba)
  • RE: ADO Recordset truncating memo field
    ... I think the problem stemmed from grouping the recordset. ... > Dim rst As ADODB.Recordset, ... > Exit Sub ... > ' Check number of trips and display data as needed. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Export query as .pab or .pst
    ... Oh, sorry about that John. ... >>found out that my main issue was that my recordset was not defined properly. ... >> Dim objFolder As Outlook.MAPIFolder ... >> Exit Sub ...
    (microsoft.public.access.externaldata)