Creating seperate User permissions without VBA

[vbaInet]

In think it isn't as simple as making a dbs read only. if you do that, you will find you cannot change the values of some things like check boxes and combo boxes.

Good point! That would depend on how the forms were designed and whether read-only users should be allowed to make selections via the same form.
If the criteria selections are made via a switchboard or via a separate form then looping through controls won't be required.

 

[ToryM]

Thanks Gemma-the-husky and vbaInet

I got rid of the macros and it seems the macros and VBA didn't interfere. It turns out the Snapshot (2) is not allowing anything but the first record from each table to be shown in the form. When I delete the recordset from the "Admin" Permissions and set them on the forms, then it will allow the records to be viewed correctly, but if I do the same in the "Read-Only" permissions it acts the same way but allows changes to be made to the Records which is mainly what I don't want. All I want is for Admins to be able to Add/Delete/Edit Records only. Is there another way to do this?

Thanks
Tory

 

[vbaInet]

Two things:

Did you understand gemma-the-husky's point?

Like I said earlier, setting the recordset type to snapshot has nothing to do with how mnay records are returned, absolutely nothing. And I showed you how to do it in the OpenForm method, so is that what you did?

 

[ToryM]

Did you understand gemma-the-husky's point?

I think I do- that setting to Snapshot will prevent any changes to anything on the record on load?

Like I said earlier, setting the recordset type to snapshot has nothing to do with how mnay records are returned, absolutely nothing. And I showed you how to do it in the OpenForm method, so is that what you did?

I set up the permissions and the Recordset type the way you suggested but now I'm a little lost as to what you mean about how many records are returned. I'm only wanting 1 record returned in the pop-up form which depends on the record I double click on. So the form will be the same but the data in it can change depending on the record double-clicked on unless the user has Admin permissions, in which case they can edit the records also.

Am I getting what you mean?

 

[vbaInet]

Not quite. Let me break it down:

1. OpenForm with the acFormReadOnly option will lock all your controls (bound and unbound) and set the recordset type to Snapshot
2. AllowAdditions, AllowDeletions and AllowEdits will lock all your controls (bound and unbound).
3. Recordset Type set to Snapshot will not lock your controls but it will not allow you make changes to the underlying data.

So it depends what you want to happen. Best thing to do is show us a screenshot of your form and we'll take it from there.

 

[ToryM]

one screen shot coming up!

 

[ToryM]

Sorry- Had to block out the Logos

But hopefully this'll explain things more and thank you so much for the help!

Tory

 

[vbaInet]

Hopefully your next try will include the attachment

 

[ToryM]

Sorry! Edited the last one!

 

[vbaInet]

Ok, because you have a textbox that you use for searching all you need to set is the Recordset Type of the form using:

DoCmd.OpenForm "FormName"             <--- open the form
Forms("FormName").RecordsetType = 2   <--- [COLOR="Blue"]set it here[/COLOR]

You don't need to set any other property.

 

[vbaInet]

There's a caveat to this.

]You don't need to set any other property.

If you have controls that fire off code behind the scenes then you will need to loop through all the controls and lock them.

 

[ToryM]

sorry vbaInet but I still can't seem to get it to work

I have this at the minute for the first pop-up form to appear, this is the code I'm using on the Start up page for the comment field :

Option Compare Database
Option Explicit

Private Sub Form_Load()

Call SetPermissions(Me)

End Sub

Private Sub txtComments_DblClick(Cancel As Integer)

DoCmd.OpenForm "frmOptionDetailsCard", acNormal, , WhereCondition:="ID= " & Me.ID

End Sub

Do I add the Forms("FormName").RecordsetType = 2 in here or in the actual pop-up form coding?

 

[AOB]

I would assume as part of the SetPermissions subroutine :

Public Sub SetPermissions(frm as Form)
 
    With frm
 
        Select Case DLookup("[User Permissions]", "tblUserLogon", _
                            "[UserName] = '" & Forms!frmLogon!cboUserName & "'")
 
            Case "ADMIN"
 
                .AllowEdits = True
                .AllowAdditions = True
                .AllowDeletions = True
[COLOR=red]                .RecordsetType = 0       ' Dynaset[/COLOR]
 
            Case "USER WITH EDITING"
 
                .AllowEdits = True
                .AllowAdditions = False
                .AllowDeletions = False
[COLOR=red]                .RecordsetType = 0       ' Dynaset[/COLOR]
 
            Case Else
 
                .AllowEdits = False
                .AllowAdditions = False
                .AllowDeletions = False
[COLOR=red]                .RecordsetType = 2       ' Snapshot[/COLOR]
 
        End Select
 
    End With
 
End Sub

But vbaInet's caveat still stands :

If you have controls that fire off code behind the scenes then you will need to loop through all the controls and lock them

 

[vbaInet]

Here -->

Private Sub txtComments_DblClick(Cancel As Integer)
 
   DoCmd.OpenForm "frmOptionDetailsCard", acNormal, , WhereCondition:="ID= " & Me.ID
[COLOR="Blue"]   Forms("FormName").RecordsetType = 2[/COLOR]
End Sub

We need to see your SetPermissions code.

 

[ToryM]

My SetPermissions are:

Option Compare Database
Option Explicit
Public Sub SetPermissions(frm As Form)

With frm

Select Case DLookup("[User Permissions]", "tblUserLogon", _
"[UserName] = '" & Forms!frmLogon!cboUserName & "'")

Case "ADMIN"

.AllowEdits = True
.AllowAdditions = True
.AllowDeletions = True

Case "USER WITH EDITING"

.AllowEdits = True
.AllowAdditions = False
.AllowDeletions = False
.RecordsetType = 0

Case Else

.AllowEdits = False
.AllowAdditions = False
.AllowDeletions = False
.RecordsetType = 2
End Select

End With

End Sub

 

[vbaInet]

Still using your old code. Did you understand what I explained here?

Not quite. Let me break it down:

1. OpenForm with the acFormReadOnly option will lock all your controls (bound and unbound) and set the recordset type to Snapshot
2. AllowAdditions, AllowDeletions and AllowEdits will lock all your controls (bound and unbound).
3. Recordset Type set to Snapshot will not lock your controls but it will not allow you make changes to the underlying data.

So it depends what you want to happen. Best thing to do is show us a screenshot of your form and we'll take it from there.