Help: Filtering report by multivalued listbox (vba code issue)

[alhabkk]

I found a code which is work good with subform table for searching multivalued Listbox but it doesn’t work with open report vba code
this is the code is used for filtering the subform and i need it for openreport command

If Me.cboDiscipline > "" Then
varWhere = varWhere & "[Discipline].value = '" & Me.cboDiscipline & "'"
End If





this the code (note: its for open filtered report)

Private Sub Toggle3_Click()
Dim strReport As String
Dim strDateField As String
Dim lngLen As Long
Dim strWhere As String
Dim lngView As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"


strReport = "rptFLM"
strDateField = "[Date]"
lngView = acViewReport


If Not Me.txtlocation = "" Then
strWhere = strWhere & "([Location] Like ""*" & Me.txtlocation & "*"") AND "
End If

If Not Me.txtDateFrom = "" Then
strWhere = strWhere & "([Date] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If

If Not Me.txtDateTo = "" Then
strWhere = strWhere & "([Date] < " & Format(Me.txtDateTo + 1, conJetDate) & ") AND "
End If

If Not Me.txtTagNumber = "" Then
strWhere = strWhere & "([Tag Number] Like ""*" & Me.txtTagNumber & "*"") AND "
End If


If Not Me.txtJSA1 = "" Then
strWhere = strWhere & "([JSA / Procedure] Like ""*" & Me.txtJSA1 & "*"") AND "
End If

If Not Me.txtCreatedby = "" Then
strWhere = strWhere & "([Created by] Like ""*" & Me.txtCreatedby & "*"") AND "
End If

If Not Me.cboDiscipline = "" Then
strWhere = strWhere & "[Discipline].value = '" & Me.cboDiscipline & "'"
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub


End Sub

 

[pbaldy]

I know you've repeatedly posted this. If you've changed from a multivalue field to a multiselect listbox, you need to loop it:

http://www.baldyweb.com/multiselect.htm

If you're still using a multivalue field, you'll find not many of us use them. I don't/won't.

 

[alhabkk]

I know you've repeatedly posted this. If you've changed from a multivalue field to a multiselect listbox, you need to loop it:

http://www.baldyweb.com/multiselect.htm

If you're still using a multivalue field, you'll find not many of us use them. I don't/won't.

i don't need to use a multiselect listbox because in my search page the [Discipline] field is a combo box so i only need to select one from the list and its enough for me

 

[pbaldy]

Then why is it a:

multivalued Listbox

I'd also point out that you didn't include the " AND " at the end for that one.

 

[alhabkk]

Then why is it a:



I'd also point out that you didn't include the " AND " at the end for that one.

I mean in table its multivalued listbox

I include "AND" but the problem remain :banghead:

 

[pbaldy]

Can you post the db here?

 

[jdraw]

Perhaps you should describe WHAT you are trying to accomplish without showing code.
Since in the code you showed in post #1

If Me.cboDiscipline > "" Then
varWhere = varWhere & "[Discipline].value = '" & Me.cboDiscipline & "'"
End

I'm sure the author of the code used the prefix cbo to name a combobox not a listbox.

After reading your posts in this thread, I still do not understand what you are trying to do. Perhaps it's just me...

 

[alhabkk]

Can you post the db here?

find attech..

 

[pbaldy]

Like I said, you've used a multivalue field, which is why you get the error. I haven't used one, not going to use one, don't care to learn how to use one. Looks like jdraw is taking over anyway, so I'll get out of the way.

 

[alhabkk]

Like I said, you've used a multivalue field, which is why you get the error. I haven't used one, not going to use one, don't care to learn how to use one. Looks like jdraw is taking over anyway, so I'll get out of the way.

Thank u ,,

 

[jdraw]

No Paul, I couldn't understand the post and after seeing your comments thought I'd ask the OP to restate/state the issue. Just trying to assist readers.

 

[alhabkk]

No Paul, I couldn't understand the post and after seeing your comments thought I'd ask the OP to restate/state the issue. Just trying to assist readers.

jdraw, download the db above it will give u clear picture of my problem.

 

[jdraw]

I have downloaded and looked at your database.

As Paul already mentioned, Discipline is a multivalued field (can be seen in attached jpg) and may be causing the error. I also do not use multivalued fields -too many negatives from users. I prefer to use a standard look-up table.

Also I notice that you do not have any relationships set up in your data base. That's where I start.

It may be clear to you, but I haven't been privy to your development, testing, problem definition etc, so it isn't clear to me.

We encourage posters with a problem/issue to provide sufficient information to help the reader understand your business, specifics of the problem/opportunity; a summary of what you have tried and the results - especially error messages, and some detail as to how to repeat the issue/problem.

I did see your Filter for Location. You might want to review Martin Green's site where he uses a Form with Options to set up Opening a Report (filtered).
see http://www.fontstuff.com/Access/acctut19.htm