Do not allow Deletions

[Guest]

Hello,

Is there a way(other than setting the forms property AllowDeletions to No)
to prevent users from deleting a record or information in fields from the
form once it has been added? I need to them to add and even go back to a
previous record to edit but no delete any of the information. Please simplify
answer so that I may follow along.

Thanks!!!

 

[Dirk Goldgar]

Hello,

Is there a way(other than setting the forms property AllowDeletions
to No) to prevent users from deleting a record or information in
fields from the form once it has been added? I need to them to add
and even go back to a previous record to edit but no delete any of
the information. Please simplify answer so that I may follow along.

Thanks!!!

What's wrong with setting AllowDeletions to No? That's what it's for.
I imagine you could cancel the BeforeDelConfirm event, if you really
need to, but what problem are you trying to solve?

 

[Guest]

I have a code for user levels and an Admin user and general user works fine.
However, when I assign the power user, it is allowing deletions. I have it
set to allow additions and edits only.

Here is what I am using:

Private Sub Form_Load()
DoCmd.Restore
Call CheckPermissions
End Sub

Private Sub CheckPermissions()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim txtUser As String
Dim stSql As String
Dim stPermissions As String


On Error GoTo eh

txtUser = basMain.sGetUser
Set db = CurrentDb

stSql = "SELECT tblPermissions.Description, tblUsers.LogonID FROM
tblPermissions"
stSql = stSql & " INNER JOIN tblUsers ON tblPermissions.Level =
tblUsers.PermisLevel"
stSql = stSql & " WHERE tblUsers.LogonID=" & "'" & txtUser & "'"

Set rs = db.OpenRecordset(stSql)

If Not rs.EOF Then
stPermissions = rs!Description
Select Case stPermissions
Case "Admin"
AllowAdditions = True
AllowEdits = True
AllowDeletions = True
Case "PowerUser"
AllowAdditions = True
AllowEdits = True
AllowDeletions = False
Case Else
'''Me.RecordsetType = 2
Call DisableControls
End Select
Else
' User is not a part of the tblUsers table
Call DisableControls
End If

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Exit Sub
eh:
MsgBox "Error Number " & Err.Number & " " & Err.Description,
vbInformation, "Error"
End Sub

Private Sub DisableControls()
Dim ctl As Control

For Each ctl In Me.Controls
If Not TypeOf ctl Is Label And _
Not TypeOf ctl Is CommandButton Then
ctl.Locked = True
End If
If TypeOf ctl Is CommandButton Then
If ctl.Name = "cmdAddRecord" Then
ctl.Enabled = False
End If
End If
Next ctl
End Sub

Here is the Module:

Private Declare Function GetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long


Public Function sGetUser() As String
Dim sReturn As String
Dim iSize As Long
Dim iValid As Integer

sReturn = Space$(40)
iSize = Len(sReturn)

iValid = GetUserName(sReturn, iSize)

sGetUser = Left$(sReturn, iSize - 1)
End Function


Public Function IsLoaded(strName As String, Optional varType As Variant)
IsLoaded = (SysCmd(acSysCmdGetObjectState, _
IIf(IsMissing(varType), acForm, varType), strName) <> 0)
End Function

Public Function AddRecordStamp()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblRecordLog")

With rs
.AddNew
!UserLogID = sGetUser
!AddRecordTime = Date
.Update
End With
End Function

 

[Dirk Goldgar]

I have a code for user levels and an Admin user and general user
works fine. However, when I assign the power user, it is allowing
deletions. I have it set to allow additions and edits only.

Here is what I am using:

[snipped]

Have you stepped through the code to verify that the AllowDeletions
property is actually getting set to False? The code looks perfectly
reasonable to me, but I don't know what you've got in your tables.

Also, is the user able to delete through the user interface when he
shouldn't, or is it just through code you've placed behind a button?
It's possible to code a delete button that will delete from the form
even though AllowDeletions is set to False. Under these conditions, a
delete coded like this will fail:

RunCommand acCmdDeleteRecord ' not if AllowDeletions=False!

but this will work:

Me.Recordset.Delete

So the exact way the delete is coded could have an effect.

 

[Guest]

Here are the two tables"

tblPermissions
Level Description
1 General User
2 Power User
3 Admin


tblUsers
UserName LoginID PermisLevel
John Doe jdoe 2

There are no buttons on the form to delete, users are deleting from the
form. The tables and guts of the database are locked and they never see it.

If I added a delete button, it will delete the current record if clicked
correct? What if they do not click the button, will they still be able to
delete from the individual fields? If not, I suppose I can place the button

I have a code for user levels and an Admin user and general user
works fine. However, when I assign the power user, it is allowing
deletions. I have it set to allow additions and edits only.

Here is what I am using:

[snipped]

Have you stepped through the code to verify that the AllowDeletions
property is actually getting set to False? The code looks perfectly
reasonable to me, but I don't know what you've got in your tables.

Also, is the user able to delete through the user interface when he
shouldn't, or is it just through code you've placed behind a button?
It's possible to code a delete button that will delete from the form
even though AllowDeletions is set to False. Under these conditions, a
delete coded like this will fail:

RunCommand acCmdDeleteRecord ' not if AllowDeletions=False!

but this will work:

Me.Recordset.Delete

So the exact way the delete is coded could have an effect.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Here are the two tables"

tblPermissions
Level Description
1 General User
2 Power User
3 Admin


tblUsers
UserName LoginID PermisLevel
John Doe jdoe 2

There are no buttons on the form to delete, users are deleting from
the form. The tables and guts of the database are locked and they
never see it.

According to the table contents you posted, your code is checking for
the wrong value. Your code says:

Case "PowerUser"

But your table has

2 Power User

Now, maybe one or the other post of yours was wrong, but I suspect you
are just checking the wrong value. When I set up the tables as you laid
out, and change your code to check for "Power User" instead of
"PowerUser", the code successfully prevents deletions on the form.

That's why I asked,

Have you stepped through the code to verify that the AllowDeletions
property is actually getting set to False?

If you had, you'd have seen that the "PowerUser" case wasn't being
exected.

If I added a delete button, it will delete the current record if
clicked correct?

If deletions are allowed, yes, but not if they aren't -- unless you code
it that one way I showed you, that deletes directly from the form's
recordset. I don't recommend that unless you need to do it that way.

What if they do not click the button, will they
still be able to delete from the individual fields?

Refusing to allow deletions is different from preventing the user from
saving blank fields. You can check for that by either making those
fields Required, or using the form's BeforeUpdate event to check if the
fields are Null/blank and refusing to save the record fi they are.

 

[Guest]

Do you know that I never even thought to check that??? I have to run to a
meeting but will check it in the morning. If that's the case, thank you for
the sharp eye!!! You may have saved me from scraping a nice piece of code. I
appreciate all your help Dirk in staying with me on this. Thank you again and
have a great day!!!

Doug