Results 1 to 3 of 3

'Enter Parameter Value' prompt showing the parameter value

  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    67

    Question 'Enter Parameter Value' prompt showing the parameter value

    Hi Folks,



    A bit confused here.. I have an MS Access Form in which a value is entered into a Text Box, and then that value is supposed to be used as a parameter in a query (upon clicking a button next to said Text Box).
    Currently, when I enter a value into the Text Box and then click the button to run the query, I receive the 'Enter Parameter Value' prompt with the current value of that Text Box as the field that it's evidently looking for. The specific value that I type into the Text Box is what shows on that dialog box. If I re-enter that value into the actual prompt and click "OK", the query then runs with expected result. So essentially, it works - but I have to enter the parameter value into the Text Box on the form, and then re-enter that same value into the 'Enter Parameter Value' prompt. The latter is what I'm confused about.
    I'm sure I'm missing something obvious! Below is the code used on the form (within the 'OnClick' event of the button). Thanks!

    Code:
    Dim db As DAO.DatabaseDim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strUserID As String
    Dim strSQL As String
    
    
    strUserID = Me!txtUID
    
    
    strSQL = "SELECT tblImport_Final.UserID, tblImport_Final.Title, tblImport_Final.Location " & _
            " FROM tblImport_Final " & _
            " WHERE tblImport_Final.UserID = " & [Forms]![frmUser_Report]![txtUID]
    
    
    
    
        DoCmd.DeleteObject acQuery, "tempQry"
    
    
        Set db = CurrentDb
    
    
        Set qdf = db.CreateQueryDef("tempQry", strSQL)
    
    
        DoCmd.OpenQuery ("tempQry")

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,884
    Unresolved parameters cause more than one type of behaviour, including errors. There are several ways to deal with it, some much more complex than what I've found often works, and that is to assign the parameter value to a variable and use the variable rather than a reference. So assuming ID is a long:
    Code:
    Dim lngID As Long
    
    lngID = [Forms]![frmUser_Report]![txtUID] 'or is it just Me.txtUID?
    
    strSQL = "SELECT tblImport_Final.UserID, tblImport_Final.Title, tblImport_Final.Location " & _
       " FROM tblImport_Final WHERE tblImport_Final.UserID = " & lngID
    I fail to see the point of repeatedly deleting and recreating a query def (most say it promotes bloat and corruption, so I joined that club) when you can simply run sql in code - which is where you are already. You can also create a qdef solely in memory by not giving it a name (use zls ("") instead).

    There must also be more code? I don't see where you use recordset, nor do you need to open a select query for use with a recordset.
    Last edited by Micron; 07-13-2019 at 02:47 PM. Reason: clarification & comments
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,476
    I avoid repeatedly deleting and recreating table or query objects. I use 'temp' table - table is permanent but data is temporary.

    Why do you find it necessary to delete/recreate query?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 11
    Last Post: 05-03-2019, 12:42 PM
  2. Parameter value prompt
    By templeowls in forum Queries
    Replies: 1
    Last Post: 02-27-2019, 04:17 PM
  3. Replies: 2
    Last Post: 03-12-2014, 11:14 AM
  4. Replies: 13
    Last Post: 01-10-2012, 09:56 AM
  5. Get parameter prompt
    By yawalias in forum Queries
    Replies: 1
    Last Post: 12-22-2011, 09:25 AM

Tags for this Thread

Posting Permissions

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