Triple Cascading Combo Boxes

[JWPratt8]

Hi all,

I have a form with various input boxes, three of which are combo boxes. These combo boxes need to affect one another. So let's call these cmb1, cmb2 & cmb3.

Hypothetically let's say:

Within cmb1 the list is A, B, C & D

If the user was to choose A, cmb2 would show the options 1, 2, 3, 4, 5 etc..
If the user was to choose B, cmb2 would show the options 2, 4, 6, 8, 10 etc..
if the user was to choose C, cmb2 would show the options 1, 3, 5, 7, 9 etc..

(So, it is possible that if the user chooses A from cmb1, cmb2 would show 1 and if the user chooses C from cmb1, cmb2 would also show 1)

Using the example in the brackets above:

If the user chose cmb1 - A then chose cmb2 - 1, cmb3 would then show the options Option1, Option2, Option3 etc..

BUT

If the user chose cmb1 - C then chose cmb2 - 1, cmb3 would then show the options Random1, Random2, Random3 etc..

Hopefully you guys can follow, what I'm trying to convey.

At the moment I have a table with 3 columns. Within the table is every possible combination of the drop down, i.e:

-A l 1 l Option1 l
-A l 1 l Option2 l
-C l 1 l Random1 l
-C l 1 l Random2 l
Etc...

I have tried to use the example shown on this website but i've had no luck: fontstuff.com/access/acctut10 (If you look at "Example 2", thats what I tried to do)

I hope someone out there can help me!

Thanks,

J.

 

[JHB]

I've made a small sample for you in the attached database.

 

[JWPratt8]

Thank you for your reply JHB. That's almost what I need, but without having duplicates within the dropdowns. Is there any way to remove the duplicates?

(edit) Also the third drop down (FieldNo3) doesn't work.

(edit) Actually, scratch that last sentance. It does work, but once you have chosen the first dropdown and the 2nd dropdown is updated, you can't then go and change the 1st field with the 2nd dropdown re-updating. The 2nd dropdown stays as it was for the first choice.

Thanks,

J.

 

[JHB]

Thank you for your reply JHB. That's almost what I need, but without having duplicates within the dropdowns. Is there any way to remove the duplicates?

Yes put in a DISTINCT in the select statement.

(edit) Also the third drop down (FieldNo3) doesn't work.

(edit) Actually, scratch that last sentance. It does work, but once you have chosen the first dropdown and the 2nd dropdown is updated, you can't then go and change the 1st field with the 2nd dropdown re-updating. The 2nd dropdown stays as it was for the first choice.

Thanks,

J.

By me it works - if you change the first combodox, the second (and third) combobox is cleared, and the second combobox is re-queried.
The third combobox's list need to be cleared.
But in the line below, red text.

Private Sub cmb1_AfterUpdate()
  Me.cmb2 = ""
  Me.cmb3 = ""
  Me.cmb2.Requery
  [B][COLOR=Red]Me.cmb3.Requery[/COLOR][/B]
End Sub

 

[JWPratt8]

Brilliant thank you.

I am having trouble with the drop downs though. I have looked thoroughly at your example and copied it into what I need for my database. I have created the SELECT DISTINCT queries for all 3 dropdowns, and used the exact code you used too. The first dropdown is fine, and there are no duplicates.

BUT, for some reason my dropdowns don't update each other, they just show every field without duplicates.

Here is the code I've used:

Private Sub cboDealerGroup_AfterUpdate()

Me.cboDealer = ""
Me.cboDealer.Requery

End Sub

Private Sub cboDealerType_AfterUpdate()

Me.cboDealerGroup = ""
Me.cboDealer = ""
Me.cboDealerGroup.Requery

End Sub


FYI, my actual database is different to the the example I gave in my first post.

If you could point out why this would be happening I would be very grateful.

Thanks,

J.

 

[JWPratt8]

I have recently found this thread
http://www.access-programmers.co.uk/forums/showthread.php?t=250011, which shows exactly what I'm trying to acheive. However, I have only just started using access within the last 2 weeks and can't seem to get it to work for me.

My details are as follows:

Currently my dropdowns work so that;
cboDealerType is based on cboDealerGroup and
cboDealer is based on cboDealerGroup
BUT!!!!!!!
I want cboDealer to be based on both cboDealerType and cboDealerGroup

Parameters are

Table = Dealer List
cboDealerType data = [Dealer List].Type
cboDealerGroup data = [Dealer List].Group
cboDealer data = [Dealer List].Dealer

Perhaps anyone can help me on this route.

Many Thanks,

J.

 

[JWPratt8]

I have now solved this problem! So for anyone that was interested here is the solution:

My details are as follows:

Currently my dropdowns work so that;
cboDealerType is based on cboDealerGroup and
cboDealer is based on cboDealerGroup

BUT I needed cboDealer to be based on both cboDealerType and cboDealerGroup

Parameters are

Table = Dealer List
cboDealerType data = [Dealer List].[Type] <---Shows "Type" column
cboDealerGroup data = [Dealer List].[Group] <---Shows "Group" column
cboDealer data = [Dealer List].[Dealer] <---Shows "Dealer" column


Where the table has 3 columns; Type, Group & Dealer. Within the table is every possible combination of data between the three columns.

I was able to solve this using code only -

Private Sub cboDealerType_AfterUpdate()
On Error Resume Next

cboDealerGroup.RowSource = "Select DISTINCT [Dealer List].Group " & _
"FROM [Dealer List] " & _
"WHERE [Dealer List].[Type] = '" & cboDealerType.Value & "' " & _
"ORDER BY [Dealer List].[Group];"
Me.cboDealerGroup.Requery

End Sub

Private Sub cboDealerGroup_AfterUpdate()
On Error Resume Next

cboDealer.RowSource = "Select DISTINCT [Dealer List].Dealer " & _
"FROM [Dealer List] " & _
"WHERE [Dealer List].[Type] = '" & cboDealerType.Value & "' AND [Dealer List].[Group] ='" & cboDealerGroup.Value & "' " & _
"ORDER BY [Dealer List].[Dealer];"
Me.cboDealer.Requery

End Sub


Hope this helps anyone that was in my situation.

J.

 

[alvincgp]

Hi all,

I've a similar problem with JWPratt8 but my last combo box was a list box which after user have selected the first two option on combo1 & 2 it will populate a list of options. I've successfull getting the result but now I need user to input their value base on the option from the list box.

Is anyone here able to help?