How to prevent fiugres from changing

[Guest]

I have a form & subform that display item cost, quantity, sale price, profit, total etc. Items and prices are listed in the tblEquipment table. If the price of an item changes, all the calculations naturally change to reflect that.

Projects (from tblProject) are given a status (from tblStatus) e.g. estimated, underway, invoiced, completed. Once a project is completed I need all figures for that project to remain unchanged, even though prices from tblEquipment for other projects might change afterwards.

I think need to commit the figures to a different table at some point. Does anyone have any suggestions for the best way to approach this?

Thanks

Andrew

 

[Ken Snell]

This is one of the "less than often" occasions where storing a data value in
more than one table is good to do. What you want to do is to store the item
cost, sale price, etc. (the ones that may change later) values as fields in
the records for each item when you've "closed" the project so that no
further changes are to occur.

--

Ken Snell
<MS ACCESS MVP>

I have a form & subform that display item cost, quantity, sale price,

profit, total etc. Items and prices are listed in the tblEquipment table.
If the price of an item changes, all the calculations naturally change to
reflect that.

Projects (from tblProject) are given a status (from tblStatus) e.g.

estimated, underway, invoiced, completed. Once a project is completed I
need all figures for that project to remain unchanged, even though prices
from tblEquipment for other projects might change afterwards.

I think need to commit the figures to a different table at some point.

Does anyone have any suggestions for the best way to approach this?

 

[Guest]

Thanks Ken

Is this the way to go?

I trigger some code when the status changes to completed. This inserts the current values in a different table and changes the subform's record source to that table.

I could use an INSERT INTO SQL statement to enter the values into the table and the RecordSource Property in VBA to change the recordset to the other table.

Any comments or suggestions would be much appreciated.

Thanks

Andrew

 

[Ken Snell]

I would probably use the INSERT INTO query to put the data in the other
table. I'm not sure why you're having to change the RecordSource of the
form, though.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken

Is this the way to go?

I trigger some code when the status changes to completed. This inserts

the current values in a different table and changes the subform's record
source to that table.

I could use an INSERT INTO SQL statement to enter the values into the

table and the RecordSource Property in VBA to change the recordset to the
other table.

 

[Ken Snell]

Using the append query "fixes" the prices into your target table. Still
don't know why the subform needs to have its recordsource changed unless,
when the form and subform are next opened, you plan to have them show data
from this "fixed" table? But to do that you'd need to save the design
changes to your form and subform when you close them, and that would mean
that the form and subform won't point to the other table for "new" projects.

So, I think I'm not understanding what your goal is. But the append query, I
believe, is all you need to do.

--

Ken Snell
<MS ACCESS MVP>

I need to change the recordsource (at least I think I do) because until

the project is closed, the subform needs to reflect up to date prices. It
is only once the project is marked completed that the prices need to be
fixed.

 

[Guest]

Thanks Ken

What I am trying to do is exactly what you say - I need to show the data from the fixed table for completed projects and show the data from the other table for new and uncompleted projects.

If I can't change the RecordSource dynamically (on the form_current event?) then could I have 2 identical subforms with different recordsources and use code to swap between them according to Project Status?

Andrew

Andrew

 

[Ken Snell]

No need for different subforms. For what you're describing, setting the
recordsource in the OnLoad event of the subform (based on whether the
project is done or new/incomplete) is valid to do. You'll just need a way to
know which is which.

If you're going to switch from a completed project to a new project while
the form and subform remain open, then you'll also need code in another
event (click of a button? on current and test for new record? etc.) to make
the recordsource change in the subform "on the fly".

I was just trying to understand the need for changing the recordsource.

When I design such systems, I don't use two separate tables for these
values. I create records for the project when it starts, and those become
the permanent values/records. If I still need to update prices, etc., then I
run code in the OnLoad event of the form to refresh the values with the
current values.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken

What I am trying to do is exactly what you say - I need to show the data

from the fixed table for completed projects and show the data from the other
table for new and uncompleted projects.

If I can't change the RecordSource dynamically (on the form_current

event?) then could I have 2 identical subforms with different recordsources
and use code to swap between them according to Project Status?