2nd GoToRecord doesn't work

[nschroeder]

I'm creating a form tied to a table with only a date in it. The subform's table has the date and a filename as the PK (multiple files per day). The Form_Load event goes to the last record and checks the date. If it is < today's date, it creates a new record with the current date, so the first time they open the form each day, it creates a new daily record. Here's the code:

Private Sub Form_Load()
    DoCmd.GoToRecord , , acLast
    If FileDate.Value < Date Then ' Generate new record for today
            Me.AllowAdditions = True
            DoCmd.GoToRecord , , acNewRec
            Me.FileDate = Date
            Me.Dirty = False
    End If
    Me.AllowAdditions = False ' Only allowed through above code
End Sub

The problem is that it doesn't successfully perform the 2nd GoToRecord, and as a result, updates yesterday's record with today's date. I've toyed with this in debugger, and it's consistent no matter what the acRecordType. For example, if I switch them and go to acNewRec first, it will do so, but then won't go to acLast. I've determined that it's related to the form/subform situation, because if I remove the subform, then it works fine. I copied the code from another db where it's working fine also, but again, no subform there. What is it about the subform that could be causing this?

Thanks for any help!

 

[bob fitz]

Perhaps you need a different event. Try OnOpen or OnCurrent

 

[nschroeder]

OnOpen gets the same results. OnCurrent wouldn't apply here.

 

[static]

I'm surprised it runs at all seeing as you have one if and 2 end if's.
Also, why move twice? Do one thing or the other.

Private Sub Form_Load()    
    If FileDate.Value < Date Then 
        ' Generate new record for today
            Me.AllowAdditions = True
            DoCmd.GoToRecord , , acNewRec
            Me.FileDate = Date
            Me.Dirty = False
    else
            DoCmd.GoToRecord , , acLast
    End If
    Me.AllowAdditions = False ' Only allowed through above code
End Sub

 

[nschroeder]

I had cut out an irrelevant IF, but failed to cut the matching End If. I fixed it above. Sorry for the confusion.

If you read my first paragraph, you will see why it is needed, so I won't repeat.

 

[static]

I read all of your paragraphs. Did you try my code? Did it work?

 

[gemma-the-husky]

instead of going to the last record, just do a dmax, to find the highest date, then do what you need based on that.

 

[missinglinq]

Just to be clear...this code is in the Main Form?

Linq ;0)>

 

[nschroeder]

Static: FileDate doesn't have a value until you've moved to the last record to get it.

Missinglinq: Yes, that's correct.

Gemma: I tried If DMax([FileDate], "FileDates") < Date, and the DMax function returned 2.34962406015038E-04, which if formatted equates to 12/30/1889. DMin returned the same result. The values in FileDate are 9/19/2016 through 9/27/2016. What am I doing wrong?

 

[nschroeder]

I don't know how things work internally, but I suspect that with the presence of a subform, the system needs to resolve the first GoToRecord in both forms before it will allow another one. Is there a way to force that to happen?

 

[static]

Try this - you'll need to change the table name from tblDates to your own table

Private Sub Form_Load()
    DoCmd.GoToRecord , , acLast
End Sub

Private Sub Form_Open(Cancel As Integer)
    On Error Resume Next
    dt = DBEngine(0)(0).OpenRecordset("select FileDate from tblDates where FileDate=#" & Format(Date, "mm/dd/yyyy") & "#")(0)
    Select Case Err.Number
    Case 3021
        DBEngine(0)(0).Execute "insert into tblDates values (#" & Format(Date, "mm/dd/yyyy") & "#)"
    Case Else
        Debug.Print Err.Description
    End Select
End Sub

If that doesn't work you'll probably need to upload a sample database for people to look at.

I doubt that you really need this dates table though.

If you are filtering a subform from the value of FileDate, you can do the same thing from an unbound control.

And you can get all of the dates keyed from your main table

currentdb.openrecordset "select distinct FileDate from YourFilesTable"

 

[missinglinq]

Just to be clear...this code is in the Main Form?

Missinglinq: Yes, that's correct.

Then, in v2007, I can't reproduce your problem...it works just as it should!

I agree with static...you need to zip up and attach your file for us to play with; apparently there's something involved that you haven't mentioned/aren't aware of.

Linq ;0)>

 

[nschroeder]

I'm on 2013, but I tried it on a 2007 machine with the same results. I've attached the db.

First, open the FileDates table and note that there are date records for 9/23, 9/26, and 9/27 (yesterday's date). Then open the File Process Dates form. The Load code will run, but instead of creating a new 9/28 record, it will stay on 9/27 and change the FileDate value to 9/28.

However, if you edit File Process Dates and delete the subform, then repeat the process (remember to change 9/28 back to 9/27 first), it works correctly.

View attachment ACH Balances test.accdb

 

[missinglinq]

Sorry...but downloaded and ran your File and it still works as expected, creating a New Record for 9/28/16!

Have no idea what to tell you! If something in the file was corrupt...the Control or the Form or the Database, itself, I should be seeing it as well!

The only thing I could think of is that MS Access itself has become corrupted and needs to be repaired/re-installed, but have no experience, whatsoever, with either of these procedures!

Sorry!

Linq ;0)>

 

[nschroeder]

Would be interested to see if others are getting the same results. Please be sure to follow my instructions exactly.

 

[static]

It does as you describe here.

 

[mjdemaris]

Might I recommend not using look up fields in your tables? It is better to use combo boxes in forms, and SQL or queries as the record source.

 

[static]

The onload event fires when the form is loading data. Trying to add a new record at that point seems wrong anyway. The code I posted earlier which adds the new record @ onopen works.

 

[mjdemaris]

Ok, I think part of the problem is the Me.Dirty = False. This resets the record entry you are trying to make.

As for the Look up fields in the table, not sure if they caused any problems, but just generally not good usage.

Also, I used a recordset to add the new date entry into the FileDates table, which only worked right after the Me.Dirty = False was removed.

Cheers!

Private Sub Form_Load()
Stop
    DoCmd.GoToRecord , , acLast
    If FileDate.Value < Date Then
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("FileDates")
    Debug.Print rst.Fields(0), rst.Fields(1)
    rst.AddNew
    rst.Fields(1) = Date
    rst.Update
    
'        DoCmd.GoToRecord , , acNewRec
'        DoCmd.GoToRecord acDataTable, "FileDates", acNewRec
    rst.Close
    Set rst = Nothing
        Me.FileDate = Date
'        Me.Dirty = False
    End If
End Sub