Hello guys I really need your help.
I hope i am anywhere near an advanced access user. Any code i have in my application is well understood but i have stumbled on a problem that i really need to solve to provide proper reporting.
To the problem
I have a form with 2 combo boxes
One is for selecting Status (active spare inactive etc)
One is for selecting name (there are duplicates)
After the user makes his selections he presses a button and a subform is filtered according to the criteria he chose. If he leaves sth blank then the function gives ALL records for this field.
This function works great but the problem is that i need the user to be able to select multiple items and not just one. So I put a multi select list box for STATUS. unfortunately i cannot get the function to work.
This below is the function that works properly for the 2 combo box selections.
-----------------------------------------
Function searchCriteria()
End Function
Dim strStatus, strnam As String
Dim task, strCriteria As String
If IsNull(Me.cboCat) Then
strnam = "[FullName] like '*'"
Else
strnam = "[FullName] = '" & Me.cbonam & "'"
End If
If IsNull(Me.cboStatus) Then
strStatus = "[Status] like '*'"
Else
strStatus = "[Status] = '" & Me.cboStatus & "'"
End IfstrCriteria = strStatus & "AND" & strnam
task = "Select * from qry07vehiclesrpt where " & strCriteria
Me.qry07vehiclesrpt1.Form.RecordSource = task
Me.qry07vehiclesrpt1.Form.Requery
End Function
-------------------------
I have searched through the internet for how to use the list box end end up in the below alteration of my function but with no luck
-----------------------------
Function searchCriteria()
Dim strStatus, strnam As String
Dim task, strCriteria As String
Dim VarString As Variant
If IsNull(Me.cbonam) Then
strnam = "[FullName] like '*'"
Else
strnam = "[FullName] = '" & Me.cbonam & "'" (this part works fine if i dont make any selection in the listbox)
End If
For Each VarString In Me.lstStatus.ItemsSelected
strStatus = strStatus & "," & Me!lstStatus.ItemData(VarString)
Next VarString
If Len(strStatus) = 0 Then
strStatus = "[Status] like '*'"
Else
strStatus = "[Status] In (Right(strStatus, Len(strStatus)) - 1)"
(this must be really wrong)
End If
strCriteria = strStatus & "AND" & strnam
task = "Select * from qry07epassrpt where " & strCriteria
(Should i do alterations in the above lines maybe??)
Me.qry07epassrpt1.Form.RecordSource = task
Me.qry07epassrpt1.Form.Requery
End Function
-------------------------------------
I wonder if i am anywhere near or i need to change the whole philosophy.
Could anyone help to show me what i am doing wrong??
Please this is important
Thanks in advance
Nikos
I hope i am anywhere near an advanced access user. Any code i have in my application is well understood but i have stumbled on a problem that i really need to solve to provide proper reporting.
To the problem
I have a form with 2 combo boxes
One is for selecting Status (active spare inactive etc)
One is for selecting name (there are duplicates)
After the user makes his selections he presses a button and a subform is filtered according to the criteria he chose. If he leaves sth blank then the function gives ALL records for this field.
This function works great but the problem is that i need the user to be able to select multiple items and not just one. So I put a multi select list box for STATUS. unfortunately i cannot get the function to work.
This below is the function that works properly for the 2 combo box selections.
-----------------------------------------
Function searchCriteria()
End Function
Dim strStatus, strnam As String
Dim task, strCriteria As String
If IsNull(Me.cboCat) Then
strnam = "[FullName] like '*'"
Else
strnam = "[FullName] = '" & Me.cbonam & "'"
End If
If IsNull(Me.cboStatus) Then
strStatus = "[Status] like '*'"
Else
strStatus = "[Status] = '" & Me.cboStatus & "'"
End IfstrCriteria = strStatus & "AND" & strnam
task = "Select * from qry07vehiclesrpt where " & strCriteria
Me.qry07vehiclesrpt1.Form.RecordSource = task
Me.qry07vehiclesrpt1.Form.Requery
End Function
-------------------------
I have searched through the internet for how to use the list box end end up in the below alteration of my function but with no luck
-----------------------------
Function searchCriteria()
Dim strStatus, strnam As String
Dim task, strCriteria As String
Dim VarString As Variant
If IsNull(Me.cbonam) Then
strnam = "[FullName] like '*'"
Else
strnam = "[FullName] = '" & Me.cbonam & "'" (this part works fine if i dont make any selection in the listbox)
End If
For Each VarString In Me.lstStatus.ItemsSelected
strStatus = strStatus & "," & Me!lstStatus.ItemData(VarString)
Next VarString
If Len(strStatus) = 0 Then
strStatus = "[Status] like '*'"
Else
strStatus = "[Status] In (Right(strStatus, Len(strStatus)) - 1)"
(this must be really wrong)
End If
strCriteria = strStatus & "AND" & strnam
task = "Select * from qry07epassrpt where " & strCriteria
(Should i do alterations in the above lines maybe??)
Me.qry07epassrpt1.Form.RecordSource = task
Me.qry07epassrpt1.Form.Requery
End Function
-------------------------------------
I wonder if i am anywhere near or i need to change the whole philosophy.
Could anyone help to show me what i am doing wrong??
Please this is important
Thanks in advance
Nikos
