Master Detail example

Hello,

I need to update an Access Database from another Access Database – both DB’s have identical schemas.

Imagine on the READ side

Table = Customer
CustID CustName
1 Microsoft
2 Apple
3 FlowHeater <<<<<<< A NEW CUSTOMER ON THE READ SIDE

Table = CustAddress
AddID CustID (Foreign Key) AddressLine1 AddressLine2
1 2 Somewhere United States
2 1 Redmond United States
3 3 Bischberg Germany

Table = CustContact
ContID CustID (Foreign Key) Name
1 1 Bill Gates
2 3 Robert Stark


And on the WRITE Side …

Table = Customer
CustID CustName
1 Microsoft
2 Apple
3 Google <<<<<<< CustID 3 – 5 have already been used on the WRITE side
4 …..
5 ……
6 FlowHeater <<<<<<< FlowHeater gets AutoInc of 6


Table = CustAddress
AddID CustID (Foreign Key) AddressLine1 AddressLine2
1 2 Cupertino United States
2 1 Redmond United States
3 3 Silicon Valley United States


Table = CustContact
ContID CustID (Foreign Key) Name
1 1 Bill Gates
2 3 Sergey Brin

If the Customer ‘FlowHeater’ was a new customer on the READ side. How can I get the Customer.CustID value when I insert a new record into the customers table on the WRITE side?

Do you have an example you can point me too that shows how to update / insert with multiple tables on the Read AND Write side. Or can you suggest an approach to use.

Also. I have only been using FlowHeater for a few hours and it looks like it will be incredibly useful and certainly has a beautifully presented user interface.

Thank you for your time.
John
Request received by Email

Hi John,

To obtain values from MASTER table fields you have to use the FlowHeater default value property for this field like the screenshot below.
Here you can choice all fields from the MASTER table, or enter a custom value of course. In case the MASTER field is an “auto increment” field FlowHeater knows the new ID and use the right one.

There´s just one problem. To detect whether an insert or update should perform you need another criteria instead of “CustID” in the Master table! In the screenshot I switched the Primary Key manualy from “CustID” to “CustName”.