Full Version: This Looks Simple, This Looks Right…what Am I Missing?
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
appraiser
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!
On 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???
Doug Steele
Is UseType a numeric field or a text field? If it's text, you need quotes around the value:
!--c1-->
CODE
Me.cboCurrentUse.RowSource = "SELECT PropertyUse FROM" & _
  " USES WHERE UseType = '" & Me.cboUseType & "' " & _
  " ORDER BY PropertyUse "

Note that that assumes that there will be no apostrophes in UseType. If there might be, you'd be better off using
CODE
Me.cboCurrentUse.RowSource = "SELECT PropertyUse FROM" & _
  " USES WHERE UseType = '" & Replace(Me.cboUseType, "'", "''") & "' " & _
  " ORDER BY PropertyUse "

Make sure, too, that cboUseType is bound to the correct field in its RowSource.
mike60smart
Hi
See the example attached
Click to view attachment
appraiser
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???
Doug Steele
To be honest, I've never used FilterByForm, so I don't have any suggestions.
opefully someone else will suggest something for you.
Good luck with your project!
RAZMaddaz
Here is an example of what it sounds like what you are trying to do.
appraiser
Hi RAZMaddaz- when i clicked "FilterByForm", i could not search on either the Location1 field or the Equipment1 field - can you?
RAZMaddaz
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?
AZMaddaz
appraiser
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.
Owonder 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?
appraiser
Hi Doug-
If you don't use the Filter By Form button to search the database, how do you search?
RAZMaddaz
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?
Doug Steele
I build my own way to filter: unbound controls and a button, and then set the Filter of the form.
imilary to what Allen Browne has at Search criteria
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.