Combo Boxes as a filter

[obheron]

Hi all,
I have a form with a table as a source. On the form I have 2 combo boxes. Is it possible for me to restrict what shows up as options in one combo box based on the choice in the first combo box? I.e. use one to filter the other.

Thanks for the help.

 

[PHV]

Dynamically set the RowSource property of the second combo in the AfterUpdate event procedure of the first.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CharlieT302]

Yes,

*Right-click the combo box you wish to syncronize (the one whose values are determined by the other box)

*Choose Properties
*On the "Data" tab, place the cursor in the Row Source property and click the build (three elipses) button.
*Make sure to include the field that the two boxes share in common.
*In the Criteria row of the common field type:
[forms]![NameofFormContaining combo boxes]![SourceComboBox]
Close the query & save changes when prompted.
*Right-click the source Combo box and choose Properties
*On the Event tab, click the "AfterUpdate" event then click the build button. Choose Code and o.k.
* type the following:
Me.ComboBox.Requery (ComboBox is the one being filtered)

You should also apply the same code to the On Current event for the form.

Hope this helps.

 

[CharlieT302]

If the code I gave you towards the end of the steps provided do not work, try:

DoCmd.Requery "ComboBox"

 

[obheron]

*Make sure to include the field that the two boxes share in common.

The two boxes do not share a field. Combo1 has a source from a different table. The values in that table match are a lookup column in the table that sources Combo2.

You should also apply the same code to the On Current event for the form.
Not sure what you mean here.

I tried everything you mentioned, and have not been successful yet.

 

[Eupher]

Have a look at the following FAQ's:

faq702-681
faq702-4289
faq702-4640

HTH,

Ken S.

 

[obheron]

I tried Charlie's method one more time and for somereason it worked. Must of had a typo somewhere. Thanks Charlie!

 

[CharlieT302]

In order to filter one box based on the value of the other, the two must share a common field. In essence, you are creating a relationship between the two combo boxes much as you would in a query. Otherwise, what are you filtering on?

In the "RowSource", the fields will display in a manner similar to that of a query. You can pull down any additional fields you wish, such as the common field.

On Current:

When a form is placed in Design view, double-click the gray box that intersects the two rulers (upper left corner). This displays the Properties tab. On the Event tab, click the "On Current" event.

As a side note, the Event tab displays the events that are appropriate for the "selected" object...whether the object is a drop-down box, field, or the form itself.

 

[obheron]

I filtered a column related to combo2 based on the value selected in combo1. Note: combo 1 and combo 2 had to different sources and it still worked.

 

[CharlieT302]

Glad to hear it. I didn't say that the data sources needed to be the same, only that they needed to share a commonality; as you would see in a one to many table relationship.

Glad its working for you.

 

[obheron]

Ok, got the combo boxes working, now further down after the updates, I need some text boxes to update based on the choice of the last combo box. Unfortunately I don't see how I can do the same procedure above (no RowSource in the text box properties). Any suggestions?

thanks again!

 

[PHV]

no RowSource in the text box properties
Play with the Value property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[obheron]

Play with the Value property.

Didn't see a Value listed in the properties window.
I found a way around it by making the fields list boxes as opposed to text boxes. Only problem is no wrap around text on the list box .

It's like cutting your distance in half to a goal, no matter how many times you cut the distance you will never reach the end.

Thanks for the help!