0

I have a form, with a listbox. User can choose record and add it to a list box using listbox. I want to create a VBA script to open a report and match all the values in listbox with corresponding records. So far i can do this with single one, but for many i don't know what is going on. Here is the code:

Private Sub btnSearchMany_Click()
Dim i As Long

With Me.List12

    If .ListCount = 0 Then
        MsgBox "Brak wybranych wpisow.Dodaj wpisy, wybierajac je z listy i wciskajac przycisk dodaj.", vbCritical
        Exit Sub
    End If
    For i = 0 To .ListCount - 1
        DoCmd.OpenReport "rptKKsy", acViewReport, , "[tblKKsy].[KKS]='" & .List12(i) & "'"
    Next i
End With

End Sub

List12- lisbox that user can add records "later i will rename it"
rptKKsy- my report
tblKKsy- table that stores values
KKs - one of the values stored in my table

I always getting error: method or data member not found. I tried to replace:

With Me.List12

with

With Forms("frmSearch").Form.list12

But it just created another error.
Edit: As suggested, my list box is a value list, and user can put thing in it by using a combobox and clicking a button ( button have addItem command programed). After user add some things, i want to open a report for each value stored in listbox. I dont want user to select things in listbox, because we will be using everything in it so its not nedded. I tried to use Item.Data property as June7 linked to, but ill be honest: I dont get it. Here is the code:

Private Sub btnSearchMany_Click()
    Dim i As Long
    Dim lValue As String

With Me.List12
    For i = 0 To .ListCount - 1
     If .ListCount = 0 Then
        MsgBox "Brak wybranych wpisow.Dodaj wpisy, wybierajac je z listy i wciskajac przycisk dodaj.", vbCritical
        Exit Sub
    End If
    Next i
    End With
    If i = Len(lValue) > 2 Then
        DoCmd.OpenReport "rptKKsy", acViewReport, , "[tblKKsy].[KKS]='" & .ItemData(lValue) & "'"
    End If

End Sub
Vokun
  • 3
  • 3
  • 1
    Does this answer your question? [Microsoft Access - Form list box to filter report for one to many relationship values](https://stackoverflow.com/questions/41488982/microsoft-access-form-list-box-to-filter-report-for-one-to-many-relationship-v) – June7 Feb 12 '23 at 18:21
  • Not quite. I dont need to filter values or select them, i just need to take every value stored in listbox and for each value generate report. – Vokun Feb 13 '23 at 13:08
  • Basically same principle. Instead of looping selected items, you loop the entire dataset. Can loop listbox items or since a listbox set is a query, open a recordset of the same query and loop that. https://stackoverflow.com/questions/47304589/looping-through-a-recordset-to-output-ms-access-report-to-a-pdf-file – June7 Feb 13 '23 at 19:02
  • If that still isn't what you want, edit question to clarify. A listbox displays items but isn't actually storing anything. That's what tables are for. And correction, listbox is usually based on a query but if yours is built as a ValueList, then you would loop through the listbox items, not open a recordset object. https://stackoverflow.com/questions/2933113/cycling-through-values-in-a-ms-access-list-box – June7 Feb 14 '23 at 00:42

0 Answers0