Can select Values in Cascading Combo box

New Contributor

I am creating an Inventory Database for out IT equipment and I am still learning Access (I have used it a little in the past, but that was years ago, so I have been taking a course on CBTNugget as I create this database and my SQL knowledge is still a little limited).
The data is being imported from the Excel-based inventory Workbook I previously created.

I set-up Cascading Combo boxes with a Brand and a Model combo box, so that when a Brand was selected in the Brand Combo box, from available Brands pulled from the Brands Table, the Model combo box would requery for valid Models based on the Brand selected, from the Hardware Table.

This part worked, except, I couldn't select from those models.

I found that if I went onto another record and came back, the values in the Model box became selectable. This led to also discovering that the values in the Model box would also become selectable if I saved every time I selected a Brand.

This was the Relationships:

WyzZero_0-1626801072237.png


And this was the Query used that the Form was bound to (Inventory Query):

 

SELECT Inventory.Barcode, Hardware.Brand, Inventory.Serial, Inventory.[Model#], Inventory.Current_Assigned, Hardware.Description, Hardware.Type, Inventory.Previous_Assigned, Inventory.BitLocker, Inventory.[Host Name], Inventory.[IO#], Inventory.[Order #], Inventory.Warranty, Inventory.[IP Address], Inventory.Notes, Inventory.Updated, Employee.Legal_Hold, SL.Status, SL.Location


FROM Employee INNER JOIN (SL INNER JOIN (Hardware INNER JOIN Inventory ON Hardware.[Model] = Inventory.[Model#]) ON SL.[ID] = Inventory.[LocID]) ON Employee.[Employee] = Inventory.[Current_Assigned];

 


---------------------------------

I had someone give me recommendations to change each of the Relationships with Inventory to Join Type 3 (Include ALL record from 'Inventory' and only those records...).

I created a new query doing this, which resolved an issue I didn't know I had. Evidently, my previous query was only showing me a small portion of my records as it was removing any record that had certain fields empty.


I then created a form based on this query and once again configured Brand and Model as combo boxes, except now, I can't select values from either combo box, at all.


Here is the updated relationship table (which also shows the 'Phones' Table that was previously hidden for some reason):

WyzZero_1-1626801311445.png

 


Here is the new Query:

 

SELECT Inventory.Barcode, Hardware.Brand, Inventory.Serial, Inventory.[Model#], Inventory.Current_Assigned, Hardware.Description, Hardware.Type, Inventory.Previous_Assigned, Inventory.BitLocker, Inventory.[Host Name], Inventory.[IO#], Inventory.[Order #], Inventory.Warranty, Inventory.[IP Address], Inventory.Notes, Inventory.Updated, SL.Status, SL.Location

FROM SL RIGHT JOIN (Hardware RIGHT JOIN Inventory ON Hardware.Model = Inventory.[Model#]) ON SL.ID = Inventory.LocID;

 



Also, if it helps:
Brand's combo box Row Source:

 

SELECT Brands.Brand
FROM Brands;

 


Model#'s combo box Row Source:

 

SELECT Hardware.Model, Hardware.Brand
FROM Hardware RIGHT JOIN Inventory ON Hardware.Model = Inventory.[Model#]
WHERE (((Hardware.Brand)=[Forms]![QryInv]![Brandbox]));

 

 

1 Reply

@WyzZero 

I see nobody has replied despite almost 30 views. Perhaps like myself, other members are finding it difficult to work out exactly what issues you are having.

I believe that you now understand the differences between inner and outer joins. However you may find this article useful to clarify your knowledge: Query Join Types 

In addition, make sure you understand about Relationships and Referential Integrity 

 

In case it helps, I have an example app which uses 5 cascading combo boxes to filter UK postcodes.

See Cascading Combo Boxes 

 

I hope that helps