Create a report from multi list query

[rio]

i get this example db from my friend. i try to play with it to learning more about queries. i need help from anyone can teach me. the question is, how to make it open the report on the current filter.



Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me.txtFirstName = ""
Me.txtLastName = ""
Me.txtMaxAge = ""
Me.txtMinAge = ""
Me.cmbCompany = 0
Me.cmbCountry = 0

' De-select each item in Color List (multiselect list)
For intIndex = 0 To Me.lstFavColor.ListCount - 1
Me.lstFavColor.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter

' Requery the subform
Me.frmsubClients.Requery
End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors

' Check for LIKE First Name
If Me.txtFirstName > "" Then
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
End If

' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName & "*"" AND "
End If

' Check for min Age
If Me.txtMinAge > "" Then
varWhere = varWhere & "[Age] > " & Me.txtMinAge & " AND "
End If

' Check for max Age
If Me.txtMaxAge > "" Then
varWhere = varWhere & "[Age] < " & Me.txtMaxAge & " AND "
End If

' Check for CompanyID
If Me.cmbCompany > 0 Then
varWhere = varWhere & "[CompanyID] = " & Me.cmbCompany & " AND "
End If

' Check for CountryID
If Me.cmbCountry > 0 Then
varWhere = varWhere & "[CountryID] = " & Me.cmbCountry & " AND "
End If

