Solved Whats wrong with my UserRights Logic?

[HillTJ]

Hi,

I have a mainform 'frmPersons' on it are a number of tabbed subforms. As an example one is called 'frmAddress_Subform, and the subform control is called 'SubfrmTab' to be original!

My intention is to pass the form name to the below function to assign user rights. All works on the main form where I use the Form Open event to call : AssignUserRights(me.Name), but I cannot get it to work from the formLoad event : Call AssignUserRights("forms!frmPersons.frmAddress_Subform"). I had the code on the form Open event originally but tried it to no avail on the form Load Event.

I get the error message 2450 ....cannot find referenced form.. So I think I'm referencing the form wrongly. I've tried various iterations but no success.

Whats wrong? Appreciate a Heads-Up.

Public Function AssignUserRights(ByVal frm As String)


Dim AccessLevel As Integer


AccessLevel = intAccessLevel


MsgBox intAccessLevel
Select Case AccessLevel


Case Is = "1" 'Viewer - view all records
    MsgBox "1"
    Forms(frm).AllowEdits = False
    Forms(frm).AllowAdditions = False
    Forms(frm).AllowDeletions = False
    
Case Is = "2" 'User - Can View Add and Amend Own Records
    MsgBox "2"
    Forms(frm).AllowEdits = True
    Forms(frm).AllowAdditions = True
    Forms(frm).AllowDeletions = False
    
Case Is = "3" Or "4" Or "5" 'Power User, Administrator or Developer - Can View Add Amend and Delete Their Own Records
    MsgBox "3 , 4 or 5"
    Forms(frm).AllowEdits = True
    Forms(frm).AllowAdditions = True
    Forms(frm).AllowDeletions = True


End Select


End Function

 

[GaP42]

Your reference to the subform property should be in the form of Forms.frmPersons!Subform1.Form!...
See: Forms: Refer to Form and Subform properties and controls (mvps.org)

 

[Edgar_]

Pass the form object instead. Remove the quotes.

Public Sub AssignUserRights(frm As Form)


Dim AccessLevel As Integer


AccessLevel = intAccessLevel


MsgBox intAccessLevel
Select Case AccessLevel


Case Is = "1" 'Viewer - view all records
    MsgBox "1"
    frm.AllowEdits = False
    frm.AllowAdditions = False
    frm.AllowDeletions = False
 
Case Is = "2" 'User - Can View Add and Amend Own Records
    MsgBox "2"
    frm.AllowEdits = True
    frm.AllowAdditions = True
    frm.AllowDeletions = False
 
Case Is = "3" Or "4" Or "5" 'Power User, Administrator or Developer - Can View Add Amend and Delete Their Own Records
    MsgBox "3 , 4 or 5"
    frm.AllowEdits = True
    frm.AllowAdditions = True
    frm.AllowDeletions = True


End Select


End Sub

Call AssignUserRights(forms!frmPersons.frmAddress_Subform.Form)
Or
Call AssignUserRights(Form_FormName) if it has module.

If frmAddress_Subform is the name of the subform control in frmPersons, keep in mind that if you do not specify that you want to refer to the form and you end your reference like this:
Call AssignUserRights(forms!frmPersons.frmAddress_Subform)
You're referring to forms!frmPersons.frmAddress_Subform.Controls

The default member of a subform is Controls, it expects you to specify a control inside the form.

Also, you're not trying to return anything from this procedure, so a Sub is better in this case.

Finally, the only thing that would kinda let you use a reference as a string is the Eval function, but even that would only work if you wanted a value, not a form reference.

 

[HillTJ]

All, thanks for the prompt response. Will have a go and let you know. Cheers

 

[HillTJ]

Friends, yes it works thanks. One question about;

Call AssignUserRights(Form_FormName)

I'd like to have formname provided by the system, that is me.Name. then concatenate as AssignUserRights("form_"&"me.Name") but I get an error when compiling. Won't accept a string...

 

