Combo box with 2 columns to search

allie357

Board Regular
Joined
Jun 28, 2006
Messages
50
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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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
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,
 

Forum statistics

Threads
1,137,205
Messages
5,680,184
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top