Link Subform to Form with Part of Field

[velcrowe]

I have a subform and form that I want to link by purchase order numbers. My form has the main purchase order numbers such as WidgetsA, WidgetsB, WidgetsC, and so on. My subform also has purchase numbers but are enumerated based on the purchasing sequence of the Widget. So everytime I order a WidgetA the purchase order gets and number extension such as WidgetA.01, WidgetA.02, etc. How can I link the form and subform so that the form and subform are linked by purchase order number such that the WidgetA is linked to WidgetA.01, WidgetA.02. I've tried using wildcard characters but I cannot figure it out. Any help would be most appreciated. Thank you in advance.

 

[Rabbie]

The simplest way is to have non-meaningful primary keys in both tables. Then you can link the tables easily. This also allows you to create you sub-widget purchase numbers by concatenating the two parts of it.

 

[Pat Hartman]

You need to change your application to using autonumbers as Rabbie suggested. In the sub-widget table, you will have an autonumber primary key and a foreign key that points to the related widget in the parent widget table. This will allow you to enforce referential integrity and use the cascade delete option as well as utalize the master/child links on forms.