Update subform with ID from parent form.

Question

Hi All,

We have an Order form (frmOrder) which is currently using a temporary Order ID until an invoice has been issued at the end of the month. 

However, after amending the Order ID in the parent form, the associated trainees in the subform (sfrmTrainee) get "lost" as their OrderID field does not get updated in the Trainee table. I know this is probably simple stuff but I am struggling with getting an After Update event to work.

Thanks!

Answer 1

If your order ID is subject to change, your mainform and subform should probably be linked by a different field, such as an autonumber Primary Key in the main table.  To update your subform records you could use VBA similar to:

dim sSQL as string

sSQL = "UPDATE MySubformTable SET OrderID = '" & Me.OrderID & "' WHERE ForeignKey = " & Me.PrimaryKey 

CurrentDB.Execute sSQL, dbfailonerror

Me.MySubformControlName.Form.Requery

The above assumes OrderID is a text field.  If it is numeric, drop the embedded single quotes. 

You could also use similar code with the where condition based on the 'previous' order ID if you include code to preserve that value.

EDIT:

Always make a backup and/or use test data when testing any action queries.

---

Miriam Bizup Access MVP

Answer 2

Where a 'natural' key is used like this, you should always enforce cascade updates in the relationships with any referencing tables.

You'll find an example in InvoicePDF.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

While this little demo file is primarily intended as an illustration of how to output a report as a PDF file it happens to be based on a simplified hypothetical invoicing application.  You'll see that in the main invoice form the invoice number is automatically generated, but can be edited.  If rows are inserted into any of the referencing tables via the invoice details of payments subforms, and the invoice number in the parent form is subsequently edited the row in the Invoices table still maps correctly to the relevant rows in the referenced tables as a result of the cascade update operations.

---

Ken Sheridan, Stafford, England