Hi All...Very simply, I want the user to choose a value from the first combo box, which will limit the choices for a second combo box. Simple, right? I thought so, but i am having difflculty...and I am reaching out for help while i still have a few hairs left on my head!

In this instance, I am working with real estate and property uses. On my form, the first combo box is called cboUseType (industrial, residential, commercial), which will limit the choices for the second combo box called cboCurrentUse (I.E. – if I choose UseType “residential”, then the choices for the second box (CurrentUse) will be limited to “apartment, mobile home, assisted living, 2-family, single family” OR if I choose use type “industrial”, then the choices for the second box will be “warehouse, truck terminal, manufacturing etc”.

I have looked at many examples, and THINK I am doing everything right…but I can’t get my second combo box to work correctly. After I choose a UseType, a dialog box pops up asking me to “Enter Parameter Value”. (i.e. if I choose “Industrial” the dialog box will pop up and I have to enter “Industrial” again in the dialog box and click “OK” so that the second box will display the correct values).

Because property type and use categories will not change over time, so I am using one table called “USES” that both combo boxes get their values. This table has three fields 1) an ID (auto number and primary key) 2) UseType and 3) PropertyUse

cboUseType has the following code:

Private Sub cboUseType_AfterUpdate()

Me.cboCurrentUse.RowSource = "SELECT PropertyUse FROM" & _
" USES WHERE UseType = " & Me.cboUseType & _
" ORDER BY PropertyUse "

Me.cboCurrentUse = Me.cboCurrentUse.ItemData(0)

End Sub


Where and how did I go wrong???

Is UseType a numeric field or a text field? If it's text, you need quotes around the value:



Note that that assumes that there will be no apostrophes in UseType. If there might be, you'd be better off using



Make sure, too, that cboUseType is bound to the correct field in its RowSource.

Hi

See the example attached

 Cascade.zip ( 24.93K ) Number of downloads: 6

Ahh Ha! Yes, that works Doug...both are text fields and I forgot the quotes!! You are a genius! Thank you x1000.

This created another (hopefully small) issue: I use "Filter by Form" to search my records. When I use Filter by Form now, the second box (cboCurrentUse) will only display the uses that correspond to the UseType of the LAST entry made. I want the user to have ALL of the options for CurrentUse available when using “Filter by Form”, AND have the same cascading filter process available during the search. I don’t know how to do this???

To be honest, I've never used FilterByForm, so I don't have any suggestions.

Hopefully someone else will suggest something for you.

Good luck with your project!

Here is an example of what it sounds like what you are trying to do.

Hi RAZMaddaz- when i clicked "FilterByForm", i could not search on either the Location1 field or the Equipment1 field - can you?

It works fine for Me. Here is another version in Access2007. See if this works, if not, go into Visual Basic and under Tools - References, what boxes do you have checked?

RAZMaddaz

Ok…now I see how to search your form. Your have location1 as a combobox to choose the location, but below you have a textbox that can be searched using filter by form. While this does work, it doesn’t have two features I am trying to accomplish:
1) In the filter by form I want the user to be able to filter out property type and use (in other words, if the user knows he is searching for an industrial property, he can select “Industrial” in the property TYPE field, but then to refine the search further, he can click on the USE field and the dropdown will show only industrial uses).
2) I don’t want to have a combo box AND a text box, I just want one combo box for each. The reason for this is because I use this process MANY times on my form and I don’t want it to be confusing, cumbersome and space consuming.
I wonder if I should use another method of searching instead of FilterByForm…but I don’t know any other method of searching. I have used access for 15 years and have always used the FilterByForm to search. Is there a better method that will allow me to have this functionality?

Hi Doug-

If you don't use the Filter By Form button to search the database, how do you search?

The Form I uploaded has TWO Combo Boxes. When you select something from the First Combo Box (Loacation1), the Form will be Filtered and ONLY the records selected from the Location1 will show below. ALSO, depending on what was selected from the the Location1 Combo Box will determine what is shown in the Equipment1 Combo Box and after you select something from the Equipment1 location combo box, the Form will be filtered again based on the Location1 AND Equipment1 Combo Boxes. Then when you click on the View Alpha Report, the Report will use what is selected from both Combo Boxes and show you the results.

Is this what you wanted?

I build my own way to filter: unbound controls and a button, and then set the Filter of the form.

Similary to what Allen Browne has at Search criteria