Stop a record being edited or deleted

SteveC24

Registered User.
Local time
Today, 15:36
Joined
Feb 1, 2003
Messages
444
Hello,

I have been looking around on the forums, but can't find anything that stops both of these things happening.

I have a table (with associated form) with a list of equipment types in. The user needs to be able to add/delete to/from this list, but I would rather they didn't delete, change or move my <<All>> option, as that is used in the form in a combobox to show ALL the equipment (regardless of type).

Any help is greatly appreciated! :)
 
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
 
Thank you! I have downloaded your demo, and will take a look! :D
 

Users who are viewing this thread

Back
Top Bottom