From data is NOT appended in Table (1 Viewer)

Vishalonnet

New member
Local time
Tomorrow, 03:06
Joined
Aug 6, 2019
Messages
8
Hello

This is my first post in this forum. I am facing a big problem... I am trying to save to records in Access 2007 table using Form in and I successfully completed this task, but Record is NOT added at last, they are added before last record which was saved previously.

What I understand that Records are NOT appended they are inserted before last record.

How can I overcome from this. Kindly guide me. For reference I attached few screenshots.

Thanking you all in advance
 

Attachments

  • Screenshot (1).png
    Screenshot (1).png
    57.5 KB · Views: 96
  • Screenshot (2).png
    Screenshot (2).png
    49.5 KB · Views: 89
  • Screenshot (3).jpg
    Screenshot (3).jpg
    93.5 KB · Views: 83

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:36
Joined
May 7, 2009
Messages
19,169
add an AutoNumber field to your table.
records are appended at the End of the table.
which means they go to the last of record.
if you have autonumber field there, you can sort your data
on this field in descending order.
 

Vishalonnet

New member
Local time
Tomorrow, 03:06
Joined
Aug 6, 2019
Messages
8
add an AutoNumber field to your table.
records are appended at the End of the table.
which means they go to the last of record.
if you have autonumber field there, you can sort your data
on this field in descending order.

Thank you Sir for considering my problem but I don't have AutoNumber field in my table. In this situation what can be be the solution?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:36
Joined
May 7, 2009
Messages
19,169
you bring your table in design view and add one.
 

Vishalonnet

New member
Local time
Tomorrow, 03:06
Joined
Aug 6, 2019
Messages
8
you bring your table in design view and add one.

Sir I understood what you are guiding me to do... You are saying to add AutoNumber field in table But I like to know what is the alternate solution without AutoNumber field???
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:36
Joined
May 7, 2009
Messages
19,169
there is no alternative. you are storing only names and surnames there. anyone can have same names and surnames. autonumber is your unique id. same as people we same name but have different social number. autonumber will serve you that. it may not be apparent now, but in the future you will realise that.
 

Vishalonnet

New member
Local time
Tomorrow, 03:06
Joined
Aug 6, 2019
Messages
8
there is no alternative. you are storing only names and surnames there. anyone can have same names and surnames. autonumber is your unique id. same as people we same name but have different social number. autonumber will serve you that. it may not be apparent now, but in the future you will realise that.

Thank you Very Much Sir for your guidance and explanation. I will follow it
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:36
Joined
Feb 19, 2002
Messages
42,976
Code:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.Net_Qty = Me.Rev_Qty - Me.Rej_Qty
    Me.Tot.Amt = Me.Net_Qty * Me.Rate
End Sub

1. ALWAYS add Option Explicit in the header of code modules. This is a database setting so you don't have to remember to do it yourself. However, changing the setting will not affect any existing modules so you will have to manually fix those yourself. Option Explicit tells Access that EVERY variable must be defined before it can be referenced. This keeps you from accidentally creating variables due to typos. These errors are subtle and can take months before your users find them.
2. For starters, you should NOT be storing calculated values in the table. These calculations should be done in the queries so they are always fresh. I'm not going to go into all the reasons. This is not just my opinion, this is the recommendation of the people who created the relational database back in the 70's and expert opinion has not changed in 50 years. There are reasons. Lecture over. To do this at all, you need to get your code into the correct event. I moved it to the Form's BeforeUpdate event. That is the last event to run before the record is saved and cannot be bypassed. So, if the record is dirtied, it will be saved, and this code in the BeforeUpdate event WILL run. If you put the calculations in the form's RecordSource query, the values in the form will automatically show up as the underlying data fields are added/changed. Using the BeforeUpdate method, you will not see the results of the calculation until after the record is saved.
3. I agree with arne's recommendation to add an autonumber. This is yet another "rule" created by those pesky experts 50 years ago and no one has come up with a better idea. ALL tables should have a primary key. If you don't have a natural key, use an autonumber. But, the missing autonumber isn't necessarily the cause of your problem.
4. Your explanation is confusing. It sounds like you are saying that the calculated values get saved but they get saved to the wrong records. If that is the case, my suggested change to which procedure to use will resolve that.

