Combo boxes turning to text fields

[Lhoj]

Ok, let me start by saying I'm pretty new to Access and there's probably something very obvious I'm missing.



I had this inventory DB working alright for the most part. My problem came with a specific part of it. The HDDs where through two fields to either a record of the "PCs" table or the "Laptops" table, depending in where they were installed. The table setup for this conflicting part of the DB was more or less like that.



While this worked great it gave me the following problem. Whenever I showed the results of a HDD lookup query on a datasheet subform, the combo boxes linking the HDD table to the PCs or laptops table turned to uneditable text fields. Even the model combo box from the HDD table turned to a text field showing the autonumber PK of the models table instead of the model name. However, this only happened when I did a union query, something like the following:

SELECT HDDs.*  FROM HDDs INNER JOIN PCs ON HDDs.InPC=PCs.SerialNum WHERE criteria UNION  SELECT HDDs.* FROM HDDs INNER JOIN Laptops ON  HDDs.InLaptop=Laptops.SerialNum WHERE criteria

So, the above query worked well separately and showed the combo boxes on the subform, but the union of both of them resulted on the every combo box in the query turning to uneditable text fields. So, to avoid the union query, I merged the Laptops and PCs tables in a single Computers table, with two sub tables with a 1-1 relation to hold the model and some other specific info depending if they're laptop or desktop computers. The HDDs table is now linked to the Computers table so there's no need for a union query. The current schema of that part of the DB is the following (simplified)


Now I don't need a union in my query, but the following query

SELECT HDDs.* FROM HDDs INNER JOIN Computers ON HDD.Computer=Computers.SerialNum WHERE criteria

is having the same behaviour the query with the union did. The computer and model combo box fields turn to uneditable text fields on the form. If I run the same query as an independent query it shows the results ok (with comboboxes). The form is a copy of the one I use for the monitors (just changing the source) and that one works normally.

Example of a search query result:

Same query with the results shown on the form:


Any idea what it might be?

[June7]

UNION queries are by nature not editable.

Have you built these comboboxes as lookup fields in table? Advise not to do that - just build comboboxes on forms. Don't see anything from the ERD that explains why you have this issue with a simple join query. However, why include both tables in form RecordSource?

Your attachment is not valid, just errors trying to download.

[Lhoj]

Thx for the fast response June! Fixed the image/attachment issue, got disconnected in the middle of writing the post and apparently it didn't restore the first image right.

Yes, the combo boxes are built as lookup fields in the tables if only because, even if not advisable, I might have to let users occasionally edit the tables directly since due to the wide variety of operations needed not all of them are covered with the forms. Luckily all the users will be computer technicians and the direct editing will be rare, but on those rare occasions having a combo box in the table will be of great help. Thing is all the tables are built like that and this is the only one having that weird behaviour, even though the subforms are all the same and the queries almost identical too.

Btw, both tables are included because of some of the search criteria which force me to include the 'parent' table on those queries.

[Minty]

If your Laptops and Computers where in the same table with an Product type Identifier (What happens if you want to add Chromebooks for instance) you wouldn't have this problem?
You could do the same with your two Model Numbers tables - add a type and you can restrict the data to the type based on what it is.

I agree with June - get rid of those look ups and build some simple table maintenance forms that have the combo's you need in them.

[Lhoj]

They're kind of in the same table now, the separate tables will now only work for some exclusive fields for those types and the link to the corresponding models table. Yes, I could do that in the same table with a type field and some modifications to the relations, but I wanted to have the inventory as modularized as possible and the common fields aren't that many. Actually the only reason I finally merged some fields from both is because I actually need to link the HDDs to a certain computer instead of to the upper entity 'Workstation' where most components are linked, because I need to keep track of the actual system they're installed on and I couldn't seem to do it through a union query.

Anyway, I redid the form and subform from the scratch (which I had already done before) and for some reason now it's working as intended. So now I only need to tinker a couple update queries a bit and I'm ready to go. Thanks for the tips!

[Minty]

As a general rule of thumb, you shouldn't have a table for specific subset of data types.

Lets take screen size as a potential field - on your data form you can easily set cboScreenSize to be disabled or hidden if the unit type is PC instead of Laptop.
Lets say you add Tablets or CAD Workstations as a type, you wouldn't want to create a new table. MacBooks? They all share a lot of common parts.
It's a lot easier to have a few fields of redundancy and one table, than trying to join up multiple tables with the same core data.
The unit could have multiple Hard drives, so they may need to be in a child table.