Message Box Where Yes Advances Form To New Record

[chemguy120]

I'm banging my head against the wall over something I thought would be simple! I have a form that that opens to the last record. I then want the "txt_ReferenceDate" field to be checked to see if it matches the current date. If it does the form should open normally to the last record. If it doesn't a yes/no message box should ask if the user wishes to create a new record (Yes) or continue of with the last record (No). I've tried using the following code:

Private Sub Form_Load()
    DoCmd.GoToRecord , , acLast
    
    If Me.txt_ReferenceDate <> Date Then
        If MsgBox("This record is not for the current date. Would you like to create a new record?", vbYesNo, "Warning!") = vbYes Then
            DoCmd.GoToRecord , , acNewRec
        Else
            Exit Sub
        End If
    End If
    
End Sub

If I answer no everything works as expected. If I answer yes I get the dreaded 2499 error stating I cannot use the GoToRecord in design view(??). I've tried the code in both the OnLoad and OnCurrent events. I get the same results using the OnCurrent event except the message box comes up twice before either opening the form or giving me the 2499 error depending on the selection.



I know the user could just manually advance to a new record after opening the form without being prompted. The purpose of the message box is to force the user to think about what they are doing rather than just blindly charging ahead. Thanks for any and all help!

[Micron]

The thing about design view is odd indeed. I'm wondering if that going to the last record makes it active, thus you can't go to new. I'm getting a bit rusty.
I might use a DLookup or DMax (depends on what your data looks like) to get the last record date and compare that to the current date. If they're not the same, do one thing. If they are, do the other. Note that if your table date field values contain time values as well, then I doubt it will ever equal Date(). Default time portion of Date() is 00:00:00 although the function won't show that when it returns a date.

[June7]

Code works for me in Load event.

Could provide db for analysis.

[chemguy120]

The DB is a bit complicated to try to remove stuff I would not have the authority to share. I used to just have the form open to the last record then automatically advance to a new record if it wasn't the current date using:

If Me.txt_ReferenceDate <> Date Then DoCmd.GoToRecord , , acNewRec

This worked well for YEARS up until this Tuesday (patch Tuesday, so not sure if THAT may have played a part). Then suddenly it won't work. I re-created the form from scratch and it worked as it used to for a day, then that code just stopped working.

If my idea as laid out in the original post won't work then I may just have a standard informational box warn the user the record is not for the current date then they would have to manually advance to a new record. I like the yes/no idea however as it seems more eloquent from an end user standpoint!

[davegri]

Just a shot in the dark, but as an alternate you could try

DoCmd.RunCommand acCmdRecordsGoToLast
and
DoCmd.RunCommand acCmdRecordsGoToNew

And FWIW, you don't need the else exit sub. You're gonna exit sub regardless.

[chemguy120]

you don't need the else exit sub

I actually meant to take that out. That was me experimenting trying to figure out what the issue is.

I get the same results using your code as I do the original except the 2499 error is replaced with 2046 "The command or action 'RecordsGoToNew' isn't available now."

When removing the message box code, both versions of the code to go to the last record as expected. Could there be an instant after clicking Yes that the form is momentarily not loaded and that is causing an issue?

[chemguy120]

Also, if it is worth mentioning, after the error is triggered, if I choose to end the dialog rather than go into the debugger, the form opens to the last record as expected for the No response.

[moke123]

Is the forms allow additions property set to yes?

[chemguy120]

Sorry for responding late, it's been a busy week.

Is the forms allow additions property set to yes?

The additions property is set to yes.

The "New Record" button on the form works to advance to a new record. The button uses the following code (in case it may be relevant):

Private Sub btn_NewRecord_Click()
    DoCmd.GoToRecord , , acNewRec
    Me.txt_ReferenceDate.SetFocus
    
End Sub

It would be nice if the Yes option in an initial message box would do that as well. For the time being I've reverted to a warning message if the date does not match the current date when the form is opened so the user knows to hit the new record button if they no longer have anything to add to the last record.

[mike60smart]

You can try the following:-

Dim LResponse As Integer
LResponse = MsgBox("Do you wish to enter a new  record?", vbYesNo, "Continue")
If LResponse = vbYes Then
   On Error Resume Next
DoCmd.GoToRecord , , acNewRec
Me.txt.Referencedate.SetFocus
Else
   Me.txt.Referencedate.SetFocus
End If

[Welshgasman]

That is bad coding.
You are duplicating the setfocus for no reason?

[mike60smart]

Thanks for that

[Welshgasman]

Never mind the fact that the control is called txt_ReferenceDate and not txt.ReferenceDate

[Micron]

What's with the underscore between the control name and its type identifier? That just seems unnecessary to me.
Ya know, you're only allowed so many key presses before you run out. No sense in using them when you don't have to (and probably we could count on one hand the number of people who would insert an underscore there).