Locking Previous Records in Access Database based on ID

1
votes

I am trying to build a code where previous records in a certain form are locked for editing. This would be to allow users to create and edit new forms, but not change data from previous forms. I want to be able to allow users to access and edit the previous 5 forms, but lock the data in all forms previous to that.

I've tried several things, but as a novice VBA user, nothing has worked. I tried:

Private Sub Form_Load()
If Me.ID < 22 Then
Me.AllowEdits = False
Else
Me.AllowEdits = True
End If
End Sub

I used a dummy "22" to see if the code would work, but it just ended up locking all of the records, not just numbers 1-21, like I was trying to do.

I would like the "22" to be the value of the ID field in the most recent record. I would also like it to be: If Me.ID < (ID of most current record)-5

Is this possible?

1
vbams-access
It may prove easier to ship the read-only records to a separate 'archive' table. – Lee Mac

1 Answers

0
votes

Form_Loadis wrong event, as only raised once on startup withMe.IDbeing the first record. You have to check current selected record withForm_Currentevent.

Don't rely onIDbeing consecutive (e.g. caused by deletions). Select Top 5 IDs (sort decending) and get minimum.

Create as saved query (e.g. name it FormNameTop5ID, where FormName is name of form, TableName is table/query bound to form )

SELECT TOP 5 ID FROM TableName ORDER BY ID DESC

In the form create aForm_Currentevent

Private Sub Form_Current()
If Me.ID.Value < DMin("ID", "FormNameTop5ID") Then
    Me.AllowEdits = False
Else
    Me.AllowEdits = True
End If
End Sub