Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open query with criteria (2k)

    Using DAO, can I open a query that has criteria defined by user? When I run code below, it fails with run-time 3061, Too few parameters, Expected 1. I want to open the query based on criteria so that it only finds specific records to perform the rest of code.

    Dim rst As dao.Recordset
    Dim dbs As dao.Database
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("query-alert", , dbOpenDynaset)
    Do While Not rst.EOF

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Open query with criteria (2k)

    There should be no reason you can't. What the error says is that you have an undefined field name in the query.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open query with criteria (2k)

    There are no undefined fields in the query. The criteria field for the query is set up for user entry with a Like [rg?] criteria. When the query is run manually, it asked for user input and then displays results. If I remove the criteria, the SET code opens the query just fine. It is only when I have a criteria input required that the SET RST code fails. Is there a parameter in the SET RST command that defines criteria? Or another way?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Open query with criteria (2k)

    Look at <post#=207160>post 207160</post#> for more details.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Open query with criteria (2k)

    You're talking about a parameter query and when you run one in code, you have to pass the parameter into the query and open the recordset on the querydef rather than on the database. You can get the criteria input in various ways including prompting the user at the time. Try it like this:

    Dim qdf As DAO.QueryDef
    Dim rst As dao.Recordset
    Dim dbs As dao.Database
    Set dbs = CurrentDb

    Set qdf = dbs.QueryDefs("query-alert")
    qdf.Parameters(0) = InputBox(qdf.Parameters(0).Name, "Enter Value")

    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    Do While Not rst.EOF

    ... etc.

    Don't forget to destroy the rst, qdf and dbs objects when you exit the routine.
    Charlotte

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

    Re: Open query with criteria (2k)

    Further to the code from Charlotte you can also specify the parameters by name for readability of your code
    dim var as Variant
    var = InputBox("inputBoxTitle", "Enter Value")
    'Test var for validity, exit if not acceptable...
    qdf.Parameters("paramNameGoesHere") = var

  7. #7
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open query with criteria (2k)

    The SET command appears to now work. Intent is to read query one line at a time and mail a report to user found in record. The SendObject line now generates a failure message "The formats that enable you to output data as a MSExcel, RTF, MSText, or HTML file are missing from the Windows Registry".
    I have other databases running that generate RTF format mail without problems. This error message is only occuring here in this database. Is the registry database specific and if so, where do I go to add the formats?
    Erroring Line of code:
    DoCmd.SendObject acReport, "id-earlynote", "richtextformat(*.rtf)", mailto, "", "", "ERT NOTICE", "", False

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

    Re: Open query with criteria (2k)

    As far as I know, the third argument to SendObject (the export format) should be<pre>"Rich Text Format (*.rtf)"</pre>

    Note the spaces. But it's a lot easier and safer to use the symbolic constant acFormatRTF (which is exactly that string) instead.

  9. #9
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open query with criteria (2k)

    The rtf command without spaces works in my other databases. I did however use the ACformatRTF command in this new code and it is working now. Thanks for the input

Posting Permissions

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