Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2014
    Posts
    2

    Unanswered: Parameters in query no longer working in Access 2010

    Hi,

    I recently converted from Access 2002 to Access 2010 and it appears the way parameters are specified in a query works differently. I have yet to figure it out. The parameter is coming from VBA code and gets used in a SELECT statement.

    Here is a VBA snippet:

    Public XProcessid As String


    Private Sub Activity()

    XProcessid = "6"

    ...

    End Sub


    Here's the problem part of the query to illustrate the problem... not the entire query, and this is intended to just illustrate the problem, not function completely.

    SELECT [dictionary].ID
    FROM [dictionary]
    WHERE ((Processids.Sprocessid) = [XProcessid])

    "dictionary" is a table.
    "Processids.Sprocessid" is referring to a column "Sprocessid" in the table "Processids"
    "XProcessid" is a parameter to the query that was declared and set in the VBA code


    This query is used to set up a list in a column based on the preceding column selection. When the query is run, a popup appears asking for a value for XProcessid, which was already set in the VBA code.

    We did not see this popup in Access 2002. But Access 2010 gives the popup.

    If I put hardcoded values in the query and run it by hand, the results are fine. It appears to me there was some sort of change when using square brackets around the parameter in the query. I thought maybe it was just getting confused as some table references and the parameter references were both with []'s. But, from what I've seen, this gets sorted out by context.

    Anyone have thoughts as to how things have changed and how to get by it?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Where is this query used
    Is it saved as a query or run as part of a recordset within a vba procedure

    Personally id expect using sonething called id is numeric not string. If processid is numeric Its possible the JET engine has changed and thats causing a problem
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2014
    Posts
    2
    Quote Originally Posted by healdem View Post
    Where is this query used
    Is it saved as a query or run as part of a recordset within a vba procedure

    Personally id expect using sonething called id is numeric not string. If processid is numeric Its possible the JET engine has changed and thats causing a problem
    Thank you for your reply. The query is used to provide a set of values for another column in the data table. So, they select one column, and based on that selection, they have a specific set of items from which to choose the value for the next column.

    I never considered the string/numeric possibility. I will look into that.

Posting Permissions

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