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

    SET CRITERIA FOR QUERY (2003)

    I have a reference query that is 10k+ records long. The code below is "After-Update" code. It copies updated control 'text102' and paste it into a query Like[ ] box to find the specific record. The code works fine on my laptop but fails on another laptop by not pasting into the Like[ ] box and waiting for input. It is probably timing related to the two different machines. Anyone have a way to set the query criteria equal to 'text102' after update without the copy/paste and start the query correctly? Once the record is found, the rest of the code copies field info from reference to current record



    Private Sub Text102_AfterUpdate()
    Dim namef As String, namel As String, emp As String
    Dim dbs As dao.Database
    Dim qdf As dao.QueryDef
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.GoToControl "text102"
    DoCmd.RunCommand acCmdCopy
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("query-active-badge-badge#")
    qdf.Parameters(0) = InputBox(qdf.Parameters(0).name, "")
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    rst.Edit

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

    Re: SET CRITERIA FOR QUERY (2003)

    The code you posted doesn't paste the value anywhere, so I don't see how it could work. Moreover, the code invokes InputBox, so it will always wait for input.

    What is your purpose? Do you want (1) to set the parameter of the query to the value of Text102 without user interference, or do you want (2) to set the default value of the input box to the value of Text102, and give the user a chance of modifying it (using InputBox) before using it as parameter of the query?

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

    Re: SET CRITERIA FOR QUERY (2003)

    I appreciate the response. Actually, the code works very well at pasting the user input of text102 and opens the query. As long as the paste is the last command before opening the query, it proceeds and finds the record I want. I use a similar code in two other DBs for grabbing content of a field and using it for report criteria. As noted in my post, it works on one PC and not on another. That aside, I do want to set the parameter of the query to the after-update content of text102. I do not want user modifying. I know the copy/paste is not the most efficient and looking for better command string is reason for post.

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

    Re: SET CRITERIA FOR QUERY (2003)

    If you don't want interaction with the user, you can use the following without setting focus to a control and without copying anything to the clipboard.

    Private Sub Text102_AfterUpdate()
    Dim namef As String, namel As String, emp As String
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("query-active-badge-badge#")
    qdf.Parameters(0) = Me.Text102
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    rst.Edit
    ...

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

    Re: SET CRITERIA FOR QUERY (2003)

    That code works. I appreciate the assistance. I will get it loaded on the "trouble pc" to test it out as well.

Posting Permissions

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