cascade combobox not working properly (1 Viewer)

eugzl

Member
Local time
Today, 06:03
Joined
Oct 26, 2021
Messages
125
Hi All,
I create form with four cascade comboboxes. The cboBrand and cboDeviceType comboboxes are working good. But cboModel didn't display list when values in cboBrand and cboDeviceType are selected. But when I ran query by itself that populates cboModel combobox it works OK.
The fourth cboHardware combobox display error message when the form is ran:
1636489077717.png

When I commented the line Me.cboHardware = Me.cboHardware.ItemData(0) combobox is populated but I cannot select a value from the list. To display the list in cboHardware need select in cboBrand value "_N/A" and in cboDeviceType value "Other".

I will appreciate for help to fix those problems? The attachment has Access DB file.

Thanks.
 

Attachments

  • CascadeCombo.accdb
    2.6 MB · Views: 345
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:03
Joined
May 21, 2018
Messages
8,463
Need to bind it to the HardwareID of the Device table not the hardware table.
 

eugzl

Member
Local time
Today, 06:03
Joined
Oct 26, 2021
Messages
125
Need to bind it to the HardwareID of the Device table not the hardware table.
Now the form ran without error message. But still I have 2 problems:
1. In cboHardware I cannot select value.
2. The cboModel not populated when values in cboBrand and cboDeviceType selected.
How fix those problems?
Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:03
Joined
Feb 19, 2002
Messages
42,970
1. These combo boxes do not cascade. They probably should, but they don't. The concept of a cascading combo is a hierarchy that limits the choices below. For Example
tblCountry - a list of countries
tblState - a list of states for a country, includes a foreign key to tblCountry
tblCounty = a list of counties for a state, includes a foreign key to tblState
tblCity = a list of cities for a state, includes a foreign key to tblCounty
Your tables have no foreign keys so one combo doesn't limit the selections in another.
2. You have 0 as the default for a foreign key. this has resulted in orphan entries in Brand and Hardware. I reassigned the 0 values to a valid value and that enabled me to enforce Referential integrity.
3. Your other relationships are invalid because they relate data to data instead of data to Primary Key as valid relationships do.
4. Do not attempt to populate data values in the Load event. You are clobbering the first record.
5. Your RowSource queries need to use the Union Queries, not have hard coded SQL.
 

eugzl

Member
Local time
Today, 06:03
Joined
Oct 26, 2021
Messages
125
1. These combo boxes do not cascade. They probably should, but they don't. The concept of a cascading combo is a hierarchy that limits the choices below. For Example
tblCountry - a list of countries
tblState - a list of states for a country, includes a foreign key to tblCountry
tblCounty = a list of counties for a state, includes a foreign key to tblState
tblCity = a list of cities for a state, includes a foreign key to tblCounty
Your tables have no foreign keys so one combo doesn't limit the selections in another.
2. You have 0 as the default for a foreign key. this has resulted in orphan entries in Brand and Hardware. I reassigned the 0 values to a valid value and that enabled me to enforce Referential integrity.
3. Your other relationships are invalid because they relate data to data instead of data to Primary Key as valid relationships do.
4. Do not attempt to populate data values in the Load event. You are clobbering the first record.
5. Your RowSource queries need to use the Union Queries, not have hard coded SQL.
Hi Pat. Thanks for replay.
I redesign tables by this way:
1. Brand table keep same
2. DeviceType table modified to: DeviceTypeID, BrandID, DeviceType
3. Model table modified to: ModelID, DeviceTypeID, BrandID, Model
4. Hardware table modified to: HardwareD, DeviceTypeID, BrandID, Hardware
Also redesign queries that populated combo boxes.
Now I have only two problems with cboHardware combo box. When form is ran I got error message
1636513586705.png

When I comment the line Me.cboHardware = Me.cboHardware.ItemData(0) cboHardware populated properly but I cannot select value.
When I try to select value at the bottom of the screen I see message: "Control cannot be edited, it's bound to AutoNumber HardwareID field". I cannot figure out. The three combo boxes design and populated the similar way two are work, third doesn't. I will appreciate if you show and explain how to fix those problems. In the attachment the last version.
Thanks.
 

Attachments

  • CascadeCombo.accdb
    2.6 MB · Views: 337
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:03
Joined
May 7, 2009
Messages
19,169
you don't need those bunch of joins.
 

Attachments

  • CascadeCombo.accdb
    2.6 MB · Views: 371

eugzl

Member
Local time
Today, 06:03
Joined
Oct 26, 2021
Messages
125
you don't need those bunch of joins.
Thanks that continue to help.
Your version of the form is ran without error message. But the cboModel and cboHardware not populated.
Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:03
Joined
Feb 19, 2002
Messages
42,970
Please read my earlier post again. Do NOT populate values in the Load event of the form. You are clobbering the first record. What are you trying to accomplish? If you are trying to provide defaults, do it in the BeforeInsert event. That way your code won't dirty a record since the user has already dirtied it and EVERY record will have the defaults not just the first one.
 

eugzl

Member
Local time
Today, 06:03
Joined
Oct 26, 2021
Messages
125
Hi Pat. Thank you for continue help.
I'm new in Access. I will try briefly describe what I would like to create. I'm trying to create inventory database. The f_Device - Device form will give user ability to add a new device to DB. I would like when user will open form the all combo boxes display default value. For instance, "Select something". I try create cascade combo boxes. I would like that selected value of the cboBrand will filter list of cboDeviceType. And selected value of the cboDeviceType will filter list of cboModel and cboHardware depends of value selected. When values are selected in all combo boxes user will have ability to save selected data to Device table by click Save button.
In the attachment last version. I didn't find event BeforeInsert like you suggested and try to use BeforeUpdate but it didn't help to display default value. I will very appreciate you if you have a sample or can do me a favor to modify my file to show how it need be to do.

Thanks
 

Attachments

  • CascadeCombo.accdb
    2.6 MB · Views: 381

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:03
Joined
Feb 19, 2002
Messages
42,970
I personally dislike having "select something" or other such phrase in the list. I think it is more obvious to the user that a combo is empty than if it has text in it. You can have code in each of the child combos that gives an error if the immediate parent combo is null.

I and the others have explained how cascading combos work.

BeforeInsert is a FORM level event.
 

eugzl

Member
Local time
Today, 06:03
Joined
Oct 26, 2021
Messages
125
I personally dislike having "select something" or other such phrase in the list. I think it is more obvious to the user that a combo is empty than if it has text in it. You can have code in each of the child combos that gives an error if the immediate parent combo is null.

I and the others have explained how cascading combos work.

BeforeInsert is a FORM level event.
Pat, just tell me about the structure and design of tables and queries that I used to populate combo boxes. Do I fixed in correct or they still have problems? And if they have problems. What need to fix?
Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:03
Joined
Feb 19, 2002
Messages
42,970
This is a lot better than it was but you really needed to start the project by building the relationship tables rather than the final table. You will have a lot less trouble if you get the cascading relationships right BEORE you try to use them. So build those FOUR forms FIRST because that is how you make the data relate. THEN apply them to the final form. I removed the popup/modal properties because they are way too annoying during development. Put them back if you really want them.

You were getting the errors because you CANNOT modify a field in its BeforeUpdate event. I moved that code to the form's BeforeInsert event. As I said, I would not do this at all but if you insist, then you have to do it in an event that makes sense.
 

Attachments

  • CascadeCombo Pat.accdb
    692 KB · Views: 333

Users who are viewing this thread

Top Bottom