Form with subform

[Guest]

Hi,

I have a main form called "Catalog Pricing" with the parent field "cat#".
On the main form is a subform called "Repricing Subform" with the parent of
"cat#" and child of "item#". I would like to automatically update a bound
control on the subform called "Base Cost" for all item#s with the same cat#.
I want the change to affect only the open subform in the underlying table
which is "tbl_Repricing". Can you tell me the easiest way to do this? You're
help is greatly appreciated!

 

[John Vinson]

Hi,

I have a main form called "Catalog Pricing" with the parent field "cat#".
On the main form is a subform called "Repricing Subform" with the parent of
"cat#" and child of "item#". I would like to automatically update a bound
control on the subform called "Base Cost" for all item#s with the same cat#.
I want the change to affect only the open subform in the underlying table
which is "tbl_Repricing". Can you tell me the easiest way to do this? You're
help is greatly appreciated!

Are you trying to store the Base Cost redundantly? If it depends only
upon the value of the Item#, then it should probably NOT be stored in
the child table.

If, on the other hand, you want to capture the base cost as of a
moment of time, and have it stay fixed in the child table after it's
been selected even if it changes in the main table, you can use the
BeforeUpdate event of each subform record to "push" the cost into the
control:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any record validation code goes first>
<if the record is valid...>
Me!txtBaseCost = DLookUp("[Base Cost]", "[tablename]", "Cat#] = " _
& Me!txtCatNumber)
<error trapping code>
End Sub


John W. Vinson[MVP]