[CJ_London]

Just use Me

Call AssignUserRights(Me)

 

[HillTJ]

Well, that was pretty simple! I've learn't about form objects. Cool. Thanks to all whom contributed.

 

[HillTJ]

So, if I may, I have one more question. On each subform is a button 'BttnDelete' that runs a deletion module. I wish to disable this for certain users. The 'BttnDelete' control is not on the mainform as I do not wish the user to be able to delete the parent record. I thought I'd check whether the form opened is a main form or a subform & if a subform, disable the 'BttnDelete' control.

I'm still struggling a bit with form Objects & would appreciate a heads up. Thanks.

Option Compare Database
Option Explicit
Public Function AssignUserRights(frm As Form) ' Used for Assigning User Rights on Main & Subforms


Select Case intAccessLevel


Case Is = "1" 'Viewer - view all records but Cannot Delete Records from Subforms
    frm.AllowEdits = False
    frm.AllowAdditions = False
    frm.AllowDeletions = False
    If IsSubform(frm) Then frm.BttnDelete.Enabled = False ' That is to disable the 'BttnDelete' controls on subform
 
      
Case Is = "2" 'User - Can View Add and Amend Own Records but Cannot Delete Records from Subforms
    MsgBox "2"
    frm.AllowEdits = True
    frm.AllowAdditions = True
    frm.AllowDeletions = False
    
Case Is = "3" Or "4" Or "5" 'Power User, Administrator or Developer - Can View Add Amend and Delete Their Own Records
    MsgBox "3 , 4 or 5"
    frm.AllowEdits = True
    frm.AllowAdditions = True
    frm.AllowDeletions = True


End Select


End Function
Public Function IsSubform(frm As Form) As Boolean
Dim bHasParent  As Boolean


On Error GoTo NotASubform


    bHasParent = Not (frm.Parent Is Nothing)
    IsSubform = True
    Exit Function


NotASubform:
    IsSubform = False
  
End Function

 

[CJ_London]

You can check if the form is opened in its own right or as a subform by referencing the parent property

if it doesn’t exist you will get an error. Plenty of ways to do this - for example

function hasParent(frm as form) as boolean
Dim s as string
on error resume next
S=frm.parent.name
HasParent=err=0
End function

 

[HillTJ]

@CJ_London , thanks. Got that bit I think, but This bit cdoesnt work,

If IsSubform(frm) Then frm.BttnDelete.Enabled = False

' That is to disable the 'BttnDelete' controls on subform

What I aim to do is disable the 'BttnDelete' control on each subform when Case is "1". I think I'm referencing the control incorrectly.

Appreciate your prompt response.

 

[CJ_London]

per post #6 - if code is in your subform use Me

me.BttnDelete.Enabled=not IsSubform(me)

if it is on the main form

me.subformname.form.BttnDelete.Enabled=not IsSubform(me.subformname.form)

but of course then you would know it is a subform so you would just use

me.subformname.form.BttnDelete.Enabled=false

no need for the function

 

[HillTJ]

@CJ_London my dilemma is that my code as in post #8 is in a stand alone module. I have 2 parent forms that use this code on the formOpen event to set up the user rights of each main form. Within each main form are several tabbed subforms. Each of these also uses the same code on their form OnOpen event. On each subform (continuous forms) is a delete button 'bttnDelete' that i wish to disable for certain users. That is, certain users cannon select the record on the subform & hit the 'delete' key or select the delete control to do same. I do not know how to referrence 'bttnDelete' to disable it. There is no 'bttndelete' on the main form. Hope this makes sense & appologies if you've already answered this ...i'm a bit slow..

 

[The_Doc_Man]

