Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244

    Unanswered: Passing Subform Name In Function

    Hello all,

    I have a problem with a function, and although I am sure the answer is staring me in the face I cannot see it.

    What I want to do is to pass the name of a subform to a function so that the function can address fields within the subform.

    The function should also be able to accept the name of a main form, i.e. not a subform, in order to similarly address fields within it.

    Passing the name of a form is fine. But passing a subform name is causing a Type Mismatch error (no. 13). This is because, to Access, a subform within a main form is viewed as a control and not a form.

    Here is the function. It is called FillControls because its purpose is to fill form controls, specifically labels, with values looked up in a table.

    The function takes two parameters: MyForm of object type Form, and Language as the number of the language being passed.

    Public Function FillControls(MyForm As Form, Language As Byte)

    'Set up error handler

    On Error GoTo errhandler

    'Create variables. Ctl will store the control name to fill with a value.

    Dim ctl As Control

    Loop through the controls, check the Tag property, fill accordingly (this section works with no problems)

    For Each ctl In MyForm

    If ctl.Tag = "L" Then

    ctl.Caption = DLookup("ControlText", "TblControlValues", "FormName ='" & FormName & "' AND ControlName = '" & ctl.Name & "' AND Language =" & Language)

    ctl.ControlTipText = ctl.Caption


    End If

    Next

    Exit Function

    errhandler:

    MsgBox Err.Description

    Exit Function

    End Function

    All is well until I pass the name of a subform. I have tried passing it as "Forms!NameOfMainForm!NameOfSubform.Form!" but that still creates an error. I am sure it is possible somehow to pass the name of a form or a subform into the function and have it perform the same operations, but I cannot see an easy way to do it.

    As I say, I'm sure this is easy to resolve (and I think I may even have done it before at some point!) so can anybody help me with this?

    Any assistance would be much appreciated.

    Thank you.

    Andy

    Last edited by andybriggs; 04-23-07 at 14:23.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    does this provide inspiration.

    it is slightly different - it recursively identifies a control (in this example the current control) through arbitrary degrees of subform nesting.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why not try with the data type:
    Code:
    Public Function FillControls(MyForm As String, Language As Byte)
    ...
    For Each Ctl in MyForm
    Code:
    Call FillControls(This (or Me))
    I did something similar a while back - I will try dig the code out for you tomorrow if it's still needed.
    George
    Home | Blog

  4. #4
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    IzyRider - thank you. But it is a little different from what I want to do, and is not inspiring me at the moment!

    Georgev - the problem with your example is that For...Each requires an object or an array to work with, and passing it a string will cause an error. I suppose what is required is to pass the form name as a string yet get the function to see it as a form object. And then somehow tell it that a subform is a form too....

    if you do have any code which might help solve it, I'd be very grateful.


    Thanks very much to you both.

    Andy
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What if you called your procedure an passed the Collection of controls into an Object parameter. Here is some code I came up with:

    Code:
    Public Sub ChangeLabels(colControls As Object)
    
        Dim ctl As Control
        
        For Each ctl In colControls
            
            If ctl.ControlType = acLabel Then
            
                ctl.Caption = "Changed"
                
            End If
    
        Next ctl
        
    End Sub
    Then for a form you would call it like:

    ChangeLabels Me.Controls

    And for a subform, like this:

    ChangeLabels Me.sfrmTest.Form.Controls

  6. #6
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Aha....now that looks as if it might work!

    I will test this and let you know.

    Thank you so much for your help

    Kind regards

    Andy.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  7. #7
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    How bizarre.

    Your method works fine with forms.

    But when passing the controls collection from a subform, I get this error when running the function:

    Run-Time error ‘-2147417848(80010108)’
    Method ‘Form’ of object ‘_Subform’ failed

    Then Access crashes.

    This is Access 2003 by the way.

    Why should this happen?
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  8. #8
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Ok, sorted that one - a quick compact and repair did the trick.

    Thanks so much DCKunkle. Your method worked perfectly. I still need to get hold of the name of the form in which the control resides, for my DLookup in the function, but I can do that.

    I am very grateful for your help.

    Kind regards

    Andy
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •