Ms-access – MSAccess: creating a new row in a related table and linking it back to the “master” table

application-designdatabase-designms accessms-access-2016

In a hypothetical 'cars' database, assume there's a model table and a make table:

models
  modelID int
  model varchar
  make int

makes
  makeID int
  make varchar

Model's make column has a foreign key relation to the make table's primary key. You can use a query against make to populate the 'make' combo box on the model form. If you enter a model for which there isn't a corresponding make row yet, you'd want it to insert the new make (since the input doesn't match any existing makes.make value), then insert the model row with the new make row's primary key in the models.make column. Pretty basic stuff.

I can't seem to figure out how to do it easily in Access 2016, though. If I use the form wizard to create the model management form and feed it either the makes table or a query as a source for the makes combo box element, I can get the list of makes to populate the fill list, but it chokes if I try to enter a make that isn't in the list. It won't let me disable the 'limit to list items' property of that interface element unless I include the makes.makeid column not just in the form's data sources but as another visible item in the box itself. This doesn't fix the insert problem and also breaks the new entry behavior when the make does exist.

Is there an obvious way to do this out of the box in Access 2016? The only solutions I see are

write a macro to insert the new make if it doesn't exist, catch the new primary key, and feed it back into the model insert
change models.make to varchar and link it directly to makes.make
turn the combo box into a restricted list box, use a different form to populate makes

None of these are particularly elegant. I've watched some tutorials and looked at their sample databases and haven't seen this scenario addressed in any useful way. Is there a simple way to use another table as a data source and create new rows in it on-the-fly and I'm just overlooking it, or is this really not something that Access does well natively?

Best Answer

Your question is legitimate and there are some reasonable patterns that you can follow to get it working. However, I also feel like your question demands some comments regarding Access forms and controls. so sorry if it's a bit chatty.

It seems completely reasonable to be able to add a new make when entering a new model for a make not yet in the database. That can be done, so my next comments are not to discourage or dissuade. However, the built-in controls and data binding in Access do not facilitate this behavior and there are good reasons for it. Custom macro and/or VBA event programming can overcome these limitations.

This also requires an understanding of what a ComboBox is really doing and not expecting it to do more. The value list of a ComboBox is largely read-only. The primary reason for this is that it accepts almost any complex query--including non-updatable queries--as the RowSource for the list values. Just because the [Makes] table only has one Text column (beside the ID column) does not mean that the ComboBox has any built-in way to automatically insert a new value into that table. I'm not familiar with any ComboBox even in other environments that automatically facilitate such behavior without custom event programming. This is not just a question of elegance, rather the impractical ability of updating any arbitrary source. From my experience, most of my ComboBoxes have lists from complex data sources that would not accept a single value for adding to the list.

Elegance is a very subjective thing. Honestly, one could argue that normalized relational database tables are completely inelegant compared to a well-designed object model with modern object databases, but that's a longstanding debate and Access is certainly not state-of-the-art. Access often demands that you deal with separate inserts, retrieving ID values, explicitly refreshing form and control query sources, etc. Pessimistically, Access is not elegant. But it can be very convenient and has its plus sides when you don't/can't worry about installing and/or maintaining other RDMS systems and marrying it to a separate GUI environment.

As for the ComboBox properties, it is possible to hide extra column values using the ColumnWidth property, like ComboBox.ColumnWidth = "0;1.5". This allows you to bind to the correct numeric value while displaying the corresponding text values ([makes].[make] in this case). How would you even properly bind to the [model].[make] field without the numeric ID?

All that said, your own #1 option is probably the best approach:

Write a macro to insert the new make if it doesn't exist, catch the new primary key, and feed it back into the model insert

I personally prefer VBA instead of a macros. Consider handling the "Not In List" event of the ComboBox.

An alternative is to create a separate form for managing the [makes] separately. Provide a button or link (or some other various way) to open the form for adding new [makes]. In your case this probably seems overkill since you only have one data field, but if you start adding more fields to the [makes] table (e.g. website URL for example), you will definitely need something beyond an automatic ComboBox.

Firstly, a schema...

tblLocation(locationID[PK++], locationReference)
tblRoom(roomID[PK++], keyLocation[tblLocation.locationID], roomNumber)
tblRack(rackID[PK++], keyRoom[tblRoom.roomID], rackReference)
tblDevice(deviceID[pk++], keyRack[tblRack.rackID], positionU, deviceLabel)

...tables for custom devices can extend tblDevice

Foreign keys in square brackets, PK++ means an auto increment primary key.

As you can see, a Location has many Rooms, a Room has many Racks, a Rack has many Devices.

I have made primary keys as auto IDs as I prefer them to be integers and there's less to think about should you change a location/room/rack reference.

Populate this with Locations, Rooms and Racks as appropriate.

Solution 1 :: Not a lot of racks

Have three drop downs on the form.
User chooses a location, you then select all the rooms of that location in the next combobox.
User then selects the room, you then fill the next combobox with the racks in the room.
User then fills in device details and U position (which you probably want to validate to make sure that you don't have anything allocated there.)

Solution 2 :: Lots of racks

Have a query similar to:

SELECT
    rackID,
    locationReference + '-' + roomReference + '-' + rackReference AS [Full Location]
FROM tblLocation
    INNER JOIN tblRoom
        ON tblLocation.locationID = tblRoom.keyLocation
    INNER JOIN tblRack
        ON tblRoom.roomID = tblRack.keyRoom
ORDER BY
    locationReference ASC,
    roomNumber ASC,
    rackReference ASC

To generate a list of Location-Room-Rack references.

ABC-101-A/1
ABC-101-A/1
ABC-101-A/2
ABC-101-A/2
ABC-105-B8
ABC-105-B8
FAY-208-X8
FAY-210-A9T
FAY-210-A9T
FAY-210-F4
NAT-410-78
NAT-410-78

User selects one, then fills in the information. List will be equal to the number of racks (which could get a bit excessive).

Advantages

If you move a rack (or racks) of equipment from one room to another, it's just a simple update query to update the database
If a location/room/rack reference changes, you only have one piece of data to update.

Further schema additions

I've used racks in the past, I simplified the schema but these are some additions that spring to mind.

Add rackSize to tblRack: the size in U's
Add sizeU to tblDevice: the size of the device in U's

You can then do further validation: if you have a 39U rack and somebody tries to put a 5U device in at location 38, you can throw an error (as U's 40-42 don't exist!). Also if somebody puts a 3U device in at location 10, and something is in location 11, you know it won't fit or they have not updated the details of the device that is no longer there!

You could also make some clever queries that would tell you how much space there is in your racks (and where). But that's beyond the scope of this question!

Hope this helps.

Database Design – How to Handle Unavoidable Circular References

A reasonable solution is the second one, that is to remove the MakeId FK from Vehicles, for two reasons:

The information about the Maker of a certain Model is already contained in the Model record, so storing it inside Vehicle is redundant.
Having both attributes in Vehicle is prone to errors, since one could have inconsistent information inside a Vehicle.