Hellllp: How do I filter a multivalued Listbox field - VBA (1 Viewer)

The main form has textbox & a Listbox with which I filtered a datasheet inside a sub form. Everything worked fine only the listbox [Discipline] is not working !! it cuase Run-time error: 3831“ The multi-valued field "[Category]" cannot be used in a WHERE or HAVING clause.

So how do I filter a multivalued Listbox field [Discipline] ?


Private Sub cmdSearch_Click()
'On erorr GoTo errr
Me.tblFLM_subform1.Form.RecordSource = "SELECT * FROM tblFLM " & BuildFilter
Me.tblFLM_subform1.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"

Const conJetDate = "\#mm\/dd\/yyyy\#"

varWhere = Null

If Me.txtlocation > "" Then
varWhere = varWhere & "[Location] like " & tmp & Me.txtlocation & tmp & " AND "
End If

If Me.txtDateFrom > "" Then
varWhere = varWhere & "([Date] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If
If Me.txtDateTo > "" Then
varWhere = varWhere & "([Date] <= " & Format(Me.txtDateTo, conJetDate) & ") AND "
End If

If Me.txtTagNumber > "" Then
varWhere = varWhere & "[Tag Number] like " & tmp & Me.txtTagNumber & tmp & " AND "
End If

If Me.txtJSA1 > "" Then
varWhere = varWhere & "[JSA / Procedure] like " & tmp & Me.txtJSA1 & tmp & " AND "
End If

If Me.txtComments > "" Then
varWhere = varWhere & "[Comments] like " & tmp & Me.txtComments & tmp & " AND "
End If

If Me.txtCreatedby > "" Then
varWhere = varWhere & "[Created by] like " & tmp & Me.txtCreatedby & tmp & " AND "
End If

If Me.cboDiscipline > "" Then
varWhere = varWhere & "[Discipline] like " & tmp & Me.cboDiscipline & tmp & " AND "
End If


If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function

------------------------------------------------------------------

also for a 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] Like ""*" & Me.cboDiscipline & "*"") AND "
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

Get rid of the multi-value field and go with the proper relational structure of a separate many-side table that you have control of. You will have to replace the multi-value list box with a subform.

Pat Hartman said:

Get rid of the multi-value field and go with the proper relational structure of a separate many-side table that you have control of. You will have to replace the multi-value list box with a subform.

Click to expand...

I didn't get u

I found a code which is work good with subform but it dosen't work with open report vba :banghead:

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

hellllllllllllllllllllllllllllllllllllllllllp