search combo boxes...2 columns?

  • Thread starter Thread starter allie357
  • Start date Start date
A

allie357

I am trying to build a search form
Yes, I thought about using the wizard for the combos, however...

I have a form with 8 combo boxes total that are similar in design. I
wanted the functionality where the user could enter part of a name and
still get a result in the search form.

In one example the combo box that has 2 columns, that shows the
policy_ID and the corresponding policy name so it easier for the user
to pick a choice. I need to be able to search the entire contents of
the combo (or just the first column) containing the Policy_ID to add it
to the filter? Is this possible?

This is my code so far:

If Me![cboPolicy] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Policy_ID Like """ &
Policy & "*"""
 
If you want to enter partial info into the combo until the desired value is
found, set the combo's Auto Expand property to Yes. This will not give you a
partial seach string as in LIKE "Something*" , but it will make it easy for
the user to find the exact policy. If you are using the value of the combo
to construct a criteria string, the combo is not going to give you a LIKE
comparison, because it returns the enter value of the item in the list, not
just what you have typed in.

If you are using 8 combos for your search, I would have a command button (In
fact I do a similar thing with 8 list boxes, but same logic), that initiates
the search. The next question is are the combos cascading? That is, does
combo2 filter on what is in combo1 or are they all stand alone?

But back to the point. In the Click event of the Search button, you need to
construct your criteria string something like:

Dim strCriteria As String

If Not IsNull(Me.Combo1) Then
strCriteria = "[FieldToMatch1] = '" & Me.Combo1 &"'"
End If

If Not IsNull(Me.Combo2) Then
strCriteria = AddAnd(strCriteria) & strCriteria _
& "[FieldToMatch2] = '" & Me.Combo2 &"'"
End If
.....................

If Not IsNull(Me.Combo8) Then
strCriteria = AddAnd(strCriteria) & strCriteria _
& "[FieldToMatch8] = '" & Me.Combo8 &"'"
End If
.....................

Private Function AddAnd(strCriteria As String) As String
If Len(strCriteria) > 0 Then
AddAnd = strCriteria & " And "
Else
AddAnd = strCriteria
End If
End Function
 
