Deleting record with primary key

[Wylie C]

I have a form with a control bound to a table. The field in the table,
RouteNum, is set as primary key. When the form opens in new record mode, the
RouteNum number is created by adding one to the maximum value in the
corresponding field in the table. I have placed the delete record and close
form command buttons by using the built in wizard in Access 2000. The
required property is set to No in the table. The problem I am having is if
the user opens the form accidentally, clicking the delete key advises that
the record is being deleted but then I get a msg, No current record but I
can't close the form using the close button. In fact nothing works and I have
to shut down the program to work in the program again.
HELP!! Code follows:

Private Sub Delete_Click()
On Error GoTo Err_Delete_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70


Exit_Delete_Click:
Exit Sub

Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click

End Sub
Private Sub Close_Click()
On Error GoTo Err_Close_Click


Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub

 

[Marshall Barton]

I have a form with a control bound to a table. The field in the table,
RouteNum, is set as primary key. When the form opens in new record mode, the
RouteNum number is created by adding one to the maximum value in the
corresponding field in the table. I have placed the delete record and close
form command buttons by using the built in wizard in Access 2000. The
required property is set to No in the table. The problem I am having is if
the user opens the form accidentally, clicking the delete key advises that
the record is being deleted but then I get a msg, No current record but I
can't close the form using the close button. In fact nothing works and I have
to shut down the program to work in the program again.
HELP!! Code follows:

Private Sub Delete_Click()
On Error GoTo Err_Delete_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Click:
Exit Sub

Don't set the PK field's value until you're sure the user
intends to creates a new record. Access fills in an
autonumber field at the time of the BeforeInsert event. In
your case I think that is way too early and that you should
use the BeforeUpdate event. You would have to be sure to
only set it on a new record though:

Form_BeforeUpdate:
If Me.NewRecord Then
Me.RouteNum = Nz(DMax("RouteNum", . . .), 0) + 1
End If

That will avoid most of the problems you're asking about.
However, if a user does hit your delete button after
starting to enter data on a new record, you should not run
the delete code (because the record doesn't exist yet).
Instead you can just undo whatever they've done before
clicking the delete button:

btnDelete_Click:
If Me.NewRecord Then
Me.Undo
Else
DoCmd.RunCommand acCmdDeleteRecord
End If

And, since the partial new record was not saved, the
BeforeUpdate event never fired so the route number was not
incremented.

 

[Wylie C]

Thank you..great explanation.

Don't set the PK field's value until you're sure the user
intends to creates a new record. Access fills in an
autonumber field at the time of the BeforeInsert event. In
your case I think that is way too early and that you should
use the BeforeUpdate event. You would have to be sure to
only set it on a new record though:

Form_BeforeUpdate:
If Me.NewRecord Then
Me.RouteNum = Nz(DMax("RouteNum", . . .), 0) + 1
End If

That will avoid most of the problems you're asking about.
However, if a user does hit your delete button after
starting to enter data on a new record, you should not run
the delete code (because the record doesn't exist yet).
Instead you can just undo whatever they've done before
clicking the delete button:

btnDelete_Click:
If Me.NewRecord Then
Me.Undo
Else
DoCmd.RunCommand acCmdDeleteRecord
End If

And, since the partial new record was not saved, the
BeforeUpdate event never fired so the route number was not
incremented.