Combo box with 2 columns to search

[allie357]

I am trying to build a search form (It is an unbound form)
I have a form with 8 combo boxes total that are similar in design and three text boxes. I wanted the functionality where the user could enter part of a name and still get a result.

For example, each combo box combo box that has 2 columns, one of them 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? (In the table/query I am filtering it just stores the ID, but I have the ID and Policy Name in the combo for the user to choose easier)

This is my code so far:
If Me![cboPolicy] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Policy_ID Like """ & Policy & "*"""


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. If this is not possible, I still need it to search on the column with the ID and ignore the rest to return query results.

I would also like to know how to hide the subform until the results are given?

 

[VicRauch]

To use a combo box selection in a query (or filter) you first have to have the combo box bound to the ID field as it is the ID field that is also stored in the table you are filtering. I assume you have set the combo box to be 2 columns, and in the Column Width property, you have entered a single zero (0). These will hide the first column of the two columns you define. So, with the ID column the first column, the BoundColumn property set to 1, and the second column being the text you want displayed for easier picking of the correct item to filter on, you are ready to go to the filter (or query).

In your code

sCriteria = sCriteria & " AND qrySearchCriteriaSub.Policy_ID Like """ & Policy & "*"""

you need to change the "& Policy &" to "& cboPolicy &" because when you refer to the combo box you get the column within the combo box that the combo box is bound to. You do not need the "Like" or the asteric "*" after the ID number you get from the combo box. You will be looking for only one ID field.

In the text boxes where the user can type only a part of a name, then you will need to use the syntax of: Like 'EnteredDate' & '*' to have the filter or query select any record that matches the partial name the user inputs.

And finally, the way to hide a subform is to set the subform control as hidden. The code would look like (assuming you are doing this from the parent form: Me.NameOfSubFormControl.Visible = False

Be sure to change the "NameOfSubFormControl" to the name you have given it (or was assigned by Access if you did a Drag-and-Drop)

HTH,

 

[allie357]

Search form

Thanks for all your help, Vic. I will give it a shot.