Thanks for replying. No, the combos are not cascading. My question
is: will the suggested code below just look at the first column of the
combo? My Policy Combo, for instance, Includes Policy_ID and Policy
Name, but it the query (based on a table) I am searching it only
includes the policy ID as a foreign key. So, I need it to look at the
Policy ID and the combo box value and match on the ID. Does that make
sense? Also icluded in this search are 4 text boxes as well (2 for
start date, end date and 1 for a $ amount and one for a description. I
need it to search any part of the description as well.

If you want to enter partial info into the combo until the desired value is
found, set the combo's Auto Expand property to Yes. This will not give you a
partial seach string as in LIKE "Something*" , but it will make it easy for
the user to find the exact policy. If you are using the value of the combo
to construct a criteria string, the combo is not going to give you a LIKE
comparison, because it returns the enter value of the item in the list, not
just what you have typed in.

If you are using 8 combos for your search, I would have a command button (In
fact I do a similar thing with 8 list boxes, but same logic), that initiates
the search. The next question is are the combos cascading? That is, does
combo2 filter on what is in combo1 or are they all stand alone?

But back to the point. In the Click event of the Search button, you need to
construct your criteria string something like:

Dim strCriteria As String

If Not IsNull(Me.Combo1) Then
strCriteria = "[FieldToMatch1] = '" & Me.Combo1 &"'"
End If

If Not IsNull(Me.Combo2) Then
strCriteria = AddAnd(strCriteria) & strCriteria _
& "[FieldToMatch2] = '" & Me.Combo2 &"'"
End If
....................

If Not IsNull(Me.Combo8) Then
strCriteria = AddAnd(strCriteria) & strCriteria _
& "[FieldToMatch8] = '" & Me.Combo8 &"'"
End If
....................

Private Function AddAnd(strCriteria As String) As String
If Len(strCriteria) > 0 Then
AddAnd = strCriteria & " And "
Else
AddAnd = strCriteria
End If
End Function

allie357 said:
I am trying to build a search form
Yes, I thought about using the wizard for the combos, however...

I have a form with 8 combo boxes total that are similar in design. I
wanted the functionality where the user could enter part of a name and
still get a result in the search form.

In one example the combo box that has 2 columns, that shows the
policy_ID and the corresponding policy name so it easier for the user
to pick a choice. I need to be able to search the entire contents of
the combo (or just the first column) containing the Policy_ID to add it
to the filter? Is this possible?

This is my code so far:

If Me![cboPolicy] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Policy_ID Like """ &
Policy & "*"""
Click to expand...
Click to expand...
 
The code will only include the bound column. If you want to use a different
column, you need to reference that column. In a 2 column combo, the first
column is 0 and the second is 1. So to get the Policy Name it would be
Me.Combo1.Column(1). But, if you have the policy id, why do you need to
search by the name?

The code I posted can include any control that contains data. It does not
have to be a combo. As to searching any part of a description field, instead
of:
"[FieldToSearch] = '" Me.SomeControl & "'"

it would be
"[FieldToSearch] Like('*" Me.SomeControl & "*')"


allie357 said:
Thanks for replying. No, the combos are not cascading. My question
is: will the suggested code below just look at the first column of the
combo? My Policy Combo, for instance, Includes Policy_ID and Policy
Name, but it the query (based on a table) I am searching it only
includes the policy ID as a foreign key. So, I need it to look at the
Policy ID and the combo box value and match on the ID. Does that make
sense? Also icluded in this search are 4 text boxes as well (2 for
start date, end date and 1 for a $ amount and one for a description. I
need it to search any part of the description as well.

If you want to enter partial info into the combo until the desired value is
found, set the combo's Auto Expand property to Yes. This will not give you a
partial seach string as in LIKE "Something*" , but it will make it easy for
the user to find the exact policy. If you are using the value of the combo
to construct a criteria string, the combo is not going to give you a LIKE
comparison, because it returns the enter value of the item in the list, not
just what you have typed in.

If you are using 8 combos for your search, I would have a command button (In
fact I do a similar thing with 8 list boxes, but same logic), that initiates
the search. The next question is are the combos cascading? That is, does
combo2 filter on what is in combo1 or are they all stand alone?

But back to the point. In the Click event of the Search button, you need to
construct your criteria string something like:

Dim strCriteria As String

If Not IsNull(Me.Combo1) Then
strCriteria = "[FieldToMatch1] = '" & Me.Combo1 &"'"
End If

If Not IsNull(Me.Combo2) Then
strCriteria = AddAnd(strCriteria) & strCriteria _
& "[FieldToMatch2] = '" & Me.Combo2 &"'"
End If
....................

If Not IsNull(Me.Combo8) Then
strCriteria = AddAnd(strCriteria) & strCriteria _
& "[FieldToMatch8] = '" & Me.Combo8 &"'"
End If
....................

Private Function AddAnd(strCriteria As String) As String
If Len(strCriteria) > 0 Then
AddAnd = strCriteria & " And "
Else
AddAnd = strCriteria
End If
End Function

allie357 said:
I am trying to build a search form
Yes, I thought about using the wizard for the combos, however...

I have a form with 8 combo boxes total that are similar in design. I
wanted the functionality where the user could enter part of a name and
still get a result in the search form.

In one example the combo box that has 2 columns, that shows the
policy_ID and the corresponding policy name so it easier for the user
to pick a choice. I need to be able to search the entire contents of
the combo (or just the first column) containing the Policy_ID to add it
to the filter? Is this possible?

This is my code so far:

If Me![cboPolicy] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Policy_ID Like """ &
Policy & "*"""
Click to expand...
Click to expand...
Click to expand...
 
Back
Top