Copy Values from one record to the next on

[alexsoft]

hI, i Am new here so i try to explain my problem. i have a table with 2 fields. f1 and f2. i want when i press enter for the next record to copy the value of f1 to the value f2 of the new record

 

[XPS35]

What's the point of having a table with duplicate records? Please tell us more about the background of this question so we can better assist you.

 

[theDBguy]

One rule of database normalization is to not store the same information multiple times. Can you explain exactly what it is you're trying to accomplish by duplicating the data?

 

[alexsoft]

ok. Here is the situation. in a table i have 2 fields in is a previous reading and the present one is the reading of a meter,
i want to press enter and on the new record to have automatic the present value of the record previous record to the present value of the new record.
for example. on the first record i have previous value 1200 and the present value 1400. When i press enter i want the value 1400 to show on the previous field

 

[alexsoft]

These reading happed once every three months. i have a person who goes to the water reading meter and tell me that the new reading for the reader is lets say 1400. when i enter this value i calculate the amount of money the person have to pay according to his/her readings. Three monts later i get the new reading. So i want the value 1400 to go the next record automatic but to the place of the previous. i dont want to type it in again.

 

[alexsoft]

On my table i have more fields than the test i sent you. My only problem is that.

 

[tvanstiphout]

The classic way is NOT to redundantly store the previous value, but simply have a list of readings taken at certain locations on certain dates.
You can then use a query to calculate the delta between last time paid (not shown in my screenshot), and today.

 

[alexsoft]

Thank u. But i know how to do it with a query, i dont want to execute a query to do it. I want after i press enter to go to the new record automatic to get the above value to the new record. I also managed when i press the enter key to get the previous value of the old record to the previous of the new one. The broblem is how to get the present value of the previous record to the previous value of the new record.

 

[Gasman]

ok. Here is the situation. in a table i have 2 fields in is a previous reading and the present one is the reading of a meter,
i want to press enter and on the new record to have automatic the present value of the record previous record to the present value of the new record.
for example. on the first record i have previous value 1200 and the present value 1400. When i press enter i want the value 1400 to show on the previous field

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

 

[alexsoft]

i want the value 600 to go to next one but on the place i had 450 before

450​	600​
600​

 

[Pat Hartman]

Tom is correct as are the others. Let me go a little further into why storing this particular calculated data is poor practice. What happens if you make a mistake and miss entering a month? Now you have a minimum of two records to change. If you don't notice it for a while, you may have a bunch of records that all need to change.

However, I will load the bullets into your gun. You would never use the enter key for this purpose. You would need code in the form's BeforeUpdate event so the populating of the "fifth" field wouldn't happen until the record gets saved. Two columns doesn't make any sense.
The table should be:
ReadingID (autonumber FK)
MeterID (FK to the meter table so you know what meter you are entering data for)
ReadingDT
CurrentReading
PrevReading

The user enters the meterID, the ReadingDT, and the CurrentReading. When you save the record, your code looks at the most recent reading for the meter and picks up the CurrentReading value from that row by opening a recordset based on a query that returns the Top 1 record and sorts descending by ReadingDT with a Where value for the entered MeterID and copies it to the PrevReading on the current record. You have to allow for no record to be found so the code will work for the first reading on a meter.

The gotcha's. Your code also must verify that there is no reading missing between the previous date and the current date. So, if there is a missing reading, it won't let you add the reading for the date you entered because if it does, the PrevReading value will be incorrect.

 

[ebs17]

Simple query solution

SELECT
   T1.ID,
   T2.XValue AS PrevXValue,
   T1.XValue
FROM
   test AS T1
      LEFT JOIN test AS T2
      ON T1.XValue = T2.XValue + 1

 

[Pat Hartman]

@ebs17 No one is arguing against the simple query solution except the OP Thanks for posting it though.

 

[XPS35]

hI, i Am new here so i try to explain my problem. i have a table with 2 fields. f1 and f2. i want when i press enter for the next record to copy the value of f1 to the value f2 of the new record

Sorry, I didn't read your question completely. Nevertheless, I agree with the others that it is not necessary to record the previous value. After all, it is in the previous record and therefore you do not have to record it again. Make sure you have a field in your table that allows you to determine the order of the recorded values.

 

[arnelgp]

here is another possible solution, using Data Macro.
first I created a Query, TestQ that will sort your data by ID Descending.
next, I modify Test table and add Macro (After Update event).


you only need to Fill-up the Present Value field on your table and the previous will be written for you (after pressing enter key).

 

[alexsoft]

I created a form so you can see my problem. Actually this form i sent is is a subform. so if you open the form test you gonna see three records. I want when i press the enter key on the last record the value of the present value to go to the previous value of the new record.
Dont buther about the code. As i said this is a part of a bigger program.

 

[arnelgp]

you need to enter some values to "present value" first.
then the "previous value" will appear.

 

[alexsoft]

No. I am on the second record of my form. At the end of the record i press ENTER. i want that time (when i press the enter key the value of this record to appear on the value of the next record.

 

[GPGeorge]

No. I am on the second record of my form. At the end of the record i press ENTER. i want that time (when i press the enter key the value of this record to appear on the value of the next record.

Maybe this will help with communication.

 

[ebs17]

@alexsoft
For the move towards realism: Do you have exactly one meter and create a database for it?
With an Excel table, the learning curve would be a little lower - and almost everyone can do Excel.