MSAccess: creating a new row in a related table and linking it back to the "master" table

Question

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

1. 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
2. change models.make to varchar and link it directly to makes.make
3. 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?

Answer 1

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.