' Check for Colors in multiselect list
For Each varItem In Me.lstFavColor.ItemsSelected
varColor = varColor & "[FavColor] = """ & _
Me.lstFavColor.ItemData(varItem) & """ OR "

Next

' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

 

[jsv2002]

You build a report based on your a stored query and in (like you one you have called qryClientData) then in the criteria of the the ClientID field put something like:

[Forms]![frmSearch]![ClientID]

Then have a command button on your form that opens your report.

Good luck John

 

[wiklendt]

use the open comand:

DoCmd.OpenReport YourReportName, acViewPreview, , strWhere

make sure you add all the commas - they tell access which argument you are using (access likes them in a specific order, so the WHERE statement is in argument 4 in that statement)

 

[wiklendt]

and like jsv said, you first need a report already made with the correct fields in it so the data slots in where it's supposed to from that SQL query

 

[rio]

Thanks.
jsv2002
wiklendt

now i learn something new.

 

[rio]

new question. as we know this code is for one list only. can i make it with 2 list. for example i have 2 color list (lstFavColor and lstFavColor2)

Original code:

' Check for Colors in multiselect list
For Each varItem In Me.lstFavColor.ItemsSelected
varColor = varColor & "[FavColor] = """ & _
Me.lstFavColor.ItemData(varItem) & """ OR "

i try to make it like this:

' Check for Colors in multiselect list
For Each varItem In Me.lstFavColor.ItemsSelected
varColor = varColor & "[FavColor] = """ & _
Me.lstFavColor.ItemData(varItem) & """ OR "

For Each varItem In Me.lstFavColor2.ItemsSelected
varColor = varColor & "[FavColor2] = """ & _
Me.lstFavColor2.ItemData(varItem) & """ OR "

but it's not working. need to learn more.

 

[wiklendt]

no, that's not quite the riht approach - you'd need to use a loop (edit: and i suggest a multiselect listbox with all your colours in it).

search the forum for "multiselect listboxes" - you'll get a million-and-one hits. you can even have a look at the sample databases area of the forum: there you can find tangible working examples of how things are done and workable. makes it easy to see exactly where code coes and how it's implemented.

 

[rio]

ooo.. thanks for suggestion. how about if i want have 2 different list, not 2 list of color. for example color and shape.

 

[wiklendt]

my bad. the code you already have for a multiselect:

' Check for Colors in multiselect list
For Each varItem In Me.lstFavColor.ItemsSelected
varColor = varColor & "[FavColor] = """ & _
Me.lstFavColor.ItemData(varItem) & """ OR "

should work for a multiselect and is effectively a loop.

to add another multiselect of, say, shape just add another 'check' and change it to the appropriate field names for your shape, and make sure you delcare all the same things for this shape as you had for the other checks:

Dim varShape As String

varShape = Null

' Check for SHAPES in multiselect list
For Each varItem In Me.lstShape.ItemsSelected
varShape = varShape & "[ShapeID] = """ & _
Me.lstShapes.ItemData(varItem) & """ OR "

and then add the correct strip of the last "OR" (copied and pasted and altered from the colour version):

' Test to see if we have subfilter for colors...
If IsNull(varShape) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varShape, 4) = " OR " Then
varShape = Left(varShape, Len(varShape) - 4)
End If

and.... not sure about this bit, but it may be necessary (i don't have your database to test - this is all just guess-code):

change from:

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

to:

'you will need to add a check to see if either varColor or varShape is null, and then add them to the filter

Dim varSubFilter As String

varSubFilter = Null

If IsNull (varColor) Then '
If Not IsNull (varShape) Then ' only varShape selected
varSubFilter = varShape
End If
Else
If Not IsNull (varColor) Then '
If IsNull (varShape) Then 'only varColor selected
varSubFilter = varColor
Else 'both selected
varSubFilter = varColor & " AND " & varShape
End If
End If

' Add some parentheses around the subfilters
varWhere = varWhere & "( " & varSubFilter & " )"
End If 'hmmm... i missed and If somewhere...

phew... i'm not going to be surprised if this doesn't work, but like i said, i don't have your database to see how this is all setup/working...

 

[wiklendt]

it's really rather complex (IMHO) to add something else to the already made filter... i know i've seen this code somewhere before... did you get this off one of the sample databases here on the forum? can you point us to it?

 

[rio]

Thanks. You are very kind. I'm very appreciate with your help. I will try this code first. any happen i will let u know.
Thanks again.

 

[rio]

OK.. i already used your code. But I'm not sure whether I put it correctly.
here i attach the db that i create. DB.mdb is example with 1 list only (color) and DB2.mdb is example with 2 list (color and shape). there is no problem with DB.mdb. only DB2.mdb got error. hopefully u can help me.
still need to learn new knowledge.

 

[wiklendt]

ah yes, i was worried last night that i put you on the wrong path... i'm still learning myself

we'll sort it out - don't worry.

you DB will make things easier b/c we can test them properly

 

[rio]

sure. thanks . hopefully we can learn successfully.

 

[wiklendt]

got it

please see attachment. i have also copied the code here (note that there are 3 subroutines and a function in this code):

Option Compare Database

Private Sub btnClear_Click()
        
    ' Clear all search items
    
    ' De-select each item in Color List (multiselect list)
    For intIndex = 0 To Me.lstFavColor.ListCount - 1
        Me.lstFavColor.Selected(intIndex) = False
    Next

    ' De-select each item in Shape List (multiselect list)
    For intIndex = 0 To Me.lstShape.ListCount - 1
        Me.lstShape.Selected(intIndex) = False
    Next
    
    btnSearch_Click

End Sub

Private Sub btnSearch_Click()
   ' Update the record source
    Me.SubProductFrm.Form.RecordSource = "SELECT * FROM Product " & BuildFilter
    
    ' Requery the subform
    Me.SubProductFrm.Requery
End Sub

Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub

Private Function BuildFilter() As Variant

    Dim varWhere As Variant
    Dim varColor As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    
    varWhere = Null  ' Main filter
    varColor = Null  ' Subfilter used for colors
    varShape = Null  ' Subfilter used for shapes
    
    ' Check for COLORS in multiselect list
    ' --------------------------------------------------
    For Each varItem In Me.lstFavColor.ItemsSelected
        varColor = varColor & "[Color] = """ & _
        Me.lstFavColor.ItemData(varItem) & """ OR "
    Next
    
    ' Check for SHAPES in multiselect list
    ' --------------------------------------------------
    For Each varItem In Me.lstShape.ItemsSelected
        varShape = varShape & "[Shape] = """ & _
        Me.lstShape.ItemData(varItem) & """ OR "
    Next
    
    ' Test to see if we have subfilter for COLORS...
    ' --------------------------------------------------
    If IsNull(varColor) Then
        ' do nothing
        Else
        ' strip off last "OR" in the filter
        If Right(varColor, 4) = " OR " Then
            varColor = Left(varColor, Len(varColor) - 4)
            varColor = "(" & varColor & ")"
        End If
    End If

    ' Test to see if we have subfilter for SHAPES...
    ' --------------------------------------------------
    If IsNull(varShape) Then
        ' do nothing
        Else
        ' strip off last "OR" in the filter
        If Right(varShape, 4) = " OR " Then
            varShape = Left(varShape, Len(varShape) - 4)
            varShape = "(" & varShape & ")"
        End If
    End If

    ' Put COLOR and SHAPE together in the string
    ' --------------------------------------------------
    If IsNull(varColor) Then
        If IsNull(varShape) Then ' neither Shape nor Color chosen
            varWhere = ""
            Else ' no colour, but SHAPE was selected
            varWhere = "WHERE " & varShape
        End If
        Else 'ok, so colour IS selected
        If IsNull(varShape) Then ' but Shape is not
            varWhere = "WHERE " & varColor
            Else ' both colour AND shape are selected
            varWhere = "Where " & varColor & " AND " & varShape
        End If
    End If

    ' --------------------------------------------------
    BuildFilter = varWhere
    ' --------------------------------------------------

End Function

 

[rio]

Ooh... great. thanks 4 your help.
Can i become genius like u.
thanks again.

 

[wiklendt]

not a genius - as is made evidence from my first attempt!!

(ur welcome)

 

[rio]

is there any way that i can learn VBA easily. actually i don know about VBA. i just take someone else code and then used it (copy and paste).

 

[wiklendt]

is there any way that i can learn VBA easily. actually i don know about VBA. i just take someone else code and then used it (copy and paste).

i started learning by copying and pasting too. eventually things start to connect a little more, and now, only just over ONE year after my tender and shy beginnings (stop laughing, everyone else!) i can do what you just saw me do.

the best way to learn VBA is to use, use, use, use!! and keep using it! any time i see something i like, i flip over to the VBA and see if i can understand any of the VBA - over time, you become familiar with certain ways of writing the code, and you learn more about what access has inherent, what bugs there are, what NOT to do (this was the hardest part for me!) etc.

keep at it and in no time at all you'll be a 'genius' like me

 

[rio]

hmmm... everyday i looking for new knowledge in the internet about ms access. when i found something new... i will try to play around with it.. easily if i found with db example. i think... the big problem is my English. sometime i take a long time to understand the meaning of some word.