searching using combo boxes

[Guest]

Hello, There may be a very simple solution to this problem I have but I can't
work it out. I have a form that contains 5 combo boxes. 1 for each of the
following: site, dept, division, stream and status. I need to search a table
using what the user selects from these combo boxes. It works if they choose
something from each combo box but not if they leave one or more blank. How
do I get around this?

Thanks in advance
rhys

 

[Allen Browne]

Download the sample database at:
http://allenbrowne.com/unlinked/Search2000.zip

It demonstrates how to put a series of unbound controls across the top of
the form, and filter the form based on whichever ones the user puts values
in. Requires Access 2000 or later.

 

[Guest]

Thanks for that Allen. It was very helpful. However when I tried to use it
in my code it only works if nothing is selected at all( the msgbox does
show). I can't get the results based on the combo boxes. Perhaps it's
something i have done in my code below.

Private Sub report_click()
On Error GoTo Err_report_Click
Dim stDocName As String
Dim strwhere As String
Dim lngLen As Long
stDocName = "CG KEY SKILLS"

If Not IsNull(Me.Site) Then
strwhere = strwhere & "([Site] = """ & Me.Site & """) AND "
End If

If Not IsNull(Me.dept) Then
strwhere = strwhere & "([Department] = """ & Me.dept & """) AND "
End If

If Not IsNull(Me.fstream) Then
strwhere = strwhere & "([Funding Stream] = """ & Me.fstream & """)
AND "
End If

If Not IsNull(Me.status) Then
strwhere = strwhere & "([Status] = """ & Me.status & """) AND "
End If

lngLen = Len(strwhere) - 3
If lngLen <= 0 Then
msgbox "No criteria", vbInformation, "Nothing to do."
Else
strwhere = Left$(strwhere, lngLen)
'Debug.Print strWhere

DoCmd.OpenForm stDocName, , , strwhere
End If
Exit_report_Click:
Exit Sub
Err_report_Click:
msgbox Err.Description
Resume Exit_report_Click
End Sub

thanks rhys

 

[Allen Browne]

1. Check yout names.
What is Site?
Is it the unbound combo?
Is there another control with that name on the form?
Is there a field by that name in the form's RecordSource?
You need to use the name of the unbound search combo, not the name of the
output field.

2. Check your data types.
Look at the Bound Column property.
Then look at the RowSource property.
Whatever table is named in the RowSource, open it in design view.
What is the data type of the bound column (e.g. the first column if Bound
Column is 1)? The code explains how to use different delimiters for
different data types.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for that Allen. It was very helpful. However when I tried to use
it
in my code it only works if nothing is selected at all( the msgbox does
show). I can't get the results based on the combo boxes. Perhaps it's
something i have done in my code below.

Private Sub report_click()
On Error GoTo Err_report_Click
Dim stDocName As String
Dim strwhere As String
Dim lngLen As Long
stDocName = "CG KEY SKILLS"

If Not IsNull(Me.Site) Then
strwhere = strwhere & "([Site] = """ & Me.Site & """) AND "
End If

If Not IsNull(Me.dept) Then
strwhere = strwhere & "([Department] = """ & Me.dept & """) AND "
End If

If Not IsNull(Me.fstream) Then
strwhere = strwhere & "([Funding Stream] = """ & Me.fstream & """)
AND "
End If

If Not IsNull(Me.status) Then
strwhere = strwhere & "([Status] = """ & Me.status & """) AND "
End If

lngLen = Len(strwhere) - 3
If lngLen <= 0 Then
msgbox "No criteria", vbInformation, "Nothing to do."
Else
strwhere = Left$(strwhere, lngLen)
'Debug.Print strWhere

DoCmd.OpenForm stDocName, , , strwhere
End If
Exit_report_Click:
Exit Sub
Err_report_Click:
msgbox Err.Description
Resume Exit_report_Click
End Sub

thanks rhys

Download the sample database at:
http://allenbrowne.com/unlinked/Search2000.zip

It demonstrates how to put a series of unbound controls across the top of
the form, and filter the form based on whichever ones the user puts
values
in. Requires Access 2000 or later.

 

[Guest]

i've sorted it now allen. i had put
lngLen = Len(strwhere) - 3
instead of
lngLen = Len(strwhere) - 4

thans for your help

rhys

1. Check yout names.
What is Site?
Is it the unbound combo?
Is there another control with that name on the form?
Is there a field by that name in the form's RecordSource?
You need to use the name of the unbound search combo, not the name of the
output field.

2. Check your data types.
Look at the Bound Column property.
Then look at the RowSource property.
Whatever table is named in the RowSource, open it in design view.
What is the data type of the bound column (e.g. the first column if Bound
Column is 1)? The code explains how to use different delimiters for
different data types.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for that Allen. It was very helpful. However when I tried to use
it
in my code it only works if nothing is selected at all( the msgbox does
show). I can't get the results based on the combo boxes. Perhaps it's
something i have done in my code below.

Private Sub report_click()
On Error GoTo Err_report_Click
Dim stDocName As String
Dim strwhere As String
Dim lngLen As Long
stDocName = "CG KEY SKILLS"

If Not IsNull(Me.Site) Then
strwhere = strwhere & "([Site] = """ & Me.Site & """) AND "
End If

If Not IsNull(Me.dept) Then
strwhere = strwhere & "([Department] = """ & Me.dept & """) AND "
End If

If Not IsNull(Me.fstream) Then
strwhere = strwhere & "([Funding Stream] = """ & Me.fstream & """)
AND "
End If

If Not IsNull(Me.status) Then
strwhere = strwhere & "([Status] = """ & Me.status & """) AND "
End If

lngLen = Len(strwhere) - 3
If lngLen <= 0 Then
msgbox "No criteria", vbInformation, "Nothing to do."
Else
strwhere = Left$(strwhere, lngLen)
'Debug.Print strWhere

DoCmd.OpenForm stDocName, , , strwhere
End If
Exit_report_Click:
Exit Sub
Err_report_Click:
msgbox Err.Description
Resume Exit_report_Click
End Sub

thanks rhys

Download the sample database at:
http://allenbrowne.com/unlinked/Search2000.zip

It demonstrates how to put a series of unbound controls across the top of
the form, and filter the form based on whichever ones the user puts
values
in. Requires Access 2000 or later.

Hello, There may be a very simple solution to this problem I have but I
can't
work it out. I have a form that contains 5 combo boxes. 1 for each of
the
following: site, dept, division, stream and status. I need to search a
table
using what the user selects from these combo boxes. It works if they
choose
something from each combo box but not if they leave one or more blank.
How
do I get around this?