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:
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):
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]));
