Using combo box to search form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am creating a search area in the Header section of my form to display
the results in the Detail section. The code I have so far is below. It
isn't really working properly, so I am hoping someone can look at it and see
why.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim strA As String
Dim strB As String
Dim lngLen As Long


If Me.cboLevel = "A" Then
strA = "A"
strWhere = strWhere & "([Level] = strA ) AND "
ElseIf Me.cboLevel = "B" Then
strB = "B"
strWhere = strWhere & "([Level] = strB) AND "
Else
strWhere = strWhere & "([Level] = FALSE) AND "
End If

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "([CompanyName] = TRUE) AND "
Else
strWhere = strWhere & "([CompanyName] = False) AND "
End If

If Not IsNull(Me.cboCountry) Then
strWhere = strWhere & "([Country] = TRUE) AND "
Else
strWhere = strWhere & "([Country] = FALSE) AND "
End If

If Not IsNull(Me.cboArch) Then
strWhere = strWhere & "([ArchFixtures] = TRUE) AND "
Else
strWhere = strWhere & "([ArchFixtures] = FALSE) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If


End Sub
 
Daniel

Have you tried building one of your WHERE strings and using that in a query?

You can open a new query in design view, switch to SQL view, and paste (or
type) in a SQL string, then run the query.

I suspect you may be having an issue with too many "AND"s...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The main function of this search form is that I want the user to click a
commond button that filters my form based on the selected criteria. The code
I started with came from Allen Browne's sample search form and I just added
to it. I'm not really familiar with SQL so I don't know how to create a
query using the code.

This code worked perfectly fine when it was just filtering on
cboCompanyName, but then I started adding cboCountry and cboLevel and it
began filtering on a seemingly unknown criteria. The main purpose is to be
able to select any number of criteria at the top of the form to search on
(company name, country, product types, etc...) and it will filter all the
companies that have the chosen criteria (but the user can enter all the
criteria, or none of the criteria)

Does this make sense?

Jeff Boyce said:
Daniel

Have you tried building one of your WHERE strings and using that in a query?

You can open a new query in design view, switch to SQL view, and paste (or
type) in a SQL string, then run the query.

I suspect you may be having an issue with too many "AND"s...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Daniel said:
Hi, I am creating a search area in the Header section of my form to
display
the results in the Detail section. The code I have so far is below. It
isn't really working properly, so I am hoping someone can look at it and
see
why.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim strA As String
Dim strB As String
Dim lngLen As Long


If Me.cboLevel = "A" Then
strA = "A"
strWhere = strWhere & "([Level] = strA ) AND "
ElseIf Me.cboLevel = "B" Then
strB = "B"
strWhere = strWhere & "([Level] = strB) AND "
Else
strWhere = strWhere & "([Level] = FALSE) AND "
End If

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "([CompanyName] = TRUE) AND "
Else
strWhere = strWhere & "([CompanyName] = False) AND "
End If

If Not IsNull(Me.cboCountry) Then
strWhere = strWhere & "([Country] = TRUE) AND "
Else
strWhere = strWhere & "([Country] = FALSE) AND "
End If

If Not IsNull(Me.cboArch) Then
strWhere = strWhere & "([ArchFixtures] = TRUE) AND "
Else
strWhere = strWhere & "([ArchFixtures] = FALSE) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If


End Sub
Click to expand...
Click to expand...
 
Daniel

These newsgroups are largely "staffed" by volunteers, so you may have to
take the pointers you get here and dig into it a bit.

Since you "started adding" additional criteria, you'll want to learn enough
about SQL syntax to know how to form a grammatically-correct SQL statement.
One way to do that is to see what Access generates when you build a query.

Create a new query, add the table you use to load the form, add the fields
you show and the fields you use for selection criteria. Add in some
selection (you are calling it "filer") criteria for these fields. Run the
query. Does it return what you expect? If not, tweak the query 'til it
does.

Now open the query in SQL view and inspect the SQL statement Access
generates. Compare this to yours.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Daniel said:
The main function of this search form is that I want the user to click a
commond button that filters my form based on the selected criteria. The
code
I started with came from Allen Browne's sample search form and I just
added
to it. I'm not really familiar with SQL so I don't know how to create a
query using the code.

