Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Question Unanswered: How to use variable from InputBox in other queries?

    I am using the InputBox function to capture a user variable to use in a stored procudure within the vb code in Access. How can I use that same variable in another query I am running in the same database?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution:
    Code:
    Function QueryParam()
    '
    ' My_Query is defined as:
    '
    '   PARAMETERS Param_1 Long;
    '   SELECT Tbl_CF_DATA.SysCounter, Tbl_CF_DATA.Customer, Tbl_CF_DATA.Order_Number
    '   FROM Tbl_CF_DATA
    '   WHERE (Tbl_CF_DATA.Customer_ID=Param_1);
    '
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim Answer as Variant
        
        Answer = InputBox("Please enter Customer ID:")
        If IsNumeric(Answer) = True Then
            Set dbs = CurrentDb
            Set qdf = dbs.QueryDefs("My_Query")
            qdf.Parameters("Param_1") = True
     '       dbs.QueryDefs.Refresh
            DoCmd.OpenQuery "My_Query"
        End If
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Thanks, I want to make sure this is going to do what I need. Are you saying that the query:

    ' My_Query is defined as:
    '
    ' PARAMETERS Param_1 Long;
    ' SELECT Tbl_CF_DATA.SysCounter, Tbl_CF_DATA.Customer, Tbl_CF_DATA.Order_Number
    ' FROM Tbl_CF_DATA
    ' WHERE (Tbl_CF_DATA.Customer_ID=Param_1);

    is a query that I can have in another report in the database and it will use the same input variable?

    The vb code I have for the inputbox is in a report and that variable I want to use in 3 subreports in the same report. but I have it setup as a query in the RecordSource under the Data tab of the Property sheet.

    I am not sure what you are suggesting is what I am trying to do. Does that explain better?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    My answer supposed that there was a query in use. By query I mean a Query object stored in the database. You seem to use several terms (stored procedure, query, recordsource) in a way that's a little confusing, at least to me.

    If the RecordSource of the Report is a SQL statement, you can assemble this SQL statement when the report is open or you can pass the SQL statement to the report when opening it, using the OpenArgs property. Other solutions exist such as using a public function that will return the SQL statement and set the RecordSource of the report equal to the value returned by the function.

    Your original question was about using a variable in a query, now its about subreports. Forms (and SubForms) and Reports (and SubReports) have a Filter property that can be used to limit the range of their data set. If the value you want to use is known at the Report level you can use it to compose the filter property for the subreports. You can also assemble the SQL statement used as RecordSource for the subreports. A subform/subreport control also have LinkChildFields and LinkMasterFields properties that can be used to create a relationship between the parent object and its child objects, etc.

    I don't have enough details to be more specific but several solutions do exist.
    Have a nice day!

Posting Permissions

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