Cycling through subform Records

[paddon]

Hey all,



I'm having problems seeing what I am doing wrong here. I have a form (RentRoll), which has a subform (sfRentRoll) contained within it. The form RentRoll get's it's data from the "Property" Table and the sub-form sfRentRoll from the "Lease" table.

Within the "Lease" table there is a look-up field "Property" which relates the leases to a specific property by it's "ID".

I linked the Master Field "ID" on the RentRoll form to the Child Field "Property" on the subform. Everything displays fine within the form.

For reporting purposes, however, I would like to cycle through the records showing on the subform (i.e. the leases for that specific property) and print to an excel template. Here is the test code I have so far, most of which I obtained through various google searches.

Private Sub Command14_Click()
    Dim objXLApp As Object
    Dim objXLBook As Object
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("P:\Jeff Paddon\Database\Reports\RentRoll.xltx")
    objXLApp.Application.Visible = True
    objXLBook.ActiveSheet.Range("A1") = Me.Field1.Value
    
    Dim tmp As Variant
    Dim tmprs As Variant
    Set tmprs = sfRentRoll.Form.Recordset
    Set tmp = sfRentRoll.Form.Controls

    If Not tmprs.BOF Then
        tmprs.MoveFirst
    End If
    While Not tmprs.EOF
        objXLBook.ActiveSheet.Range("A9") = tmp.Tenant.Value
        objXLBook.ActiveCell.EntireRow.Insert
        tmprs.MoveNext
    Wend
    
    tmprs.MoveFirst

End Sub

I've never done this before, nor have I ever tried to use recordsets within subforms so I probably am just misunderstanding how they are accessed.

Any help or pointers in the right direction would be greatly appreciated!

Thanks very much,
jpaddon

[ajetrumpet]

you need to use "me.subform.recordsetclone". That'll be in the intellisense dropdown, if you've typed it correctly.

and then simply run your loop. it's that simple. If I'm remembering correctly!

[NTC]

I tend to feel that the concept of "cycle through the records showing on the subform " is code centric rather than database centric.

The db approach, imo, is to make a query that results in the same records as the subform. Then trigger an export to excel of that record set.

Hope this helps.

[paddon]

Thanks for all the help guys. I have figured it out I think!

There were a couple of problems, one even being that I had mis-named my sub-form... silly. Here is the code that I am using to accomplish what I mentioned above and it's working like a charm.

Private Sub Command14_Click()
    Dim objXLApp As Object
    Dim objXLBook As Object
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("P:\Jeff Paddon\Database\Reports\RentRoll.xltx")
    objXLApp.Application.Visible = True
    objXLBook.ActiveSheet.Range("A1") = Me.Field1.Value
    
    Dim tmprs As Variant
    Set tmprs = Me.sfTable1.Form.Recordset
    Dim count As Integer
    count = 0
    
    If Not tmprs.EOF Then
        tmprs.MoveLast
    End If
    While Not tmprs.BOF
        
        objXLBook.ActiveSheet.Range("A9").EntireRow.Insert
    
        objXLBook.ActiveSheet.Cells(9, 1) = Forms!RentRoll!sfTable1.Form!Tenant.Value
                
        objXLBook.ActiveSheet.Cells(9, 2) = Forms!RentRoll!sfTable1.Form!Suite.Value
                
        objXLBook.ActiveSheet.Cells(9, 3) = Forms!RentRoll!sfTable1.Form!Area.Value
                
        objXLBook.ActiveSheet.Cells(9, 4) = Forms!RentRoll!sfTable1.Form!LeaseStart.Value
                
        objXLBook.ActiveSheet.Cells(9, 5) = Forms!RentRoll!sfTable1.Form!LeaseExpiry.Value
                
        objXLBook.ActiveSheet.Cells(9, 6) = Forms!RentRoll!sfTable1.Form!Rate.Value
                
        objXLBook.ActiveSheet.Cells(9, 7) = "=F9*C9"
                
        objXLBook.ActiveSheet.Cells(9, 8) = Forms!RentRoll!sfTable1.Form!RenewalOptions.Value
        
        
        tmprs.MovePrevious
        count = count + 1
    Wend
    
    objXLBook.ActiveSheet.Cells(count + 10, 7) = "=sum(G8:G" & count + 9 & ")"
    objXLBook.ActiveSheet.Cells(count + 10, 3) = "=sum(C8:C" & count + 9 & ")"
    
    objXLBook.ActiveSheet.Cells(count + 14, 7) = "=sum(G" & count + 10 & ":G" & count + 13 & ")"
    objXLBook.ActiveSheet.Cells(count + 18, 7) = "=sum(G" & count + 14 & ":G" & count + 17 & ")"
    
    tmprs.MoveFirst
    
    objXLBook.SaveAs "P:\Jeff Paddon\Database\Reports\" & Me.Field1.Value & "-" & Format(Date, "yyyymmdd") & ".xls"

End Sub