This code worked perfectly fine when it was just filtering on
cboCompanyName, but then I started adding cboCountry and cboLevel and it
began filtering on a seemingly unknown criteria. The main purpose is to
be
able to select any number of criteria at the top of the form to search on
(company name, country, product types, etc...) and it will filter all the
companies that have the chosen criteria (but the user can enter all the
criteria, or none of the criteria)

Does this make sense?

Jeff Boyce said:
Daniel

Have you tried building one of your WHERE strings and using that in a
query?

You can open a new query in design view, switch to SQL view, and paste
(or
type) in a SQL string, then run the query.

I suspect you may be having an issue with too many "AND"s...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Daniel said:
Hi, I am creating a search area in the Header section of my form to
display
the results in the Detail section. The code I have so far is below.
It
isn't really working properly, so I am hoping someone can look at it
and
see
why.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim strA As String
Dim strB As String
Dim lngLen As Long


If Me.cboLevel = "A" Then
strA = "A"
strWhere = strWhere & "([Level] = strA ) AND "
ElseIf Me.cboLevel = "B" Then
strB = "B"
strWhere = strWhere & "([Level] = strB) AND "
Else
strWhere = strWhere & "([Level] = FALSE) AND "
End If

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "([CompanyName] = TRUE) AND "
Else
strWhere = strWhere & "([CompanyName] = False) AND "
End If

If Not IsNull(Me.cboCountry) Then
strWhere = strWhere & "([Country] = TRUE) AND "
Else
strWhere = strWhere & "([Country] = FALSE) AND "
End If

If Not IsNull(Me.cboArch) Then
strWhere = strWhere & "([ArchFixtures] = TRUE) AND "
Else
strWhere = strWhere & "([ArchFixtures] = FALSE) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks. Thats a pretty good idea!

Jeff Boyce said:
Daniel

These newsgroups are largely "staffed" by volunteers, so you may have to
take the pointers you get here and dig into it a bit.

Since you "started adding" additional criteria, you'll want to learn enough
about SQL syntax to know how to form a grammatically-correct SQL statement.
One way to do that is to see what Access generates when you build a query.

Create a new query, add the table you use to load the form, add the fields
you show and the fields you use for selection criteria. Add in some
selection (you are calling it "filer") criteria for these fields. Run the
query. Does it return what you expect? If not, tweak the query 'til it
does.

Now open the query in SQL view and inspect the SQL statement Access
generates. Compare this to yours.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Daniel said:
The main function of this search form is that I want the user to click a
commond button that filters my form based on the selected criteria. The
code
I started with came from Allen Browne's sample search form and I just
added
to it. I'm not really familiar with SQL so I don't know how to create a
query using the code.

This code worked perfectly fine when it was just filtering on
cboCompanyName, but then I started adding cboCountry and cboLevel and it
began filtering on a seemingly unknown criteria. The main purpose is to
be
able to select any number of criteria at the top of the form to search on
(company name, country, product types, etc...) and it will filter all the
companies that have the chosen criteria (but the user can enter all the
criteria, or none of the criteria)

Does this make sense?

Jeff Boyce said:
Daniel

Have you tried building one of your WHERE strings and using that in a
query?

You can open a new query in design view, switch to SQL view, and paste
(or
type) in a SQL string, then run the query.

I suspect you may be having an issue with too many "AND"s...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I am creating a search area in the Header section of my form to
display
the results in the Detail section. The code I have so far is below.
It
isn't really working properly, so I am hoping someone can look at it
and
see
why.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim strA As String
Dim strB As String
Dim lngLen As Long


If Me.cboLevel = "A" Then
strA = "A"
strWhere = strWhere & "([Level] = strA ) AND "
ElseIf Me.cboLevel = "B" Then
strB = "B"
strWhere = strWhere & "([Level] = strB) AND "
Else
strWhere = strWhere & "([Level] = FALSE) AND "
End If

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "([CompanyName] = TRUE) AND "
Else
strWhere = strWhere & "([CompanyName] = False) AND "
End If

If Not IsNull(Me.cboCountry) Then
strWhere = strWhere & "([Country] = TRUE) AND "
Else
strWhere = strWhere & "([Country] = FALSE) AND "
End If

If Not IsNull(Me.cboArch) Then
strWhere = strWhere & "([ArchFixtures] = TRUE) AND "
Else
strWhere = strWhere & "([ArchFixtures] = FALSE) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top