Need assistant with Access VBA coding. Thanks

Question

Hello, I am not a programmer, I have Access form that has 5 Entry fields, also created 5 different queries that runs according to the user selection by entering information on proper field and selecting “Search” icon. Those work well and can pull correct data.

Is it possible to show the result of the query on the just one "list box" according the user selection? I do not want to create 5 different List Box to accomplish this. (Please see attached screen print)

Field name are:

txtBoxNumber

txtCaseNumber

txtFileNumber

txtBuildingNumber

txtFloorNumber

List Box name “ListSubWindow”

Search icon name “cmdSearch”

 

Private Sub cmdSearch_Click()

On Error GoTo Err_cmdSearch_Click

'Declare objects

Dim dbs               As DAO.Database

Dim qdfQuery    As DAO.QueryDef

Dim rs                 As DAO.Recordset

     If txtBoxNumber <> "" Then

                 'Use current DB and qryDataBoxSearch query.

            Set dbs = CurrentDb

            Set qdfQuery = dbs.QueryDefs("qryDataBoxSearch"

            'Open recordset from query and assign to form.

            Set rs = qdfQuery.OpenRecordset

            Set Me.ListSubWindow.Recordset = rs

    ElseIf txtCaseNumber <> "" Then

   

    ElseIf txtFileNumber <> "" Then

 

    ElseIf txtBuildingNumber <> "" Then

  

    ElseIf txtFloorNumber <> "" Then

      

    End If

    DoEvents

    DoCmd.Close acForm, "frmWait"

 'Cleanup

Set qdfMembers = Nothing

Set dbs = Nothing 

End Sub

Answer 1

You can search on the basis of multiple selections in a list box by setting its MultiSelect property to Simple or Extended.  For an illustration of how to return rows which match any or all of the selected  take a look at SearchDemo.zip in my public databases folder at:

 

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

 

In this little demo file, clicking on the Select Criteria for Report button opens a dialogue form in which one or more selections can be made in a list box. The following code in the Click event procedure of the OpenReport button then opens a report filtered on the basis of the selections in the list box:

 

Private Sub cmdOpenReport_Click()

 

    Const REPORTCANCELLED = 2501

    Const MESSAGETEXT = "No matching items to report."

    Dim varItem As Variant

    Dim strStockIDList As String

    Dim strCriteria As String

    Dim ctrl As Control

   

    Set ctrl = Me.lstStock

   

    If ctrl.ItemsSelected.Count > 0 Then

        For Each varItem In ctrl.ItemsSelected

            strStockIDList = strStockIDList & "," & ctrl.ItemData(varItem)

        Next varItem

       

        ' remove leading comma

        strStockIDList = Mid(strStockIDList, 2)

       

        If optAnyOrAll = 1 Then 'any item stocked

            strCriteria = "StoreID IN(SELECT StoreID " & _

                "FROM StoreStock " & _

                "WHERE StockID IN(" & strStockIDList & "))"

        Else                    'all items stocked

            strCriteria = "StoreID IN(SELECT StoreID " & _

                "FROM StoreStock " & _

                "WHERE StockID IN(" & strStockIDList & ") " & _

                "GROUP BY StoreID " & _

                "HAVING COUNT(*) = " & ctrl.ItemsSelected.Count & ")"

        End If

       

        On Error Resume Next

        DoCmd.OpenReport "rptStoreStocks", _

            View:=acViewPreview, _

            WhereCondition:=strCriteria

         Select Case Err.Number

            Case 0

            ' no error

            Case REPORTCANCELLED

            ' anticipated error

            MsgBox MESSAGETEXT, vbInformation, "Warning"

            Case Else

            ' unknown Error

            MsgBox Err.Description, vbExclamation, "Error"

        End Select

    Else

        MsgBox "No items selected", vbInformation, "Warning"

    End If

   

End Sub