Cascading Combo Problem

[Pat Hartman]

You should always use queries (preferably stored querydefs rather than SQL strings) as the RowSources for list and comboboxes and the RecordSources for forms and reports.

 

[Pat Hartman]

Take a look at this "brilliant" solution to the problem of Cascading Combos on continuous forms.

 

[scouser]

You are Brilliant!!

Pat you are a superstar. I have implemented your solution. I have only one (OK maybe 2 questions!!).

After inputting a second record via the modified combo, if I choose to revisit the first record entered the list items are relative to the LAST entry.

i.e.
Record One -
Chose Make 'Ford' second combo displays all associated Models for Ford - Selected 'Focus'

Record Two-
Chose Make 'Bentley' second combo displays all associated Models for Bentley - selected 'Vanquish'.

If I then revisit the first entry it shows only models for 'Bentley' and not 'Ford'. This is only a minor blip, I presume it would be the same for your example?

Also (not your problem!). You helped me out ENORMOUSLY with this issue.

Rich pointed out that the value being saved in tblModels for MakeID is the text value and
not the MakeID? I can not see how to resolve this? i.e.
ModelID = 1
MakeID = Ford (this should be a numerical value?)
Model = Focus

Anyhow, I have attached my DB so you can see how the combo is behaving.

Once again, great solution!!
Many Thanks,
Phil

PS: I have added some custom nav buttons to spice things up!!

 

[Pat Hartman]

If I then revisit the first entry it shows only models for 'Bentley' and not 'Ford'. This is only a minor blip, I presume it would be the same for your example?

-No it is not the same for my example. My example works. You really need to improve your "paying attention to details" skills. Otherwise I would suggest a career change. I already fixed this for you in your other database. You need to requery the combo in the form's current event as well as in the make combo's AfterUpdate event.

Rich pointed out that the value being saved in tblModels for MakeID is the text value and

- You have defined a lookup at the table level. The id is being stored but the text value is shown when you open the table. I suggest removing this lookup. It is a "feature" designed to help novices who can't write queries. It actually gets in the way if you create queries and write VBA.

Take a look at This Post for another "feature" gone bad.

BTW - every time I open your db it puts me in a bad mood. Why you remove the menus and remove access to design options in a development db is beyond me. So, every time I double-click to open it, I remember, I can't open it that way. So I have to close it and open it again while holding the shift key down. At least mention that when you're asking for help next time.

 

[scouser]

Woops Sorry!

Sorry Pat.......having to hold the shift key is a pain I know. I forget each time I post! (promise not to again!!). I am modifying a DB for my brother. I prevent the menus from showing so he doesn't meddle!

Attention to detail is usually good........honest!!

No it is not the same for my example. My example works.

Should have known that!!!!!!

Cheers Pat....getting late here!!
I will implement your suggested changes and then get some much needed beauty sleep!!
Phil.

 

[scouser]

All Done

Many thnaks Pat, all working A-OK.