Table update

I have just realised that I have a problem with the way I set up my
master/detail form and table.

I used the following code in the "after update" of the column that items are
being selected from in the subform:

Private Sub d_itemid_AfterUpdate()

With Me.d_itemid
If Not IsNull(.Value) Then
Me.d_cost = .Column(3)
Me.d_eskpurch = .Column(5)
End If
End With

End Sub

Problem is, now when costs are updated in the product table it does not
automatically change on the already created entries in the detail table. It
only updates when the items are re-selected in the form.

I'm not very knowledgable wrt Access, so can anyone help me with this.
I don't want to change the tables as there are already thousands of entries
on the various tables in this database

Thanks in advance

On Mon, 25 Aug 2008 09:04:03 -0700, Jacques Steinman
wrote:

I have just realised that I have a problem with the way I set up my
master/detail form and table.

I used the following code in the "after update" of the column that items are
being selected from in the subform:

Private Sub d_itemid_AfterUpdate()

With Me.d_itemid
If Not IsNull(.Value) Then
Me.d_cost = .Column(3)
Me.d_eskpurch = .Column(5)
End If
End With

End Sub

Problem is, now when costs are updated in the product table it does not
automatically change on the already created entries in the detail table. It
only updates when the items are re-selected in the form.

I'm not very knowledgable wrt Access, so can anyone help me with this.
I don't want to change the tables as there are already thousands of entries
on the various tables in this database

Thanks in advance

Are you sure that you WANT to change them!? In a sales database you typically
will want to record the cost of the item *as of the time that it was
purchased*; if the cost later changes, you would not want to retroactively
change previous records.

What are the tables and fields involved? If you want the value to reflect the
current Item table, perhaps your d_cost and d_eskpurch fields should simply
not exist in the table; instead you can use a query to dynamically display
them.
--

John W. Vinson [MVP]

Yes, They have to be dynamic. I never took this into account when I started.
This is not an invoice generating form / system. The form creates a structure
record with the structure composing multiple items, and the record being used
whenever the structure is ordered.

The structure is then viewed, filtered and printed in another form. This is
where the correct cost counts. And this is where the cost is displayed as
seen in the table.

I know there is a fairly easy answer to this, I just don't what it is.



"John W. Vinson" wrote:

On Mon, 25 Aug 2008 09:04:03 -0700, Jacques Steinman
wrote:

I have just realised that I have a problem with the way I set up my
master/detail form and table.

I used the following code in the "after update" of the column that items are
being selected from in the subform:

Private Sub d_itemid_AfterUpdate()

With Me.d_itemid
If Not IsNull(.Value) Then
Me.d_cost = .Column(3)
Me.d_eskpurch = .Column(5)
End If
End With

End Sub

Problem is, now when costs are updated in the product table it does not
automatically change on the already created entries in the detail table. It
only updates when the items are re-selected in the form.

I'm not very knowledgable wrt Access, so can anyone help me with this.
I don't want to change the tables as there are already thousands of entries
on the various tables in this database

Thanks in advance

Are you sure that you WANT to change them!? In a sales database you typically
will want to record the cost of the item *as of the time that it was
purchased*; if the cost later changes, you would not want to retroactively
change previous records.

What are the tables and fields involved? If you want the value to reflect the
current Item table, perhaps your d_cost and d_eskpurch fields should simply
not exist in the table; instead you can use a query to dynamically display
them.
--

John W. Vinson [MVP]