You will not be able to stop edits or deletes in a table, you will have to make sure your users do not have access to your tables. You can stop edits or deletes in a query by changing its record set type to “snapshot”
On your form, you can stop edits or deletes by changing the properties of the controls to: locked = Yes, however this is often laborious, and suffers from the problem that you have to unlock them when you want to do an edit. This will not stop delete However! To stop delete you have to set your forms property Allow deletions to “No”
I find the following two pieces of code useful, the first routine locks your controls and the second routine unlocks them.
Public Sub fLockAll()
On Error GoTo fLockAll_Err
Dim I As Integer
For I = 0 To (Me.Controls.Count - 1)
Me.Controls.Item(I).Locked = True
Next
fLockAll_Exit:
Exit Sub
fLockAll_Err:
'If Err.Number = 438 Or Err.Number = 2448 Then
Resume Next
'End If
MsgBox Err.Description, , conAppName & " - Error Number " & Err.Number
Resume fLockAll_Exit
Resume
End Sub
Public Sub fUnLockAll()
On Error GoTo fUnLockAll_Err
Dim I As Integer
For I = 0 To (Me.Controls.Count - 1)
Me.Controls.Item(I).Locked = False
Next
fUnLockAll_Exit:
Exit Sub
fUnLockAll_Err:
'If Err.Number = 438 Or Err.Number = 2448 Then
Resume Next
'End If
MsgBox Err.Description, , conAppName & " - Error Number " & Err.Number
Resume fUnLockAll_Exit
Resume
End Sub
When you need to edit certain controls you can call a routine similar to this one and have it unlock the controls you want to edit.
Public Sub fAddItems()
Call fLockAll ‘Lock them and then unlock the one’s you want
txtJobMissingItems.Locked = False
sFrmWindow1.Locked = False
chkJobSentToDisp.Locked = False
txtJobDateSent.Locked = False
chkJobBoxMissing.Locked = False
chkJobDeleted.Locked = False
End Sub
You can even call the above subroutine when you’re actually opening your form, This gives you much more power than the other method of setting values whilst opening the form “OpenArgs” And it’s much simpler, And less error prone! (in my opinion)
Public Sub fFrmJobEdit()
'For adding Items
Dim FormName As String
Let FormName = "frmJob"
DoCmd.OpenForm FormName, acNormal, , , acFormEdit
With Forms(FormName)
.fAddItems ‘This calls the routine in the form
.lblIntro.Visible = False
.sFrmWindow1.Visible = True
.btnPreview.Visible = True
.btnReturnSlip.Visible = True
.btnBookInLbl.Visible = True
.txtFrmLayout = 2
.Caption = conAppName & " - Add EDP Details "
End With
End Sub
You can download a working example from my website, download Item 7:
One Form Example Here