How to keep formula in form field when add new record using form in access

0
votes

I have a form to entry new data to table in Access 2010.

I want Request ID field to be Max(Req_Number)+1 from current table, whenever user add new record and want it to be filled automatically, also Req_Number is the key. When use Data Mode all fields are blank so I wonder how to lock one filed with a function when entry data to a table using form in access?

1
ms-accessdata-entryms-access-forms

1 Answers

0
votes

The RequestID text box should be Locked and Disabled. Add code to the form's Before Insert event as follows:

Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo PROC_ERR

    RequestID = Nz(DMax("Req_Number", "myTable"), 0) + 1

PROC_EXIT:
    On Error Resume Next
    Exit Sub

PROC_ERR:
    MsgBox "Error " & Err.Number & " - " & Err.Description
    Resume PROC_EXIT

End Sub