The way I might approach this is simple enough but a bit tedious. Since you have a stand-alone module, you can declare a public variable in that module that would be visible any time once the module has been used i.e. any routine inside that module is called. Such as your common code to set form abilities. So... you could include one or more flags (as many or as few as are needed) to hold / remember / retain a particular status or ability, something that you set or clear when you called that module to set form abilities. If you have one ability, one flag is needed. If you have ten abilities, ten flags are needed. All of these flag variables are in the stand-alone module's Module Declaration area. You set or clear those flags in the "set form abilities" routine. (This is the part that gets tedious.)

Now for each of your sub-forms, there are events you can use such as the Form_Activate event that fires when focus changes from the main form to that sub-form. Not that you'd need it, but Deactivate fires in the sub-form when you click back to the main form.

Inside the sub-form, Me.AllowAdditions refers to the sub-form and yet you can see the public flags you set or cleared earlier, and using the flags for setting or clearing the properties in question and/or enabling or disabling controls as needed according to those flags.

I MIGHT have suggested using the sub-form's Form_Load event except that the sub-form events occur BEFORE the main form's event, so your code to initialize that set of flags would occur in the wrong order. The link below is a reference for you that might help selecting events. It DOES contain a heading on Subform events.

Order of events for database objects - Microsoft Support

support.microsoft.com

 

[HillTJ]

@The_Doc_Man , Yes I could adapt an approach as you suggested & had thought about a technique similar to what you described. But, for now, I've added a one line of code to the on open event of each subform to hide the 'bttnDelete' control. This is easy, but my goal was to try and not repeat any code, hence my preference for trying to incorporate the hide routine in the public code module that I have already developed. Looks like it may be a copy/paste job into the form open event of each subform. Don't like that much!

Cheers & thanks.

 

[Gasman]

Did you look at the link in post #2 ?

 

[HillTJ]

@Gasman, i know the table well. My issue is that i refer to this module from multiple forms. The 'bttnDelete' routine is on each subform only. Id prefer to pass the control name as a variable. Its simple to disable the control on each form load event of the subform, but i was trying to figure out a way of not having to repeat the one line of code on each subform seeing that i was already referencing the form control to set the user permissions thats all.

 

[Gasman]

Well you are passing in the form, and if the delete button is not the same name, then pass that in as well.
I would expect you would have to pass in the subform control name as well?
I have never addressed forms using variables, so someone else is going to have to help with that.

 

[HillTJ]

@Gasman, i'd come to the same conclusion. If have to pass in the control name to the function, then i may as well just add one line of code to each subform to hide the control! I just thought there should be a smarter way. Cheers & you confirmed my thoughts.

 

[The_Doc_Man]

@Gasman, i'd come to the same conclusion. If have to pass in the control name to the function, then i may as well just add one line of code to each subform to hide the control! I just thought there should be a smarter way. Cheers & you confirmed my thoughts.

There is an old principle that I learned long ago regarding relevance and where you place relevant code. It always goes as close as possible to the thing to which it is relevant. The basis of the principle was to resist global solutions to potentially local problems because the opposite approach invites side-effects or complex testing. If you want to do something to sub-forms ABC and DEF but not to GHI or JKL, a solution that is employed from outside of all four sub-forms loses relevance to its function because of the need to recognize that something you wanted to do DOESN'T APPLY to two of the four forms. Even though it means repeating local code now and then, it alleviates the need for extra testing or extra error suppression (when you try to diddle a control that isn't there.) So this problem actually resolves to a "pay me now or pay me later" situation.

Therefore, be aware that "there should be a smarter way" is often a chimera, a beast that doesn't exist if you have selectivity of applicability.

 

[HillTJ]

@The_Doc_Man , yes i agree. Sometimes it may be tempting to invest a disproportionate amount of time into some 'ideal' routine that really does not add significantly to the project. My 'issue' became a somewhat technical issue as i could not figure out how to do what i wished via a public module. I admit defeat & have copied & pasted my one line of code into each of 16 subforms to get the result i needed.

I hate being defeated & hate even more to not understand why i could not achieve my aim. I also hate repetition.

Cheers & thanks for the comments.