UtterAccess Discussion Forums > Cascading Drop Downs

eterney

May 4 2012, 06:45 AM

Hello,
I'm trying to get some cascading drop downs to work.

I have a table (tblRequirementsICS) with the following fields

txtCitation (primary key)
txtTitle
txtPriorityGroup
txtIssue
memEEIDescription

I have a sub-form to select through the table options to arrive at the correct citation number.

So, I start with a drop down of the three Titles, called cboRequirementName

Under the AfterUpdate, I've put the following code:

CODE

Private Sub cboRequirementName_AfterUpdate()

'Set PriorityGroup combo box to be limited by selected Requirement Set

Me.cboPriorityGroup.RowSource = "SELECT txtPriorityGroup FROM tblRequirementsICS " & _
" WHERE txtTitle = '" & Me.cboRequirementName & "' " & _
" ORDER BY txtPriorityGroup"

EnableControls 'a function that is currently working

End Sub

Right now, I select the cboRequirementName, and the cboPriorityGroup has no entries.

Alan_G

May 4 2012, 06:56 AM

Hi

Welcome to UtterAccess

Immediately before the EnableControls line of code, put

Debug.Print Me.cboPriorityGroup.RowSource

and look in the Immediate window (Ctl + G) where you should see the SQL of the rowsource, including the value of Me.cboRequirementName. Check that that value is the one you're expecting, and also since it seems to be a text value that there aren't any quotes in it. If there are (or there ever could be) then you'd need to trap for that by using for example the Replace() function and doubling them up

accesshawaii

May 4 2012, 06:56 AM

Your code looks right. txtPriorityGroup is text type, correct? Do you have the combo-box set to just one column and it's not hidden? Also, I would suggest using an Auto-ID key as your primary key vice a text based field. You can set the prioritygroup field to be no duplicates in the table but it you would be better off having the relationships set on a numeric field such as an Auto-ID.

eterney

May 4 2012, 07:10 AM

You were right, it was the combo-box setting. I had it set to 2 columns.
Thanks.

New problem, the field txtPriorityGroup in the table has multiple of the same word, so when I click the dropdown it gives me 15 of one term and 5 of another. Can I make it so it doesn't duplicate?

I suppose I could make different tables for each portion as I move through the drop downs, but I'd really like to be able to add new rows without adjusting each table.

Would different queries work?

QUOTE (accesshawaii @ May 4 2012, 07:56 AM)

Your code looks right. txtPriorityGroup is text type, correct? Do you have the combo-box set to just one column and it's not hidden? Also, I would suggest using an Auto-ID key as your primary key vice a text based field. You can set the prioritygroup field to be no duplicates in the table but it you would be better off having the relationships set on a numeric field such as an Auto-ID.

Alan_G

May 4 2012, 07:17 AM

Hi

QUOTE

New problem, the field txtPriorityGroup in the table has multiple of the same word

That sounds like your table(s) aren't normalised and could do with a bit of a rethink. Just a guess though as obviously I don't know how you currently have things set up.

Anyhoooo, you could try changing your query to include the DISTINCT keyword -

CODE

Me.cboPriorityGroup.RowSource = "SELECT DISTINCT txtPriorityGroup FROM tblRequirementsICS etc.....