Results 1 to 7 of 7
  1. #1
    NbdyFamous is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2018
    Posts
    3

    Unhappy New Record randomly gets posted on top of the table instead of at the bottom

    Hi,

    I'm a beginner with access and thanks to help from this forum and other sources on the internet i was able to develop a program for my company that tracks current tasks and displays them on a weekly basis.

    For this I've created a a new record for every week and have coded it so that everytime the form loads, it checks if the current week is the same as on the form, if not it creates a new form with the current week.
    The code is as follows:

    Private Sub Form_Load()
    DoCmd.RunCommand acCmdRecordsGoToLast

    If Text212.Value <> Format(VBA.Date, "ww", vbSunday, vbFirstFourDays) And IsNull(Text212) = False Then Command228.SetFocus
    SendKeys "{Enter}"



    End Sub

    This was working perfectly fine till about last week when Access started creating new records at the beginning rather than the end. So when the command DoCmd.RunCommand acCmdRecordsGoToLast runs, the system doesn't realise that the new record is created at the beginning and creates another duplicate record (again at the beginning) and the whole thing repeats everytime the form loads. In the Image below you can see 1817 doe weeks 17 of the year 2018 is created at the top instead of the bottom as it was doing before.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	16.3 KB 
ID:	33676

    Is there any reason why this error is randomly showing in a system that was working fine all this while. Also can you help me fix the issue.

    Thanks a Ton

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Up to WeekNo 10, the date is for successive Saturdays
    From then on the days vary -Fri/Sun/Mon/Tue so the interval isn't every 7th day
    WeekNo 15/16 are for the same date

    You need to fix the incorrect dates & fix the code used to create those dates as it is faulty
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    NbdyFamous is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2018
    Posts
    3
    Dont look at the DateTemp column, that is not part of any code. It is just the date manually entered whenever the record was last modified.

    the code doesnt work on 7 day intervals. it just checks if the WeekNo of the latest record is the current week number as per system date. If its not the same then a new record is created.

    I just need to know why the new record is being created at the top instead of the bottom.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As far as I understand you, it still doesn't work correctly or you wouldn't have the same date for weeks 15 & 16.
    Also there is no date on either record for week 17

    As the dates are in ascending order, the null values appear at the top

    Is the screenshot showing a table or a query?
    If it's a table there appears to be no primary key field as the first two fields contain duplicates & the date field contains null values
    If that's the case, you need to set a PK field - probably SNo as that is YearNo & WeekNo

    If you are showing a query then you need review the SQL to prevent null values & duplicates
    You should also sort using field SNo (or Week No) not DateTemp
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    NbdyFamous is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2018
    Posts
    3
    "As far as I understand you, it still doesn't work correctly or you wouldn't have the same date for weeks 15 & 16.
    Also there is no date on either record for week 17"

    The date column displayed in the screenshot is for the user to manually enter the date when the record was last modified. weeks 15 and 16 had some corrections and they were made on the same date, which is why they have the same dates. I assure you, that date column has nothing to do with the code.

    what i had send was the screenshot of the table.

    Thank you for the PK suggestion. That will probably help with the duplicates being created. I will check the same and let you know if that fixes the issue.

    Thanks Again

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The date column displayed in the screenshot is for the user to manually enter the date when the record was last modified.
    So why are any records created with no DateTemp field.
    I understand that the date field is irrelevant but, from your description, it should still contain something.
    And what is the point of having a week no field if it isn't related to the week number?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    when Access started creating new records at the beginning rather than the end. So when the command DoCmd.RunCommand acCmdRecordsGoToLast runs
    To answer your question about records appearing at the beginning. The fact they have appear at the end in the past is a coincidence. Access (any db) stores data randomly and has zero concept of 'last' without an order. Further, when running a query, the data will be returned in a potentially random order depending on the nature of the query - criteria etc.

    The solution is to set an order to the recordset (best to do this in the underlying query to your form) - might be a pk (depends on your requirement but is not 100% reliable for sorting, particularly in a multi user environment) might be a date (but two records with the same date will be returned randomly), or perhaps a timestamp which should be sufficient for manually entered data.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-09-2017, 08:48 AM
  2. Replies: 5
    Last Post: 02-10-2015, 08:26 PM
  3. Replies: 5
    Last Post: 01-05-2015, 03:52 PM
  4. Replies: 2
    Last Post: 12-09-2014, 02:14 AM
  5. Replies: 3
    Last Post: 11-24-2010, 06:33 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums