Referring to multiple form names via code

[Ally]

I am completely useless when it comes to code. I've got some user code (given to me) where I want to give users with a certain LevelID either AllowEdit rights or not.

Rather than put the code in every single form, I'd like to call the function but am getting an error.

My function is:

Public Function fAllowEdits()

Dim ctl As Control, frm As Form, Db As Database, r As Recordset
Dim Lev As Integer, intCanEdit As Integer, Nme As String, frmName As String
    
    [color=blue]Set frm = Forms!frmName[/color]
    Set Db = CurrentDb
    Set r = Db.OpenRecordset("tblUsers")
    Dim frmCurrentForm As Form
    Set frmCurrentForm = Screen.ActiveForm

msgbox "Current form is " & frmCurrentForm.Name


   Call fOSUserName2
   Nme = fOSUserName2
   
   Do While Not r.EOF
   
   If Nme = r.Fields("Username") Then
   Lev = r.Fields("LevelID")
   End If
   r.MoveNext
   Loop
   
   
    For Each ctl In frm.Controls
        With ctl
            Select Case .ControlType
                
                Case acTextBox
                    If Lev = 2 Then
                       intCanEdit = False
                    Else
                         intCanEdit = True
                    End If
            End Select
        End With
    Next ctl
    
    If intCanEdit = False Then
        With frm
            .AllowAdditions = False
            .AllowDeletions = False
            .AllowEdits = False
        End With

    Else
        With frm
            .AllowAdditions = True
            .AllowDeletions = True
            .AllowEdits = True
        End With
    End If

End Function

I have Run fAllowEdits OnLoad of a form.

I get the error message:

"...can't find the form frmName referred to in a macro expression or visual basic code..." with the blue bit above highlighted as the problem bit.

Is it possible to do what I'm trying to do please? Hope I've explained it ok.

 

[ed333]

I would comment out that line and then try changing all your remaining "frm" references to "Me" (referring to the active form)
It looks like you're getting that error because Access doesn't know what form you're trying to reference. The variables are "Dim"ed, but they have no values assigned to them yet.

 

[Ally]

But this code is in the module, so I can't use Me?

 

[FoFa]

I think you should pass the current form reference into your function as a parameter.

 

[Ally]

Gulp. How exactly do I do that please? Have done something similar recently with a query and have got in a complete muddle.

 

[ghudson]

Why don't you use a DLookup to grab the users LevelID. Then test if the user can have edit rights or not in the forms OnOpen event.

 

[Ally]

Would that not mean having the code on each form? There's about 30 in all and I wanted to avoid having to put the code on each form?

 

[Rich]

Set Db = CurrentDb
Set r = Db.OpenRecordset("tblUsers")
Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm

msgbox "Current form is " & Screen.ActiveForm.Name

 

[FoFa]

Going off the top of my head here but I beleive it would be something like this:
Public Function fAllowEdits(formref as form)
sets up the parameter for your function

Then when you call it you should be able to use:
fAllowEdits(Me)
I think since you are in the form when you call it, you can pass Me as the object to it for the reference.

 

[Ally]

Sorry ....

Rich: when I tried yours, I get "Run-time error 2475. You entered an expression that requires the form to be the active window and the
Set frmCurrentForm = Screen.ActiveForm is highlighted.

FoFa: I still get the same error about frmName.

 

[Rich]

Use the Form_Current event

 

[Ally]

Sorry Rich - I get the same message.

 

[Fizzio]

Try this Ally.

Public Function fAllowEdits(frm as Form)

Dim ctl As Control, Db As Database, r As Recordset
Dim Lev As Integer, intCanEdit As Integer, Nme As String, frmName As String

Set Db = CurrentDb
Set r = Db.OpenRecordset("tblUsers")


Call fOSUserName2
Nme = fOSUserName2

Do While Not r.EOF

..........

call function with fAllowEdits(Form) on the Form_Open event.

K

 

[Ally]

That works excellent. Thanks Fizzio...

... *Deleted* - I woke up and realized I'd forgotten to put the extra code in, but now there's another problem.

The first form works fine, and it begins to open a second form, but "... db can't find the procedure "." and it highlights the Run code.

 

[Fizzio]

Make sure the code is in a general module, not behind a form's code

 

[Ally]

The user name code is in a separate module. The Run code is on Open of each (or will be on each) form.

 

[Fizzio]

Sorry, make sure the function fAllowEdits(frm as Form) is in a module, not behind a form's code.

If no joy, make it a Public Sub and change the run code to

Call fAllowEdits(frm as form) and see if this works.

 

[Ally]

The function is in a separate module, but I changed it as you said to a public Sub and it's great. I did have to put (Form) rather than (frm as Form) as it said "expected list separator". Just need to work on the refreshing for the allow edits thing!

Thank you very much for everyone's input and patience!

 

[Fizzio]

*phew* glad we sorted that one before the weekend!