Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OpenRecordSet method, HELP!!! (Access 2k)

    Hello again

    Could some please tell me why I get the error message 'variable not defined' or 'too few parameters, expect 1' with the code below? Every book I have with examples of this code looks like this, all the examples I've seen on here look like this, some times with out the speech marks, but i've tried that..... <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    What ever I've done wrong is going to be simple and probably very stupid, some one put me out of my misery <img src=/S/please.gif border=0 alt=please width=31 height=23>

    Private Sub enterbtn_Click()
    On Error GoTo err_enterbtn_click

    Dim strpassword As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strqry As String

    Set dbs = currentdb()
    Set rst = dbs.OpenRecordset("passwrd_qry")

    strpassword = (rst!user_pw)

    If Me.passwordtxt = strpassword Then
    DoCmd.Close acForm, "startup", acSaveNo
    DoCmd.Close acForm, "passwordfrm"
    MsgBox "You are now entering the heart of the database, please take care", vbOKOnly, "Last Chance to Back out <img src=/S/smile.gif border=0 alt=smile width=15 height=15>"
    Else
    MsgBox "Polite Message", vbExclamation, "Title"
    End If

    exit_enterbtn_click:
    Exit Sub

    err_enterbtn_click:
    MsgBox Err.Description
    Resume exit_enterbtn_click

    End Sub


    thanks

    Ian

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OpenRecordSet method, HELP!!! (Access 2k)

    I guess your query (passwrd_qry) has a parameter (User ID ?) that has to be supplied.
    You can either reference your startup form directly in the query (check spelling if you do so already). There is a wizard that will help you
    Or open your recordset on a query

    dim Qry as querydef
    set Qry = dbs.Querydefs("passwrd_qry")
    Qry![USer ID] = me![User ID]
    set rst = Qry.OpenRecordset()
    ...
    set Qry = nothing
    set rst = nothing
    set dbs = nothing

    tidying up your vars by setting to nothing is good VBA practice - it sometimes gets upset if you don't do that.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OpenRecordSet method, HELP!!! (Access 2k)

    This last point about setting variables to Nothing. Is this the same as closing them with e.g. rs.close? Should I be doing both?

    David Parton

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OpenRecordSet method, HELP!!! (Access 2k)

    You should always release the memory used by object variables by setting them to Nothing after you are done with them. Object variables are the ones you assign a value to with the Set keyword.

    ' assign value
    Set varname = value
    ' code to do something with the variable
    ...
    ' release memory
    Set varname = Nothing

    If you open a database or recordset (or, if you use Automation, a Word document or Excel workbook or ...), you should also close it (before setting it to Nothing):

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblData")
    Do While Not rs.EOF
    ' Do something with record
    ...
    rs.MoveNext
    Loop
    ' Close recordset and release memory
    rs.Close
    Set rs = Nothing
    ' No need to close db - we didn't assign it using OpenDatabase
    Set db = Nothing

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OpenRecordSet method, HELP!!! (Access 2k)

    Andrew / Hans

    Thanks for the information, I haven't put the 'set var = nothing' in yet as the code isn't working (I think it was one of you I saw telling some one else about this that got me doing it), I'm a bit lazy like that, get it to work then tidy it up (the error trapping is there as I used the button wizard...).

    The query does reference the password form for a user_id, I used the 'build wizard' to set the statement. The query works when I open it with the form open in form view, and a user _id selected in the form, so the reference is correct?? Should I be using the () after the currentdb? (for some reason my VBA editor always resets CurrentDb to currentdb).

    I'm intrigued by the QueryDef bit, I thought the OpenRecordset was the only way of opening a record set..... Where I use a query for my source data (nearly all the time) is it better to use QueryDef to make the data available?

    Back to my VBA editor, lunch might have cleared my tiny a bit.

    Thanks again for the suggestions

    Ian

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OpenRecordSet method, HELP!!! (Access 2k)

    Ian,

    OpenRecordset is a DAO method. DAO knows nothing about Access forms and controls, so if your query refers to something like [Forms]![frmPassword]![txtUser_ID], DAO doesn't recognise it and interprets it as a parameter.

    You can set the parameter in code, or construct the SQL for the query in code, instead of using the stored query.

    Suppose that the SQL for your query is

    SELECT User_PW FROM tblPassword WHERE User_ID = [Forms]![frmPassword]![txtUser_ID]

    Then you could do the following:

    Dim strSQL As String
    strSQL = "SELECT User_PW FROM tblPassword WHERE User_ID = " & Chr(34) & [Forms]![frmPassword]![txtUser_ID] & Chr(34)
    Set rs = db.OpenRecordset(strSQL)

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OpenRecordSet method, HELP!!! (Access 2k)

    Hans

    <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> , this is why the thing hasn't been working..... Where it's been returning 'too few parameters' as the error it's because I'm using a query controlled by a form?????? <img src=/S/doh.gif border=0 alt=doh width=15 height=15> <img src=/S/doh.gif border=0 alt=doh width=15 height=15>.

    If I open the query using the querydef bit and then put into the code the relevant part of Andrew's code, 'Qry![user_id} = me![user_id]' (altered to suit my variables) will this set the query parameter? I think it will, but after this morning I'm feeling a tad fragile confidence wise.

    Thanks again for the sanity.

    Ian

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: OpenRecordSet method, HELP!!! (Access 2k)

    If you use a querydef object, it also needs to be set to nothing. Otherwise, it can keep the database from closing just like the other objects can.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OpenRecordSet method, HELP!!! (Access 2k)

    Using a SQL statement constructed in code should work.

    Andy's method should work if modified slightly, to set the parameter instead of a field (of course, you must substitute the appropriate names)
    <UL><LI>Open the query in design view
    <LI>Select Query/Parameters...
    <LI>Create a parameter named exactly the same as the reference to the form, and of type Text
    For instance, if the reference to the form is [Forms]![frmPassword]![txtUser_ID], you must enter that as name for the parameter.
    <LI>Close and save the query.
    <LI>Use code like this:[/list]Private Sub Enterbtn_Click()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qryPassword")
    qdf.Parameters("[Forms]![frmPassword]![txtUser_ID]") = Forms!frmPassword!txtUser_ID
    Set rst = qdf.OpenRecordset

    ' do something with it
    ' ...

    Exit_Handler:
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OpenRecordSet method, HELP!!! (Access 2k)

    Use both. Closing the object just closes it. It still exists. Setting it to nothing destroy the object. Access ought to clean up nicely when the object goes out of scope, unfortunately it seems that it doesn't always do so. Or at least it was a potential source of problems.

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OpenRecordSet method, HELP!!! (Access 2k)

    Querydefs are for parameter based queries - you can supply a value for the parameter at runtime without having it looked up on a form.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •