Copy Values from one record to the next on (1 Viewer)

alexsoft

New member
Local time
Today, 10:49
Joined
Feb 4, 2024
Messages
12
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

Active member
Local time
Today, 10:49
Joined
Jul 19, 2022
Messages
159
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

I’m here to help
Staff member
Local time
Today, 01:49
Joined
Oct 29, 2018
Messages
21,467
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

New member
Local time
Today, 10:49
Joined
Feb 4, 2024
Messages
12
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
 

Attachments

  • test.accdb
    560 KB · Views: 39

alexsoft

New member
Local time
Today, 10:49
Joined
Feb 4, 2024
Messages
12
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

New member
Local time
Today, 10:49
Joined
Feb 4, 2024
Messages
12
On my table i have more fields than the test i sent you. My only problem is that.
 

tvanstiphout

Active member
Local time
Today, 01:49
Joined
Jan 22, 2016
Messages
220
1707071382543.png

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

New member
Local time
Today, 10:49
Joined
Feb 4, 2024
Messages
12
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

Enthusiastic Amateur
Local time
Today, 09:49
Joined
Sep 21, 2011
Messages
14,253
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

New member
Local time
Today, 10:49
Joined
Feb 4, 2024
Messages
12
i want the value 600 to go to next one but on the place i had 450 before

450​
600​
600​
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:49
Joined
Feb 19, 2002
Messages
43,257
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

Well-known member
Local time
Today, 10:49
Joined
Feb 7, 2020
Messages
1,942
Simple query solution
SQL:
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

Super Moderator
Staff member
Local time
Today, 04:49
Joined
Feb 19, 2002
Messages
43,257
@ebs17 No one is arguing against the simple query solution except the OP;) Thanks for posting it though.
 

XPS35

Active member
Local time
Today, 10:49
Joined
Jul 19, 2022
Messages
159
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

..forever waiting... waiting for jellybean!
Local time
Today, 16:49
Joined
May 7, 2009
Messages
19,230
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).
 

Attachments

  • test (2).accdb
    940 KB · Views: 32
Last edited:

alexsoft

New member
Local time
Today, 10:49
Joined
Feb 4, 2024
Messages
12
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.

1707117510687.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:49
Joined
May 7, 2009
Messages
19,230
you need to enter some values to "present value" first.
then the "previous value" will appear.
 

Attachments

  • Test.accdb
    512 KB · Views: 33

alexsoft

New member
Local time
Today, 10:49
Joined
Feb 4, 2024
Messages
12
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.
 

ebs17

Well-known member
Local time
Today, 10:49
Joined
Feb 7, 2020
Messages
1,942
@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.
 

Users who are viewing this thread

Top Bottom