Is there a limit to the no. of sub forms I can refer to?

[Steven811]

Hi

The following code works successfully (almost) for mail merging an active form/sub form data to a Word doc.

Using the form/subform faq advice I am now using the correct syntax, I've got it working to the next 2nd subform.

Is there a limit to the number of subforms that I can refer to?

The code I am now using successfully is as follows:

.ActiveDocument.Bookmarks("MyControl").Select
.Selection.Text = (CStr(Me!subform1.Form!subform2.Form!MyControl))

I would like to get to the following:

.ActiveDocument.Bookmarks("MyControl").Select
.Selection.Text = (CStr(Me!subform1.Form!subform2.Form!subform3.Form!subform4.Form!MyControl))

Are there any limitations I should be aware of?

Thanks

Steven811 - Novice

 

[smercer]

I think there is a limit of 5000 forms (subforms included)
is the same with tables, querys and reports.

 

[Steven811]

Can I nest to 4 levels

I think there is a limit of 5000 forms (subforms included)
is the same with tables, querys and reports.

Do you know whether that applies to nesting too?

Thanks

Steven811

 

[Bat17]

from help Access 2003

Attribute Maximum
Number of characters in a label 2,048
Number of characters in a text box 65,535
Form or report width 22 in. (55.87 cm)
Section height 22 in. (55.87 cm)
Height of all sections plus section headers (in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.)) 200 in. (508 cm)
Number of levels of nested forms or reports 7
Number of fields or expressions you can sort or group on in a report 10
Number of headers and footers in a report 1 report header/footer; 1 page header/footer; 10 group headers/footers
Number of printed pages in a report 65,536
Number of controls and sections you can add over the lifetime of the form or report 754
Number of characters in an SQL statement that serves as the Recordsource or Rowsource property of a form, report, or control (both .mdb and .adp) 32,750

Peter

 

[Steven811]

Thanks

Thanks for the post.

 

[WayneRyan]

Steven,

I think that prior to A2003, the max level for subforms is 3.

Wayne

 

[Steven811]

Access 2000

Hi

Thanks for that, although I'm now disappointed having read the previous post.

I use Access 2k and would be grateful if someone could confirm the 3 subforms issue.

Steven811

 

[Steven811]

I've found it

Had a look in help and there it is, 3 levels.

Thanks for that.

Would you upgrade from 2k to 2003?

Regards

Steven811

 

[WayneRyan]

Steven,

Judging from the number of A2003 problems I've seen here, no. I have
seen many reports of "things that worked in A2000" that no longer work
after converting to A2003. Maybe start a poll in the General section
(or Watercooler)?

I have no desire to "upgrade" at this time.

Wayne

 

[Steven811]

Can't quite persuade it to work

Hi

Although A2k limits the use of nesting subforms to 3 levels and this increases to 7 for 2002 and 2003 it still doesn't seem to work.

On another PC I have A2002, I converted the db and it all seems to work fine. When I run the merge I still can't seem to get past the 2nd subform to copy the record set from the 3rd and final subform.

I have followed the advice from the faq syntax for main and subforms.

Any tips would be very welcome.

Steven811

The full code is as follows:

Private Sub MergeButton_Click()
    On Error GoTo MergeButton_Err

    Dim objWord As Word.Application


    'Start Microsoft Word.
    Set objWord = CreateObject("Word.Application")

    With objWord
        'Make the application visible.
        .Visible = True

        'Open the document.
        .Documents.Open ("C:\MyMerge.doc")
        
        'Move to each bookmark and insert text from the form.
        .ActiveDocument.Bookmarks("CompanyName").Select
        .Selection.Text = (CStr(Forms!forCustomerDetails2!CompanyName))
        
        
        'Move to each bookmark and insert text from the form.
        .ActiveDocument.Bookmarks("SiteName").Select
        .Selection.Text = (CStr(Me!forSiteName.Form!SiteName))
        
        .ActiveDocument.Bookmarks("cboDesignation").Select
        .Selection.Text = (CStr(Me!forSiteName.Form!cboDesignation))
        
        .ActiveDocument.Bookmarks("SiteAddress1").Select
        .Selection.Text = (CStr(Me.forSiteName.Form!SiteAddress1))
 
        
        .ActiveDocument.Bookmarks("cboManager").Select
        .Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form!cboManager))

        .ActiveDocument.Bookmarks("Contact11stName").Select
        .Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form!Contact11stName))

        .ActiveDocument.Bookmarks("Contact12ndName").Select
        .Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form!Contact12ndName))

        .ActiveDocument.Bookmarks("JobNo").Select
        .Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form!forJobTracking2.Form!JobNo))


        .ActiveDocument.Bookmarks("DateofComment").Select
        .Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form!forJobTracking2.Form!forProject.Form!DateofComment))
        
        
'    '        .ActiveDocument.Bookmarks("JobNo").Select
'        .Selection.Text = (CStr(Me.Form!forJobTracking2!JobNo))

'        .ActiveDocument.Bookmarks("Comments").Select
'        .Selection.Text = (CStr(Me.forSiteName.Form!forProject.Form!Comments))
'
'        .ActiveDocument.Bookmarks("Action").Select
'        .Selection.Text = (CStr(Me.forSiteName.Form!forProject.Form!Action))
'
'        .ActiveDocument.Bookmarks("ActionByDate").Select
'        .Selection.Text = (CStr(Me.forSiteName.Form!forProject.Form!ActionByDate))
'
'        .ActiveDocument.Bookmarks("ByWhom").Select
'        .Selection.Text = (CStr(Me.forSiteName.Form!forProject.Form!ByWhom))
        
'           'Move to each bookmark and insert text from the form.
'        .ActiveDocument.Bookmarks("DateofComment").Select
'        .Selection.Text = (CStr(Me.forProject2.Form!DateofComment))

        
'        .ActiveDocument.Bookmarks("JobNo").Select
'        .Selection.Text = (CStr(Me.Form!forJobTracking2!JobNo))
        
'        .ActiveDocument.Bookmarks("cboManager").Select
'        .Selection.Text = (CStr(Me!forContactsJobTracking.Form!cboManager))
'
'        .ActiveDocument.Bookmarks("Contact11stName").Select
'        .Selection.Text = (CStr(Me!forContactsJobTracking.Form!Contact11stName))
'
'        .ActiveDocument.Bookmarks("Contact12ndName").Select
'        .Selection.Text = (CStr(Me!forContactsJobTracking.Form!Contact12ndName))
        

'       Me.forProject2.Form.SetFocus

'Me.forProject2.SetFocus
'Me.forProject2!DateofComments.SetFocus
'
'
        'Move to each bookmark and insert text from the form.
        .ActiveDocument.Bookmarks("DateofComment").Select
        .Selection.Text = (CStr(Forms!forProject2!DateofComment))
        
        .ActiveDocument.Bookmarks("Comments").Select
        .Selection.Text = (CStr(Forms!forProject2!Comments))
        
        .ActiveDocument.Bookmarks("Action").Select
        .Selection.Text = (CStr(Forms!forProject2!Action))
        
        .ActiveDocument.Bookmarks("ActionByDate").Select
        .Selection.Text = (CStr(Forms!forProject2!ActionByDate))
        
        .ActiveDocument.Bookmarks("ByWhom").Select
        .Selection.Text = (CStr(Forms!forProject2!ByWhom))



    End With

    'Print the document in the foreground so Microsoft Word will not close
    'until the document finishes printing.
'    objWord.ActiveDocument.PrintOut Background:=False

    'Close the document without saving changes.
'    objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'
'    'Quit Microsoft Word and release the object variable.
'    objWord.Quit
'    Set objWord = Nothing
'    Exit Sub

MergeButton_Err:
    'If a field on the form is empty, remove the bookmark text, and
    'continue.
    If Err.Number = 94 Then
        objWord.Selection.Text = ""
        Resume Next
End If

    Exit Sub
End Sub