“No Current Record” error on acCmdSaveRecord when

Question

I'm using RunCommand acCmdSaveRecord to save a record, and I'm trying to use the form's BeforeUpdate event to validate certain fields that I don't want to be left blank before the record is saved.

I'm a bit new to using BeforeUpdate so I'm not sure if I'm using it correctly; here's my BeforeUpdate code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.[First Name]) Or IsNull(Me.LastName) Or IsNull(Me.DateOfBirth) Or IsNull(Me.CourseStartDate) Then

        MsgBox "Before the enrolment can be saved, you must provide:" & vbCrLf & vbCrLf _
             & "- First Name" & vbCrLf _
             & "- Last Name" & vbCrLf _
             & "- Date of Birth" & vbCrLf _
             & "- Start Date" & vbCrLf & vbCrLf _
             & "You must also attach a course. This can be done by selecting " _
             & "the appropriate course in the Prospectus Search and clicking " _
             & "the Use Prospectus Code button." & vbCrLf & vbCrLf _
             & "If your course is not currently in the prospectus, you can " _
             & "add it first by clicking the Add Course button.", vbOKOnly Or vbInformation

        Cancel = True

    Else

        Me!EnrolmentID = "Enr" & Format(Me!ID, String(12 - Len("Enr"), "0"))

    End If

End Sub

So this basically tests to see if certain fields have been left blank, if they have been left blank then a message box is displayed showing what fields need data and then the update is cancelled. Otherwise it assigns a custom primary key and allows the record to update.

It's throwing an error when cancelling the update though stating "No Current Record" and highlighting the RunCommand acCmdSaveRecord line of code.

What do I need to do to avoid the error? I've also tried Me.Dirty = False, but still get the same error.

@Johnny Bones:

Here's a simplified test I've tried on the answer you gave me:

Public ShouldRun As Boolean

Private Sub Command7_Click()

    If ShouldRun = True Then

        MsgBox ShouldRun

        RunCommand acCmdSaveRecord

    End If

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.Field1) Or IsNull(Me.Field2) Then

        MsgBox "This is the true"

        ShouldRun = False

    Else

        MsgBox "This is the false"

        ShouldRun = True

    End If

End Sub

Basically none of the MsgBox functions are firing and the record is being saved regardless of whether a field is being left blank or not. I tried this as a test because the MsgBox in my real code was not firing in the BeforeUpdate event either. Can't see why though.. if a user leaves one of the fields blank this should trigger the true part of the if statement in the BeforeUpdate event, right?

The only thing I can think of is that the BeforeUpdate event is not being triggered by RunCommand acCmdSaveRecord, despite MSDN saying:

"if you then move to another record or save the record, the form's BeforeUpdate event does occur."

Not sure what the order is here... does VBA run the RunCommand acCmdSaveRecord then the BeforeUpdate event? If this is the case then the ShouldRun variable won't have any assignment at the first step (RunCommand acCmdSaveRecord) because it only gets that at the second (BeforeUpdate).

Answer 1

I would set a global variable like this:

Global ShouldRun as Bool

You throw that at the very top of the code page, up where you declare Options Explicit. Then, in the If/Then/Else statement you provided above, replace the line:

Cancel = True

with

ShouldRun = False

and in the Else part add the line

ShouldRun = True

Finally, your command button's OnClick event should look more like this:

If ShouldRun = True then
  RunCommand acCmdSaveRecord
End If

By doing this, it will never even try to save the record if it shouldn't.

Answer 2

I know it's late, but have you tried to use DoCmd.RunCommand acCmdSaveRecord instead?

Because for me it's firing the Before Update event well, when RunCommand acCmdSaveRecord wasn't!

But after cancelling the event you may get an execution error '2759', because the record can not be save anymore. So you can handle it this way :

Private Sub Command7_Click()
On Error GoTo Command7_Click_Error

     DoCmd.RunCommand acCmdSaveRecord

Command7_Click_Exit:
     Exit Sub

Command7_Click_Error:
     If Err.Number=2759 Then
         'ignore the error
     Else
         MsgBox Err.Description
     End If    
     Resume Exit_Here
End Sub

If you are using Me.Dirty = False instead, you can handle the error '2101'.

Hope it helps