multi-cascade combo boxes

[eugzl]

Hi All.
I would like to create cascade combo boxes. For that I created 4 tables:
1. Brand table: BrandID, Brand
2. DeviceType table: DeviceTypeID, BrandID, DeviceType
3. Model table: ModelID, DeviceTypeID, BrandID, Model
4. Hardware table: HardwareD, DeviceTypeID, BrandID, Hardware

And 4 queries to populate combo boxes:
q_cboBrand, q_cboDeviceType, q_cboModel, q_cboHardware



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.
To display the list in cboHardware need select in cboBrand value "_N/A" and in cboDeviceType value "Other".
Now the first three combo boxes work properly but fourth cboHardware doesn't. In the attachment my last version of the file. I will appreciate if someone will show how to fix the problem.

Thanks.

[June7]

I tried to open file but I am using Access 2010 and something in your db is not backward compatible and won't open.

[eugzl]

I tried to open file but I am using Access 2010 and something in your db is not backward compatible and won't open.

Hi June7. Thanks for reply.
Unfortunately I don't have Office 2010 and cannot to convert the file into earlier version. I will appreciate if you have and share sample similar which I'm looking for. With at least 4 combo boxes cascade. Or may be you have link that demonstrate how it to do.

Thanks

[Micron]

A table is using BigInt but I see no reason for that. I've changed that to Long and re-zipped it for others because I have to go out in about 30 minutes.
Having trouble making sense of the left joins for combos as I suspect it will likely allow the creation of records for non-existing combinations. Suggest the use of equal joins but I don't think the data table(s) is/are correct.
There are copies of some objects because I edited the originals.

CascadeCB2.zip

[eugzl]

I modified the file and cascade works fine but I still have 2 problems:
1. When I selected in cboBrand value "_N/A" then the cboDeviceType will display "Other" and cboHardware populate by according values. Up to this moment all is OK. But I cannot select value from cboHardware. At the bottom of the screen I see message: "Control can't be edited; it's bound to AutoNumber field HardwareID." All my combo boxes bound to AutoNumber fields. Why that combo box has such a problem?
2. When form is ran and I filled fields by some values, it automatically changing value in according fields of the Device table. How to avoid it? I would like to insert a new record.


In the attachment my last version of the file. I will appreciate if someone will show how to fix those problems.
Thanks.
CascadeDetail.zip

[June7]

1. Cannot be bound to autonumber field because that would be attempting to save a value into that field. In other words, ControlSource cannot be autonumber field because that will not accept user input. Setting BoundColumn property is not the same as binding control to a field in ControlSource.

2. Make sure form is on a new record row before executing code to populate fields.

[eugzl]

Hi June7
Unfortunately you can't open my file. That is query of the form:

SELECT Device.DeviceID, Device.BrandID, Brand.Brand, Device.DevTypeID, DeviceType.DeviceType, Device.ModelID, Model.Model, Device.HardwareID, Hardware.HardWare, Device.Description
FROM Device 
LEFT JOIN DeviceType 
ON Device.DevTypeID = DeviceType.DevTypeID 
LEFT JOIN Hardware 
ON Device.HardwareID = Hardware.HardwareID 
LEFT JOIN Brand 
ON Device.BrandID = Brand.BrandID 
LEFT JOIN Model 
ON Device.ModelID = Model.ModelID;

That is query to populate cboModel:

SELECT Model.ModelID, Model.Model, 1 AS SortRow
FROM Model
WHERE (((Model.BrandID)=[Forms]![f_Device]![cboBrand]) AND ((Model.DeviceTypeID)=[Forms]![f_Device]![cboDeviceType]))
UNION SELECT 0, "Select Type", 0 FROM DeviceType
ORDER BY SortRow, Model.Model;

And that is query to populate cboHardware

SELECT Hardware.HardwareID, Hardware.HardWare, 1 AS SortRow
FROM Hardware
WHERE (((Hardware.BrandID)=[Forms]![f_Device]![cboBrand]) AND ((Hardware.DeviceTypeID)=[Forms]![f_Device]![cboDeviceType]))
UNION SELECT 0, "Select Hardware", 0 FROM Hardware
ORDER BY SortRow, Hardware.HardWare;

The combo box cboModel works properly, and in combo box cboHardware I can't select value. But code of queries is similar. Where is problem?
Thanks

[ssanfu]

I think you have made the queries overly complex. Here is a dB I created a long, long time ago to help me learn about cascading combo/list boxes.
There are two forms - one with cascading combo boxes and one with cascading list boxes.

Sorry about the names of the tables. As I said, this was made a very long time ago. I should rename the tables to have the prefix "tbl" first, then the number, then the name.
So instead of "1tblUnit", I should have named the table "tbl1Units". (You should never have a number as the first character of a table name.)



Also, the top two lines of EVERY module should be:

Option Compare Database
Option Explicit

[eugzl]

Hi ssanfu. Thanks for reply.
I very appreciate. Grate help. Thanks a lot.

[ssanfu]

Happy to try and help.