PS, in the future, when you post code, please post text strings. Do NOT post pictures. If we want to help you, it means WE need to RETYPE the code and we shouldn't have to do that to help you.
 

Vishalonnet

New member
Local time
Tomorrow, 03:06
Joined
Aug 6, 2019
Messages
8
Code:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.Net_Qty = Me.Rev_Qty - Me.Rej_Qty
    Me.Tot.Amt = Me.Net_Qty * Me.Rate
End Sub

1. ALWAYS add Option Explicit in the header of code modules. This is a database setting so you don't have to remember to do it yourself. However, changing the setting will not affect any existing modules so you will have to manually fix those yourself. Option Explicit tells Access that EVERY variable must be defined before it can be referenced. This keeps you from accidentally creating variables due to typos. These errors are subtle and can take months before your users find them.
2. For starters, you should NOT be storing calculated values in the table. These calculations should be done in the queries so they are always fresh. I'm not going to go into all the reasons. This is not just my opinion, this is the recommendation of the people who created the relational database back in the 70's and expert opinion has not changed in 50 years. There are reasons. Lecture over. To do this at all, you need to get your code into the correct event. I moved it to the Form's BeforeUpdate event. That is the last event to run before the record is saved and cannot be bypassed. So, if the record is dirtied, it will be saved, and this code in the BeforeUpdate event WILL run. If you put the calculations in the form's RecordSource query, the values in the form will automatically show up as the underlying data fields are added/changed. Using the BeforeUpdate method, you will not see the results of the calculation until after the record is saved.
3. I agree with arne's recommendation to add an autonumber. This is yet another "rule" created by those pesky experts 50 years ago and no one has come up with a better idea. ALL tables should have a primary key. If you don't have a natural key, use an autonumber. But, the missing autonumber isn't necessarily the cause of your problem.
4. Your explanation is confusing. It sounds like you are saying that the calculated values get saved but they get saved to the wrong records. If that is the case, my suggested change to which procedure to use will resolve that.

PS, in the future, when you post code, please post text strings. Do NOT post pictures. If we want to help you, it means WE need to RETYPE the code and we shouldn't have to do that to help you.

Dear Pat Sir
Very much thankful for your detail explanation and guidance. I made the changes.
Sorry for confusing query kindly refer the image of problem to understand the problem.
Thank You
 

Attachments

  • Screenshot (8).jpg
    Screenshot (8).jpg
    96.5 KB · Views: 92
  • Screenshot (7).jpg
    Screenshot (7).jpg
    100.4 KB · Views: 92

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:36
Joined
May 7, 2009
Messages
19,169
if you added the autonumber field, you can sort it on that field in ascending order, then you'll get the correct sequence of records.

ehem, Pat is a woman.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:36
Joined
Sep 21, 2011
Messages
14,047
If you are heart set on NOT having an autonumber, then add the time element to your M_date and order on that?
 

Vishalonnet

New member
Local time
Tomorrow, 03:06
Joined
Aug 6, 2019
Messages
8
if you added the autonumber field, you can sort it on that field in ascending order, then you'll get the correct sequence of records.

ehem, Pat is a woman.

Even after Adding AutoNumber field facing same problem NO improvement.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:36
Joined
May 7, 2009
Messages
19,169
then manually copy the record you want to be in last row, to a new record and delete the previous record. everything else will be in order when you use the form.
 

JHB

Have been here a while
Local time
Today, 22:36
Joined
Jun 17, 2012
Messages
7,732
Even after Adding AutoNumber field facing same problem NO improvement.
Do you've a Primary/ Index or sortorder set on your table?
Show a print screen of your table structure, or post your database?
 

Vishalonnet

New member
Local time
Tomorrow, 03:06
Joined
Aug 6, 2019
Messages
8
Do you've a Primary/ Index or sortorder set on your table?
Show a print screen of your table structure, or post your database?

Thank You I forgot to make Indexed property as Yes(No Duplicate)

Now its working fine Thank You very much.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:36
Joined
Feb 19, 2002
Messages
42,976
I know you think you've solved your problem but please review my explanation again. The ONLY way to predict order in a recordset is to SORT it!!!!!!
 

Users who are viewing this thread

Top Bottom