forms - Accesslevel

[Sheba]

thanks Dave. The data types are matching and yes I'm aware that the count starts from 0...


super

[burrina]

Can you upload a DEMO for us to look at?

[Sheba]

Hi Dave,

Sorry for the delayed response. Please see the attached file. I tried as best as I could to capture the problem. You can log in as Jay (echo1 as PW) or as Laura (echo2 as PW) or as Michael (echo3 as PW). If you double click on the TripID field of the subform, an old record comes up. Click on DELETE.

**The data entry user should not be able to delete a record**

[ssanfu]

There is not a login form, subform or a field "TripID" that I can find in the example dB "test-database2"

[burrina]

Sheba,I did not download your db. Steve (ssanfu) says somethings are missing. It has been downloaded 2 times. Please upload a complete Demo. People here are really ready to help!

[Sheba]

a thousand apologies I think I uploaded the wrong one. Apparently, a previous upload was still hanging around and since they had the same file name, I must selected it again?

[Sheba]

Yes this is the correct file. Dave and Steve, I apologise for the delay and really appreciate your help.

[burrina]

Steve has already downloaded it. I bow out. Good Luck With Your Project!

[Sheba]

many thanks Dave I really appreciate the support I have received thus far from this site. I look forward to Steve's feedback
Have a great evening

[Demerit]

In which event are you running the code? Sometime the problem comes fromThe event. Is it the forms current, on open event, on load event. Check that out. The code should work better in the on current event of the form.

[June7]

All your code does is prevent record deletion with the keyboard and ribbon Delete on the main form. Does nothing for the subform. Set those properties in Property Sheet for PediatricSubform to No.

Also, does not prevent programmatic deletion. The Delete command button code will have to validate user has this permission. Or disable the Delete command button in the code.

I don't use macros, only VBA.

[ssanfu]

I've found a couple of things that are a problem - still looking at the dB.

But first, Questions:
1) How many Access levels do you have?
2) Is it a fixed number Access levels or will/can the number of access levels change?

3) How many Categories are there?
4) Will/can the number increase/decrease?

5) How do the access levels and categories relate?

6) If I have an AccessLevelID of 4 and a CategoryID of "PD02", does that mean that I can't enter data for "CR03"?

Description	AccessLevelID	AllowAdditions	DataEntry	AllowEdits	AllowDeletions
Administrator	1	YES	YES	YES	YES
?	2
?	3
Data Only	4	YES	YES	no	no
Read Only	5	no	no	no	no

-----------------------------------------------
OK, problems.....

1) Look up fields in the tables. IMO, a major waste of time.
The Ten Commandments of Access
See: http://access.mvps.org/access/tencommandments.htm

and The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm


2) The PK for the table "Category" is a text field. All of my PK fields are Long Integers (actually Autonumbers). There are issues with using text fields as the PK. (or is it just me??)

3) As a rule, I do not use the data validation in tables. I validate in the controls and forms. Easier because I can handle the error however I see fit, not just get an error . I do not allow anyone access to tables for any reason. And I very rarely (almost never) use a form in datasheet mode. I do "create" my own datasheet display using a form in continuous view with control packed tightly together. Again, for controlling data entry and validation. (issues with control.... Why, yes.. is that bad???)

4) So opening the form "frmLogin" and entering the correct user id, Password and clicking on the OK button executes some code. here is a snippet:

    If Me.txtPassword.Value = DLookup("strPassword", "tblUser", "[UserID]=" & Me.cmbUser.Value) Then
        UserID = Me.cmbUser.Value
        DoCmd.OpenForm "PaediatricForm", acNormal, acSaveNo
        'DoCmd.OpenForm acForm, "PaediatricForm", acSaveYes

The openform command is looking for a filter named "acSaveNo". Doesn't throw an error, but is the wrong argument.

You have this code:

DoCmd.Close acForm, "PaediatricForm", acSaveNo

The parameter "acSaveNo" (or acSaveYes) does not have anything to do with saving the data.
"acSaveNo" and "acSaveYes" only apply IF the form was opened in design view. If changes were made, then "acSaveNo" / "acSaveYes" determine if the changes to the form are saved or not.


and the biggie:
5) Right now it looks like it is the DELETE macro that is causing the problems. I never use macros so I am having to convert the macro to VBA. Then do more testing....




Testing continues........

[ssanfu]

In the delete macro, delete the command "GoTo Control". (=[Screen].[PreviousControl].[Name])

Access has a bad habit of naming bound controls the same name as the control source.
I renamed the subform control to "sfPaediatric".

Then I added 3 lines

Private Sub Form_Open(Cancel As Integer)

    If Forms!frmLogin!cmbUser.Column(3) = 4 Then
        If Forms!frmLogin!cmbUser.Column(4) = "PD02" Then
            With Me
                .DataEntry = True
                .AllowEdits = True
                .AllowDeletions = False
                .AllowAdditions = True
                .AllowFilters = True
            End With
            '-----------subform properties -----------
            Me!sfPaediatric.Form.AllowDeletions = False
        Else
            MsgBox "You are not authorized to view this form"
            Cancel = True
            DoCmd.Close acForm, "PaediatricForm", acSaveNo
        End If
    Else
        If Forms!frmLogin!cmbUser.Column(4) = "All" Then
            If Forms!frmLogin!cmbUser.Column(3) = 1 Then
                With Me
                    .DataEntry = True
                    .AllowEdits = True
                    .AllowDeletions = True
                    .AllowAdditions = True
                    .AllowFilters = True
                End With
                '-----------subform properties  -----------
                Me!sfPaediatric.Form.AllowDeletions = True
            Else
                With Me
                    .DataEntry = False
                    .AllowEdits = True
                    .AllowDeletions = False
                    .AllowAdditions = False
                    .AllowFilters = True
                End With
                '-----------subform properties -----------
                Me!sfPaediatric.Form.AllowDeletions = False
            End If
        End If
    End If
End Sub

[Sheba]

Hello demerit, the code is in the form's Open event...

[Sheba]

hey June7 - thanks for your post. Records CAN be deleted only not by Data entry users. If I set the form's delete property to NO will that not affect everyone?