listbox ItemsSelected in ms access-CodePudding

I have a form contains of 3 listboxes that will filter my report based on the selected items in my query I used the below criteria for the combobox and I tried it on the listbox but it does not work!

Like [Forms]![Statusfrm]![FieldCombo] & "*"

any advise please?

I also tried this but when I select one selection it is not showing records for the same selection!

Private Sub Command26_Click()
   On Error GoTo ControlError
   Set ctl = Me.Combo22 'frm!Combo22
   Set ctl2 = Me.Combo24
   
   'Set rpt = Foms!rpt
   
   If Me.Combo22.ListIndex <> -1 Then  'And Me.Combo24.ListIndex <> -1
      miFiltro = "id in("
      For Each varItm In ctl.ItemsSelected
       'miFiltro = miFiltro & "'" & varItm & "',"
       miFiltro = miFiltro & varItm & ","
       'Lista27.AddItem varItm
     
       'ctl.ItemData (varItm)
    
      Next varItm
   
      miFiltro = Mid(miFiltro, 1, Len(miFiltro) - 1)
      miFiltro = miFiltro & ")"
      'MsgBox (miFiltro)
      If miFiltro <> "" Then
       DoCmd.OpenReport "Rpt", acViewPreview, , miFiltro
       miFiltro = ""
      End If
      
     'Aplicamos el filtro al formulario
     'Me.Filter = miFiltro
     'Me.FilterOn = True
   Else
     MsgBox ("Please select data")
     Me.Combo22.SetFocus
   End If
     
   'DoCmd.OpenReport "Rpt", acPreview, , Me.Filter
   

ControlError:
    MsgBox "Encontré el error" & Err.number & " " & Err.Description
     
End Sub

CodePudding user response：

You can change your Report to be based on a fixed DB query. When the user clicks the "View Report" button, add the code:

Dim strSQL As String
strSQL = "SELECT * FROM YourTable WHERE [Place] = '"   Me!Statusfrm   "'" 
CurrentDB.QueryDefs("TheAccessQueryObjectTheReportIsBasedOn").SQL = strSQL 
    
DoCmd.OpenReport "YourReport"

In this model, you are setting the SQL of the Query object at the Access DB container level, then opening the report. You can debug your VBA code more easily this way, instead of relying on a RecordSource query with a runtime parameter referencing the form. You can also close the form if you want to before opening the report.

The WHERE condition for your strSQL variable can be built up based on input (or lack thereof) from the user. For example:

If Nz(First(Me!categoryListBox.SelectedItems).Value,"") <> "" Then
   strSQL = strSQL   " AND [Category] = '" Me!categoryListBox.SelectedItems).Value   "'"
End

If Nz(First(Me!placeListBox.SelectedItems).Value,"") <> "" Then
   strSQL = strSQL   " AND [Place] = '" Me!placeListBox.SelectedItems).Value   "'"
End

Also, is your list box bound to an ID or the text of the item selected? You may be getting an ID vs the text you are trying to use with the asterisk wildcard. A list box can have multiple selections, so you need to disable it, or do something like :

strSQL = "SELECT * FROM YourTable WHERE [Place] = '"   First(Me!statusfrm.SelectedItems).Value   "'"

If the user can only select one value, a combobox it the better choice. Either way, by building up an SQL statement in VBA that uses the forms value, you can debug the syntax of the SQL more easily.