Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    VBA Script - Data fr Access ((VBA Excel 2003))

    Hi,

    Im currently writing a macro to connect to an access database to pull through some data into an excel worksheet and Im getting the following message;
    Run-Time error '3061' ; Too few parameters. Expected 3,
    with the debug highlighting the following syntax;
    Set rst = dbs.OpenRecordset("SELECT [Completion Date],[FileNo] FROM qryBillL")

    I have seen this error somewhere before but Im having a bit of a block. Can anyone tell me what it means please??

    Many Thanks.
    Lee

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

    Re: VBA Script - Data fr Access ((VBA Excel 2003))

    Is qryBillL a parameter query? Or does it refer to an Access form? The DAO OpenRecordset method can't handle those.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA Script - Data fr Access ((VBA Excel 2003))

    Hans,

    Im under the impression it is a parameter query. Does that mean I cant use it? Would it be better to use a table??

    Cheers.
    Lee

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

    Re: VBA Script - Data fr Access ((VBA Excel 2003))

    A parameter query, when run in Access itself, prompts the user to enter one or more values, these are then used to filter the records.
    The OpenRecordset method opens a recordset in code, not interactively, so it cannot display parameter prompts.
    It is possible to specify the values for the parameters in code - post back if you want to do that.
    Otherwise, you should open a recordset on a table or on a query that doesn't prompt for a value.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA Script - Data fr Access ((VBA Excel 2003))

    Ok, thats great, thanks for the guidance Hans.

    I'll open a recordset on a table instead.

    Many Thanks.
    Lee

  6. #6
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Script - Data fr Access ((VBA Excel 2003))

    How can I specify the parameter value of a query using code?
    Thanks
    chuck

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

    Re: VBA Script - Data fr Access ((VBA Excel 2003))

    The parameter must have been declared explicitly in the design of the query in Query | Parameters. You can then use code like this:
    <code>
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qryTest")
    qdf.Parameters("[Start Date]") = Me.txtStartDate
    Set rst = qdf.OpenRecordset
    ...
    </code>
    This code sets the value of the parameter [Start Date] to the value of the text box txtStartDate (on the form that runs the code), then opens a recordset on the query.

Posting Permissions

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