MS Access vba save button error 3021

Question

For various reasons, I want to set up a custom button on my forms to save the current record. I use a navigation form and want to trigger the same process (integrity-checks, user input etc.) whenever the entry is saved, thus whenever the user presses the "save"-button or switches to another form. The user will conditionally be asked to confirm the process and is thus able to cancel it as well.

Everything is running smoothly with one really odd and annoying exception: Whenever I click the save button on a new record and prompt a message within the "BeforeUpdate" event, I receive

RTE 3021 ("no current record")

Without the MsgBox, everything is fine. Even more strange: When I trigger the save process by switching to another form using the navigation form (or simply press "outside" the form used for data entry), everything is fine as well.

Here is a minimalistic example (similar results with DoCmd.Save, Requery or acCmdSaveRecord):

Private Sub vt_save_Click()
Me.Dirty = False
End Sub

Private Form_BeforeUpdate(Cancel As Integer)
Cancel = True
MsgBox "Test"
End Sub

Any ideas? I simply can't wrap my head around that error.

What is the minimalistic example supposed to show? Possibly error is from the other code called in BeforeUpdate trigger (i.e., integrity-checks, user input) that cannot run on a new record. Please post such fuller code. — Parfait
I've never used the form BeforeUpdate event. I do data validation within the button Click event. — June7
@Parfait The minimalistic example shows you exactly what is described. Create a new database, one table, one form. Create a record, enter data and press the button. Et voilá - you'll get the error. — Thuro G
@June7: Yes, this would be the workaround. Though, since there are (basically) two ways to save the entry (press a button or switch to another form) I want to put both functions in one code. — Thuro G

Chris Chris · Accepted Answer · 2017-12-19T18:06:18

You could maybe try to run a query using the values in the form while checking if the record exists or not.

Is there a primary key on the table? if so, the primary key will be your focal point.

Private Sub vt_Save_Click()
dim rst        as DAO>Recordset
Dim strSQL     as String
Dim strID      as string

strID = me.YourPrimaryKeyField

strSQL = "SELECT * " & _
         "FROM YourTableName " & _
         "WHERE (((YourTableName.YourFieldName) =" & me.PrimaryKeyField & "));"

set rst = currentdb.openrecordset(strsql)
if rst.recordcount = 0 then
    currentdb.execute "INSERT INTO YourTableName ( List All Fields to Add ) " & _
                      "SELECT List All Field controls with values to add;"
End IF

'Anything else you want the code to do from here

EndCode:
If not rst is nothing then
    rst.close
    set rst = nothing
End IF
End Sub

Repeat this process for the Form_LostFocus() event. If you want to make it easier, make this code a module and call within both event triggers on your form.

If this doesn't work please let me know and I will be happy to further assist.

MS Access vba save button error 3021

2 Answers