GoToRecord acNewRec not working in 2010

[nschroeder]

Greetings. I have a form that automatically creates a new record each day, the first time the form is opened that day. Code in the Form_Load event checks the date value in the last record of the table, and if it is less than the current date, it creates the new record. Otherwise, it stays on the last record. Here's the code:

Private Sub Form_Load()
    DoCmd.GoToRecord , , acLast
    If PODDate.Value < Date And _
       Weekday(Date) > 1 And Weekday(Date) < 7 Then ' No record yet today, and not a weekend
            DoCmd.GoToRecord , , acNewRec
            PODDate.Value = Date
    End If
End Sub

This has been working fine in Access 2003, but in 2010 the acNewRec command doesn't work. Stepping through the debugger, it correctly goes to the last record after the acLast command, but after the acNewRec, it still points to the last record (PODDate still contains yesterday's date), even though no error seems to have occurred. It then updates yesterday's record with today's date, which forces me to have to go into the table every day and fix it. When I step through the 2003 version, PODDate contains null after the acNewRec command, as it should.

We have mixed (2003/2010) users accessing the database, and either one could be the first to open the form. Any help would be appreciated.

 

[boblarson]

1. I don't have 2010 with me at the moment to test so I can't give a definitive yes/no about it working in 2010. But I have an alternative for you:

2. You can try using this instead:

 DoCmd.RunCommand acCmdRecordsGoToNew

3. For your Go to Last - is your form's record source based on a QUERY with the data sorted by that date?

 

[gemma-the-husky]

its most likely this

DoCmd.GoToRecord , , acLast

you need to look and see what aclast does - I suspect its probably not what you think!

 

[Trevor G]

its most likely this

DoCmd.GoToRecord , , acLast

you need to look and see what aclast does - I suspect its probably not what you think!

I have just tested this in 2010 I would suggest you use Bob's code as this does work.

Dave sorry to say this is correct in 2010 and 2007 to move to the last record
DoCmd.GoToRecord , , acLast


Look at this example

Private Sub Form_Load()
DoCmd.GoToRecord , , acLast
If me.StartDate.Value < Date Then
DoCmd.RunCommand acCmdRecordsGoToNew
me.StartDate.Value = Date
End If
End Sub

 

[gemma-the-husky]

what i meant was ...

access doesnt have an innate concept of a last record. (and i couldnt find an exact answer on a google search)

so what do you want to mean by last record?

last record phsically inserted?
last according to a primary key?
last according to a different key?
last in the current record set?

these may all be different, but are all diferent concepts

so when you get to the so-called last record, you may find it isnt the record you expect it to be, and your test may not work as you expect, and therefore the gotonewrecord code doesnt happen.

put a msgbox in, or a breakpoint and see exactly what your code is doing?

 

[boblarson]

Dave sorry to say this is correct in 2010 and 2007 to move to the last record
DoCmd.GoToRecord , , acLast

That acLast will take you to the last record in the recordset. If you use a table it is unpredictable as to which record it will really get. You need to have based the form's recordset on a QUERY which has an ORDER BY clause in it which sorts it so that you have the last record added in the proper place. Most likely the sort should be done on a date/time stamp and not on an autonumber field since we all know that autonumbers only guarantee a UNIQUE number and not necessarily in order.

 

[nschroeder]

It's a fine discussion about acLast, gentleman, and I appreciate the replies, but that's not the issue, as that part of the code is working fine. The problem is with acNewRec. Thanks for the suggestion about acCmdRecordsGoToNew, but unfortunately that has the same results -- It works in 2003 but not in 2010.

After doing some more testing, it appears that it has something to do with either the sequence of events or the timing of how/when they occur in 2010. Here's what I found:

In Access 2003, I put a debug stop on the DoCmd, acNewRec line. When I stepped forward from there, it jumped into the Form_Current event as expected. In 2010 with the same test, however, it just jumped directly to the next line. I tried the same thing with acCmdRecordsGoToNew and got the same results. HOWEVER, in both cases, when the form opened up it flashed briefly on the last record in the file, and then jumped to a new record, whereas in 2003 it opens up directly to the new record. This would probably be ok, except that the code has already updated the last (yesterday's) record with today's date, so new one for today ends up with a null value (it's not a user-updatable field on the form). Is there something I can do to force the DoCmd to complete before I execute the next line of code?

 

[gemma-the-husky]

i would put a breakpoint in the left margin by the docmd.goto

then use F8 to step through the code, and examine the programme

are you running an mdb in A2010, or have you updated to an accdb -the database format is different (jet and ace) and this may affect some things.

obviously something is happenming in these two lines. the only way you will find out is to do some more debugging. it may even be the .value qualifier

DoCmd.GoToRecord , , acLast
If me.StartDate.Value < Date Then

 

[nschroeder]

It is an mdb. I have already done as you suggested. As stated in my previous post, the problem is not with acLast. It appears that the acNewRec command doesn't occur immediately, as it does in 2003, but waits until the Form_Load event is complete, but by then my code has already updated the date in the wrong record.

 

[boblarson]

Can you post a sample of your database with any sensitive data turned into bogus data so we can test it?

 

[nschroeder]

Rather than upload the database, which is fairly substantial, I decided to create a small sample one. However, that one seems to be working correctly, so I'll do some more research and let you know if I find anything. Thanks much.

 

[boblarson]

Rather than upload the database, which is fairly substantial, I decided to create a small sample one. However, that one seems to be working correctly, so I'll do some more research and let you know if I find anything. Thanks much.

If the sample is working correctly and the other is not, perhaps you have some corruption creeping in. Try importing (not copy/paste) everything into a new, blank database shell and see if that helps.

 

[nschroeder]

Well, I got it working, even though I don't have an explanation for why it didn't work. I started by making a copy of the database, then stripping out everything unrelated to the issue -- all tables, queries, forms, reports, and modules, except for the two tables and forms (main form and subform), as well as any unrelated code and events. It still didn't work, so I made a test copy of the main form and deleted the subform from it. The problem went away. I then copied the subform from the original form to the test form and the problem did not come back with it.

Whatever. Thanks for your attention and help.

 

[boblarson]

probably a spot of corruption. It happens.