Retrieving Value from a Field in a Previous Record

[Margah]

Hello All,

I am trying to Get a value from a Field in a Previous record. Could someone please give me a boost in the right direction. Not looking for a complete solution but a Nudge in the right direction..

Thanks Margah

 

[RoyVidar]

That would depend upon how you define "a previous record".

Is it the previous record according to the current sort order of the displayed records, is it the last added record...

One method of "carrying over" values from one record to a new record, is to place the values of the controls in the current record to the controls defaultvalue property before leaving. In the before update event of the form, in a save button...

[tt]me!txtControl.DefaultValue = me!txtControl.Value[/tt]

Else, you could retrieve the forms recordset (clone) and navigate to what would currently be "a previous record", and fetch the value.

[tt]dim rs as dao.recordset
set rs=me.recordsetclone
' use appropriate method...
rs.findfirst "id = " & somevalue
rs.movelast ' ....
me!txtControl.Value = rs!YourField[/tt]

From your description, I'm not sure whether this is in the right direction, but post back...

Roy-Vidar

 

[Margah]

Sorry bout that it will be the last Added Record.

 

[Margah]

Ok I am going to need a little more help.

Trying to pull a value from a field in the last record saved.

 

[RoyVidar]

Well, the most popular version, is my first suggestion, using the default value property of the control before/or whilst the "previous" record is saved. This will provide the previous record controls value as default value for the new record.

Some like also to store these in tables, and assign at form load, here are some threads on that:
thread702-772209, thread702-793961

Since Access or relational databases don't know what the previous record is, if you're going to "pull" from it, you'll need to know/let Access know what that is by storing/keeping a value for later use.

Either the value from the control, or the value from the primary key of the record, and use some of the other mentioned methods for retrieval.

Roy-Vidar

 

[pvsmith5]

I have been trying to do almost the same thing as the question asks, but here's what I'm trying to do:

Here's what the table looks like:
Year1 Year2 Year3 Year4 Forecast
Record 1 1 2 3 4
Record 2 5 6 7 8

What I want to do is do a recordset line to accomplish this formula:

[(Record2(year2)+Record2(year3)+Record2(year4)/ Record1(year1)+ Record1(year2)+record1(year3)] Times (Record1)year4 = Record2(forecast)

Do I need to use a Bookmark property or can I do this as you've described earlier?

I sure would appreciate your help. Thank you all.

Preston

 

[pvsmith5]

I need help on trying to do something like has been discussed, but beyond it. I have about 15 tables that are structured like this:

Year1 Year2 Year3 Year4 Forecast
Record 1 1 2 3 4
Record 2 5 6 7 8

I want to calculate the following formula:
[Year2(6)+ Year3(7) + Year4(8) / Year1(1)+ Year2(2)+ Year3(3)] * Year4(4)= Record2(Forecast)

Do you have any ideas on how to do this? Do I have to set a bookmark on Record1? I want to do the same type of calculation using Record2 and Record3 when it loops. Then I want to take the forecast numbers for each record and all 15 tables, and roll them up into one table.
HELP!!!!!!!

 

[Golom]

I assume that you don't really have fields that contain "Record 1" and/or "Record 2" as values so how does your system tell that the "1, 2, 3, 4" record is record 1 and "5, 6, 7, 8" is record 2?

 

[pvsmith5]

No, I just put Record1/Record2 so you'd know how the table was set up. The way the system now knows what record it is on is with a counter. But I have to be able to access the data in the previous record and apply that calculation to go into the forecast field. That's the problem.