Access 2003 -- show new record as committed upon OpenForm

1
votes

The user needs to create a new order. To allow this, we have a button that opens the order form to a blank record in the Order table:

DoCmd.OpenForm "frmOrder", , , , acFormAdd

The form comes up and is ready to go. But the new Order ID number displays (New). That field is an autonumber. When I make an entry anywhere on the form, (New) is replaced by the ID number. But I want it right away.

I figure it's because the new record is not committed. Can I make an initial commit so the primary key is displayed upon opening?

I tried using RunCommand acCmdSaveRecord to force a commit in the Form_Load() event, but no success.

1
vbams-access

1 Answers

2
votes

This might be a bad idea,

if you commit first, how can you cancel the order, without modifying your db ?

By default, even if frmOrder is opened for acFormAdd, if the user click on the button [Close], AutoNumber is not modified. If modifying any field on the form either by VBA or by a user trigger, AutoNumber is incremented by 1, even no record is inserted at the end, or even cancelled.

Anyway, there is a method, assuming

Me.txtOrderDate.Controlsource = "OrderDate"

then

Private Sub Form_Load()

  Me.txtOrderDate.Value = Now()

End Sub

If you have an OrderDate field, or like, ID is automatically generated by Access.

Be careful, if you use SQL Sever Backend, ID is not assigned until the record is committed.