Results 1 to 2 of 2
  1. #1
    nggman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    27

    Using OpenRecordset with query with parameters

    I am trying to run an Access query in VBA that has start date and end date parameters. I have read several posts and am close to getting this I believe. I am just a little off on the syntax. Here is a snippet of code that I have. This gives me a run time error. I am just not sure of the syntax with the lines (bolded) that contains the actual parameter names. My parameters are [Enter start date] and [Enter end date].

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef


    Dim prm As DAO.Parameter
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set qdf = db.QueryDefs("MyQueryName")

    For Each prm In qdf.Parameters
    prm = Eval("[Enter Start Date]") prm = Eval("[Enter End Date]")
    Next prm

    Set rs = qdf.OpenRecordset

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    a) you can't use the same prm variable for 2 different values like that, especially on one line. If you simply split the line, prm will get one value from one line, then another.
    b) that means a loop isn't much good
    c) not sure if you can Eval a parameter prompt

    The dates should come from form controls and be validated to ensure dates are supplied. Then you can ditch Eval and the parameters if you refer to the form controls in the query. Concatenation of sql in code can also eliminate the parameter problem provided you use concatenation on the form control parts.
    Setting the parameters in the query itself is another way.
    Alternatively, ditch the loop and provide the parameter names and their values:

    Code:
    qdf.Parameters("JobID") = lngJobID
    qdf.Parameters("WorkMnth") = intMonth
    qdf.Parameters("WorkYear") = intYear
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Too few parameters error on CurrentDb.OpenRecordset
    By hfreedman1957 in forum Programming
    Replies: 4
    Last Post: 05-12-2017, 08:45 PM
  2. Too Few Parameters on .OpenRecordset()
    By Voodeux2014 in forum Forms
    Replies: 9
    Last Post: 01-28-2016, 04:45 PM
  3. Replies: 12
    Last Post: 09-04-2014, 10:53 PM
  4. Replies: 8
    Last Post: 12-21-2011, 12:50 PM
  5. OpenRecordset vs. Query
    By crispy-bacon in forum Programming
    Replies: 7
    Last Post: 07-04-2011, 09:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums