Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002

    Unanswered: Subform within a Subform

    Hi there,

    I have a main form Called [JSAsMainContactForm]
    It has a subform called [usysbPAGESDetails2Sites]
    and this subform has a subform called [usysbPAGESDetails3Contacts]

    I have an on click button event that has the code below. The button is placed on the subform called [usysbPAGESDetails3Contacts]

    I keep on getting the following error message:
    Microsoft Access Cant find the field 'usysbPAGESDetails2Sites' referred in your expression.

    I want to:
    'Check to see if the both subforms are new records. I.E. If child record (subform - [usysbPAGESDetails2Sites]) is a new record and if (subform -usysbPAGESDetails3Contacts) is a new record.

    If yes then copy some details from parent record (JSAsMainContactForm) to the child record (subform - usysbPAGESDetails3Contacts).

    I have tried a number of approaches but cant get anything to work.

    This is the code I currently have:

    If (IsNull(Forms![JSAsMainContactForm]![usysbPAGESDetails2Sites]![usysbPAGESDetails3Contacts].Form.[SiteContID]) And IsNull(Forms![JSAsMainContactForm]![usysbPAGESDetails2Sites].Form.[PAGESiteID])) Then
    Call MsgBox("Site details must be completed before you can Copy Head Office Details to Site Contact Details. " _
    & vbCrLf & "" _
    & vbCrLf & "Click on OK then complete site details. " _
    , vbExclamation, "SITE DETAILS NOT COMPLETED")
    GoTo Exit_CopyHOContBtn_Click
    End If

    Can anyone provide a solution?

    Thanks heaps.

    Karen Day
    Last edited by Karen Day; 12-05-09 at 08:46. Reason: make my problem clearer

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    just a guess
    do you really need to use this design, or would you be better off using a tabbed dialogue
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    Dear Karen,

    An interesting way of handling this consists in having each subform to return the required data to its parent. That way you can perform the tests at the Main Form level)

    In my example, there are three forms: F_dbo_CF_DATA (level 0: MainForm), F_dbo_LCF_Data (level 1: SubForm) and F_dbo_LCF_Acc_Data (level2: Sub-SubForm) where:
    - F_dbo_LCF_Acc_Data is a subform of F_dbo_LCF_Data
    - F_dbo_LCF_Data is a subform of F_dbo_CF_DATA.

    In F_dbo_CF_DATA I created two properties:
    -SubForm_PK that must hold a primary key from its F_dbo_LCF_Data subform
    -SubSubForm_PK that must hold a primary key from the F_dbo_LCF_Acc_Data subform of its F_dbo_LCF_Data subform (the box in the box in the box).

    In F_dbo_LCF_DATA I created one property:
    -SubForm_PK that must hold a primary key from its F_dbo_LCF_Acc_Data subform.

    On its Current event, F_dbo_LCF_Acc_Data will assign its SysCounter (primary key) value to the SubForm_PK property of its parent form (F_dbo_LCF_Data) and on its Current event, F_dbo_LCF_Data will assign its SysCounter (primary key) value as well as the value of its SubForm_PK property to the SubForm_PK and SubSubForm_PK properties of its parent form (F_dbo_CF_Data)

    It seems to be horribly complex but its actually quite simple (less than 20 lines of code). You can easily generalize the principle.

    Have a look at the attached db to see it working.
    Attached Files Attached Files
    Have a nice day!

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 14

    Come to think of it, you can even simplify the code by dropping the SubForm_PK property of the level 1 subform (F_dbo_LCF_DATA) and return the SysCounter value from the level 2 F_dbo_LCF_Acc_Data form directly to the SubSubForm_PK property of the main (level 0) form F_dbo_CF_DATA.

    The code becomes:
    ' In F_dbo_LCF_Acc_Data module
    Private Sub Form_Current()
    '    Me.Parent.SubForm_PK = Me!SysCounter
        Me.Parent.Parent.SubSubForm_PK = Me!SysCounter
    End Sub
    ' In F_dbo_LCF_Data module
    Private Sub Form_Current()
    '    Me.Parent.SubSubForm_PK = m_varSubForm_PK
        Me.Parent.SubForm_PK = Me!SysCounter
    End Sub
    Have a nice day!

  5. #5
    Join Date
    May 2002

    Subform within a Subform

    Hi Sinndho

    I tried down loading the example database but it just will not work for me. Keeps on freezing or comming up with errors. I may be using an earlier version of Access than you and this might be causing the problem.

    I don't understand the use of "Me!SysCounter" nor the way the code you have suggest works. So I have not utilized this suggestion.

    BUT when you talked about having each subform to return the required data to its parent, this reminded me that I already was keeping the parentID within each subform. This as you stated, then meant that all I had to do was use the me.[controlname] (ie the ID of both the subform and the parent form) to base my If statement on.

    Once again I could not see the trees for the forrest!
    I cant believe I just wasted 20 hours trying to get this to work the other way only to have it work so quickly now.

    You know I love your assistance. Thank you so much once again.

    Perhaps one day when you have nothing better to do, you could explain the use of "Me!SysCounter".


Posting Permissions

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