Subform for existing entries only (1 Viewer)

I'm using the following scheme to track how unique widgets come in and go out.


The receipt form is related to its widget subform through m.id = w.from_movement_id and the shipment form through m.id = w.to_movement_id.

The receipt_widget subform works well for adding widget entries that are received for the first time, but the shipment_widget subform doesn't precisely because it insists on adding new entries even though it is not supposed to do so. How can I configure figure the shipment_widget subform to have the user chooses existing widgets.

As of now, the shipment_widget shows "New" in the id field of its first blank row. As soon as one clicks on it, the id turns into the next autonumber. If I then proceed to choose an existing widget from from a combobox bound to w.id or even try to edit the id field directly, Access doesn't let me do so. If I disallow new entries in the subform, then I can't do anything with it at all.

I have an idea how to do this using the combobox's before update event and requery, but I wonder if there's a simpler way. Thank you.

Do you still need help on this?

Kind of. It's not urgent because this will be a refactoring of an already working code, plus I have an idea of how to do it (using a before-update script), but any suggestions would be appreciated.

To me it doesn't make sense that a widget has a single from_movement_id and a single to_movement_id. A widget should be able to exist without any movement. Alternatively, a widget should be able to exist that has moved many times. The conclusion to draw, I think, is that the concept "widget movement" is an entity unto itself, and not a direct property of a widget at all, and as a result, I think it is a mistake that the widget table contains any FK links to movement records.

To put it perhaps more simply: to completely describe what a widget is, it does not matter where it is or when it got there.

Similarly though, I expect a movement might involve many widgets at once, and if so, then you probably need a many-to-many relationship between the Movement table and the Widget table, and then you need a third table.

tMovement
MovementID (PK)
MoveDate
origin_id
destination_id

tMovementDetail
MovementDetailID (PK)
MovementID (FK)
WidgetID (FK)

tWidget
WidgetID (PK)
model_id
serial_number

Click to expand...

See how this scheme leaves your widget definition completely free of any movement information? Also, a Movement might now contain any number of widgets, and a widget determines its location by consulting it's movement history. This model makes more sense to me.

In business terms, these three tables would be named tOrder, tOrderDetail, and tProduct, and it is a very common pattern to keep track of things (products, widgets, whatever) that move, and when those movements occur.

hth
Mark