I think I want to use the Set Field script step. Here is what I want to do. It's really very simple.

I've got table [Join], which has the following fields:

[WellId]

[OwnerId]

[interestId]

[interestType]

[ProductionMonth]

[Year]

In a related table, [Owners], are the following fields:

[GasPrice]

[TaxRate]

In another related table, [Wells], is field [McfId]

My layout, "Data Entry," is based on table [Join]. I have value lists to populate all of the fields, and that works great, but I need to automate... (rhyme not intentional )

Here is what I want to do with layout "Data Entry"

0. Click a button to create a new record in table [Join]

1. Click the [WellId] field. A drop down list appears.

2. Choose a value.

3. Based on the values of the last [Join] table record where the value of [WellId] was the same as that selected in #2, the following fields are populated:

[Join].[WellId]

[Join].[OwnerId]

[Join].[interestId]

[Join].[interestType]

[Join].[Year]

[Owners].[GasPrice]

[Owners].[TaxRate]

4. Enter a value for [Join].[ProductionMonth]

-----------------------------

The two other tables, [Wells] and [Owners], both have one to many relationships with table [Join], where [Join] is on the one side, and records can be added both ways for all three tables.

Here is what I'm worried about: I don't want to create new records in tables [Wells] and [Owners] when I touch fields [Owners].[GasPrice], [Owners].[TaxRate], and [Wells].[McfId]. I want to create new records in table [Join], but not in tables [Owners] and [Wells]. I just want to set values in existing records in the realted tables, not create new records.

Please let me clarify if necessary. My database is here . In the referenced version, not all of the fields work on layout "Data Entry," but my explanation provides what I want it to do. I just had to get something out, so I could get help on it. I'll make all of the fields work on layout "Data Entry" if you have to have that. I just didn't go back and make all of the fields work, because I want them all to be automated in the end anyway.

THANKS! ! ! ! !

There is no Data Entry layout so I assumed you meant the Join layout. See if the attached revision is what you are after.

Be sure to look up the Last function in the Help File. I'm not sure how things will react if one of the fields was left blank in the last record with the same WellID.

Copy_of_Royalties_Revised.zip