Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    37

    Unanswered: Subform and combo


    Hi
    I have already posted this a couple of days ago and I have still not managed to resolve the problem. I am new access vb and in it right over my head. My linemanageer is not impressed even though i never claimed to be a programmer!
    I am having a main form with a subform and I am trying to to update a text box on the subform according to the selection in my combobox on the mainform.

    I have the following text but it just don't like it.


    Private Sub combo_propnumber_AfterUpdate()
    Forms.sub_test!txtPropNum = Me!combo_propnumber.Column(3)
    End Sub

    Sub_test is my subform
    txtPropNum is the text box on the subform
    Combo_propnumber is the combobox on the mainform

    Help - I am getting rather worked up here!

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Send a short example of your MDB. (Access 2000 or 2002).

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This cannot work because when a form is open as a subform it is not added to the Forms collection, so you cannot address it using Forms!FormName.

    The proper syntax is:
    Code:
    Me.sub_test.Form!txtPropNum = Me!combo_propnumber.Column(3)
    See: Forms: Refer to Form and Subform properties and controls
    Have a nice day!

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by Sinndho
    This cannot work because when a form is open as a subform it is not added to the Forms collection, so you cannot address it using Forms!FormName.

    The proper syntax is:
    Code:
    Me.sub_test.Form!txtPropNum = Me!combo_propnumber.Column(3)
    See: Forms: Refer to Form and Subform properties and controls

    Ummm....I'm able to address a subform open within a main form.

    Forms!MyMainFormName!MySubformName!MyFieldValue = Forms!MyMainFormName!MyComboboxName.column(3)

    works just fine.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    pkstormy,

    Your last post really confused me.

    I have a form F_Parent with a subform F_Child (see attached mdb file). In the Load event of F_Child I place this code:
    Code:
    Private Sub Form_Load()
    
        Dim frm As Form
        Dim i As Integer
        Dim strMsg As String
        
        strMsg = "When subform is loading (Form_Load event of F_Child):" & vbNewLine
        For Each frm In Forms
            strMsg = strMsg & vbNewLine & i & vbTab & frm.Name
            i = i + 1
        Next frm
        MsgBox strMsg, , "Forms present in the Forms collection:"
        
    End Sub
    When opening F_Parent, the messagebox only lists one form present in the Forms collection: 0 F_Parent.

    Now I have a command button on F_Parent with the following code:
    Code:
    Private Sub Command_EnumFormsCollection_Click()
    
        Dim frm As Form
        Dim i As Integer
        Dim strMsg As String
        
        strMsg = "When everything is loaded (command button on parent form):" & vbNewLine
        For Each frm In Forms
            strMsg = strMsg & vbNewLine & i & vbTab & frm.Name
            i = i + 1
        Next frm
        MsgBox strMsg, , "Forms present in the Forms collection:"
            
    End Sub
    When clicked, the message box also lists only one form in the Forms collection: 0 F_Parent.

    If I put a command button with the same code on F_Child, the message box also lists only one form in the Forms collection: 0 F_Parent.

    If I try to reference the form F_Child from F_Parent through the Forms collection:
    Code:
    Private Sub Command_ReferenceSubform_Click()
    
        MsgBox Forms("F_Child").Name, , "Name of the subform (via the Forms collection):"
        
    End Sub
    it causes an error:
    Run-Time error '2450':
    Microsoft Office Access can't find the form 'F_Child' referred to in a
    macro expression or Visual Basic code.
    I've tried this with Access 2003 (11.8166 8221) SP3, with Access 2007 (12.0 6423) and with Access 2000 SP2 but could not succeed in addressing a subform via the Forms collection.

    Do I miss something, is there an option in Access that allows to do that or is this behaviour particular to a specific version of Access?
    Have a nice day!

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There's a couple different syntax ways to reference (and update) a field in a subform. I'm not sure why you need to cycle through the forms collection in the Onload event of a form to update a field value though on a subform. You shouldn't need to cycle through the forms collection but should instead reference the field(s) directly with the correct vba syntax (unless there's a technique being used which allows opening of a form upon a form upon a form to update the same data tables - this coding is usually RARE due to the problems it causes and you typically don't want to open multiple forms based on the same data table as the recordsource! In these cases of coding the me.parent syntax is typically used.)

    Although a subform when open is not added to the Forms collection (and yes, you'll probably get an error on something like this: MsgBox Forms("F_Child").Name), you CAN still access/update field(s) on the subform(s) (providing those fields are updatable) (ie. you can address it using Forms!FormName!SubFormName).

    (as a side-note: if you need to test to see if a form is loaded or not, I'll use the example module/function (by sybex) which is the isformloaded("myformname") = true/false) function. You can check on the Microsoft website regarding this function or I'm pretty sure it's in one of my examples in the code bank (I copy this module in almost all my apps.)

    (Note: Please excuse the spaces I didn't put in the examples below.)

    Regarding referencing a value of a field on your mainform, I'd recommend (to quickly troubleshoot that you're getting the right value), code like this in some event (ie. a button OnClick - ie. your Command_ReferenceSubform_Click button)
    Dim MyVar as variant
    MyVar = Forms!MyMainFormName!MyFieldName (to just check the main value of the combobox or field value for text boxes.)
    or
    MyVar = Forms!MyMainFormName!MyFieldName.column(3) (to check the 2nd column of data in a combobox/listbox's rowsource query).
    or
    MyVar = me.MyFieldName.column(3)

    msgbox "MyVar = " & MyVar

    just to make sure you're getting the correct value from the combobox.

    An example to reference a field on a subform (which I use in my Calendar example in the code bank) is:
    If IsNull(Me!SubFormName) Or Me!SubFormName = "" Then
    Forms(Me!FormName)(Me!FieldName) = Me!WorkDate
    Else
    Forms(Me!FormName).Controls(Me!SubFormName).Form.C ontrols(Me!FieldName) = Me!WorkDate
    End If

    (in the above, I pass the FormName, SubFormName, and FieldName values to unbound fields on a popup calendar form and the code above then writes the date selected (on the calendar form) to the date field on the form or subform depending on whether I populated the SubFormName field when opening the calendar form.)
    The point being is you could possibly use syntax like this:
    Forms(Me!FormName).Controls(Me!SubFormName).Form.C ontrols(Me!FieldName) = Me.MyFieldName.column(3)
    Otherwise, (for example), my code to open the calendar form is:
    docmd.openform "CalPopUp"
    Forms!CalPopUp!FormName = "MyDateFormName"
    Forms!CalPopUp!SubFormName = "MySubFormName_If_the_DateField_Is_On_A_Subfor m" (otherwise = "")
    Forms!CalPopUp!FieldName = "MyDateFieldName"

    (again, see the calendar example in the code bank for an example of how this works.)

    Usually I use Forms!MyMainFormName!MySubFormName!MyFieldName to reference (update) the field on the subform but some older MSAccess versions may not like this and prefer something similar to:
    Forms!MyMainFormName!MySubFormName.Form!MyFieldNam e
    or
    Forms(Me!FormName).Controls(Me!SubFormName).Form.C ontrols(Me!FieldName)

    (again, excuse the spaces I didn't add above.)

    Here's again, a good reference as previously posted:
    Forms: Refer to Form and Subform properties and controls

    Regarding the original code:
    Private Sub combo_propnumber_AfterUpdate()
    Forms.sub_test!txtPropNum = Me!combo_propnumber.Column(3)
    End Sub

    You're most likely going to get an error on this: Forms.sub_test!txtPropNum

    because it should probably be (to reference the field on the subform):

    Forms!MyFormName!MySubFormName!MyFieldName (replacing MyFormName with your form name, MySubFormName with your subform name, and MyFieldName with the field name you want to update) or use the 2nd example above, again replacing the appropriate names.

    and this:
    Me!combo_propnumber.Column(3)

    Could also be referenced instead by using:
    Forms!MyFormName!MySubFormName!combo_propnumber.Co lumn(3)
    or properly
    Me.combo_propnumber.Column(3) <- notice the me. versus me! (but I think the me! should also work in 2003.)

    but I'd still do the MyVar and set MyVar with the msgbox to test to make sure that you're grabbing the right column and data from the combobox on the mainform. You must also make sure the field you're updating CAN be updated on the subform. I've often seen where the problem is that the field itself cannot be updated on the subform.

    (Note: Please excuse the spaces I didn't put in the above examples.)

    Note also: I have rarely ever had the need to put in code to cycle through the form's collection, even for troubleshooting. Especially to try and use to reference a field on a subform.

    Note also - there was no attachment in your post.
    Last edited by pkstormy; 09-21-09 at 22:06.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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