I am struggling to teach myself Access VBA. I do feel that the initial step is quite large, so I'd appreciate your help on this.
I want to be able to create a filtered report, so that the user applies the filter by means of a list box. The user should be able to choose several values from the list box.
My project is a quite large inventory database for my house. The inventory items is stored in (quite a number of) boxes. I want to create a filtered report so that only the contents of i.e. Box 1, Box 2 and Box 3 is in the list.
I borrowed a code sample from http://www.fabalou.com/Access/Reports/filterreport.asp, and adapted it to fit my database. But I can't get it to work.
Here is my code:
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In ListFilter.ItemsSelected
stDocCriteria = stDocCriteria & "[Box] = " & ListFilter.Column(0, VarItm) & " Or """
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function
And then there is a command button:
Private Sub Command2_Click()
DoCmd.OpenReport "Overview", acPreview, , GetCriteria()
End Sub
I expected it to produce a filtered report based in the user's input in the ListFilter listbox. However, a "Enter Parameter Box" shows up, requesting the user for an input for precisely the item chosen in the list box (if several items were chosen, then a syntax error pops up.
Can somebody please help me on the right track?
Thank You.