CODEWITHSUNDEEP
vbaformsfunctionms-access
Tom36
Tom36

Reputation: 152

Passing form name to function and loop controls

I have a form call Questions I have a function called LoadQuestions

In the Questions form I have textboxes called WPQ1, WPQ2, WPQ3, WPQ4 etc....

I call the function LoadQuestions using the line:

LoadQuestions (Me.Name)

My LoadQuestions function code is:

Function LoadQuestions(ByVal frm As String)
n = 1   'n is the control number on the form (e.g. WPQ1)
For b = 1 To 6  'b 
Forms!Home!CPQNo = b    

QNUM = DLookup("[QuestionsTbl]", "QuestionTotal", "[Count] > 0") 'Query QuestionTotal gives the total number of questions for the
                                                            'current question set (question set = the value of variable 'b'
On Error Resume Next    'used to catch the instances where there is no value for the DLoopUp to return

    For i = 1 To QNUM       'i is the lower level question number - 1 to Total number of questions from the query

        frm![Controls("WPQ" & n)] = DLookup("[questionText]", "QuestionsTbl", "[NoQuest] = " & b & " And [LowQ] = " & i & "")

        n = n + 1               'add 1 to n as this is the control number which increments by 1 for each control used
    Next i
Next b
End Function

I am getting the error

Qualifier must be a collection

and the code highlighted is the line:

![Controls("WPQ" & n)] =

My question is how do I reference the control on the form? If I run this code as a SUB in the actual form that is using it (with the code

Me.Controls("WPQ" & n) =

then it works fine. How do I get it to work in a function?

Thanks

Upvotes: 0

Views: 39

Answers (1)

Erik A
Erik A

Reputation: 32682

Your frm variable is just a string, not a form. If you want to pass the name of the form, you need to get the appropriate form object before you can use it:

Function LoadQuestions(ByVal formName As String)
    Dim frm As Form
    Set frm = Application.Forms(formName)
    n = 1   'n is the control number on the form (e.g. WPQ1)
    For b = 1 To 6  'b 
    Forms!Home!CPQNo = b    

    QNUM = DLookup("[QuestionsTbl]", "QuestionTotal", "[Count] > 0") 'Query QuestionTotal gives the total number of questions for the
                                                                'current question set (question set = the value of variable 'b'
    On Error Resume Next    'used to catch the instances where there is no value for the DLoopUp to return

        For i = 1 To QNUM       'i is the lower level question number - 1 to Total number of questions from the query

            frm.Controls("WPQ" & n) = DLookup("[questionText]", "QuestionsTbl", "[NoQuest] = " & b & " And [LowQ] = " & i & "")

            n = n + 1               'add 1 to n as this is the control number which increments by 1 for each control used
        Next i
    Next b
End Function

Upvotes: 1

Related Questions