locked
take multiple input from user RRS feed

  • Question

  • I'd like to create a form that can take multiple input (i.e., Word Order Numbers) from user and then pass these inputs into a SQL Statement with a condition like "...Where WO# = WOInput1 & WOInput2 & ... & WO#=WOInput10". The result of this query is then displayed on a report with the first column is WO# and the second column is Description.

    Access VBA has the InputBox method, but I'd like to take multiple input, not just one WO.

    Can you please help? Thanks.

       
    Wednesday, February 1, 2017 4:53 PM

Answers

  • Hi,

    You can still use the InputBox() function but the user must delimit the input with something. For example, if they separate the number with a comma, you can use it to determine one number from another. To process the input, you can use the Split() function to store the numbers in an array. You can then loop through the array to create your SQL statement.

    Hope it helps...

    • Marked as answer by ttim Wednesday, February 1, 2017 5:37 PM
    Wednesday, February 1, 2017 5:00 PM

All replies

  • Hi,

    You can still use the InputBox() function but the user must delimit the input with something. For example, if they separate the number with a comma, you can use it to determine one number from another. To process the input, you can use the Split() function to store the numbers in an array. You can then loop through the array to create your SQL statement.

    Hope it helps...

    • Marked as answer by ttim Wednesday, February 1, 2017 5:37 PM
    Wednesday, February 1, 2017 5:00 PM
  • You might like to consider using a multi-select list box to select one or more order numbers from a sorted list, rather than calling the input box function.  You'll find examples in MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the top two buttons on the opening menu form open dialogue forms which include such a list box.  In the first the report is filtered by building a string expression for the WhereCondition argument of the OpenReport method as follows:

    Private Sub cmdOpenReport_Click()

        Dim varItem As Variant
        Dim strEmployeeIDList As String
        Dim strCriteria As String
        Dim ctrl As Control
        
        Set ctrl = Me.lstEmployees
        
        If ctrl.ItemsSelected.Count > 0 Then
            For Each varItem In ctrl.ItemsSelected
                strEmployeeIDList = strEmployeeIDList & "," & ctrl.ItemData(varItem)
            Next varItem
            
            ' remove leading comma
            strEmployeeIDList = Mid(strEmployeeIDList, 2)
            
            strCriteria = "EmployeeID In(" & strEmployeeIDList & ")"
            
            DoCmd.OpenReport "rptEmployees", _
                View:=acViewPreview, _
                WhereCondition:=strCriteria
        Else
            MsgBox "No employees selected", vbInformation, "Warning"
        End If
        
    End Sub

    The second uses code similar to the above to assign the value list to a hidden control in the form, which is referenced by the report's query as a parameter, using the GetToken and InParam functions published by Microsoft to simulate the use of the IN operator with parameters (the IN operator only accepts a literal string as its argument).

    You could of course use an input box to assign a value list to a hidden control in the form and reference it as a parameter in the same way.  Your decision on whether to use a list box or call the InputBox function should take into account the total number of orders.  With a list box, while less error prone than the user's typing in the value list, it could require a lot of scrolling through the list if it is a very large one, though you might be able to restrict the list box's RowSource on some basis, e.g. to unfulfilled orders, orders fulfilled within the last month etc.  Without knowing the business context I can only give you hypothetical examples of course.

    Ken Sheridan, Stafford, England

    Wednesday, February 1, 2017 5:46 PM