Problem looping Form & Subform while writing to Excel

[Lokoono]

My code below is not cooperating with me. I've opened up an Excel file and I'm trying to write data to the first two columns of the spreadsheet (eventually it will be around 40 columns).

What I'm trying to do is loop through 10 subform records that are tied to 10 or so main records (which = 100 total rows of data in Excel).

The code I'm posting will take care of the 1st main record and the 10 underlying records for it. However, when it gets to the 2nd main record, it stalls at the 2nd subform record in the form with the message "No Current Record" and the Debug highlights the "i = rs(1)" line at the bottom of my code.

I pretty much just guessed with what to type there (most of what I'm posting is based off of my limited "looping through Excel" knowledge).

Could someone please tell me where my code's gone wrong?


Dim rs As DAO.Recordset
Set rs = [subfrmViolations].Form.RecordsetClone
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Open("h:\Violations.xls", 0, False)
Set xlSheet = xlApp.Worksheets("Sheet1")
Do Until txtDirector.Value = "ZZ Top"
For i = 0 To Me.txtNumberOfRecords
xlSheet.Cells(2 + i, 1).Value = [txtDirector]
xlSheet.Cells(2 + i, 2).Value = [subfrmViolations]!DM
If [subfrmViolations].Form.CurrentRecord = Me.txtNumberOfRecords And Me.Form.CurrentRecord = Me.txtRecordCount Then
MsgBox "All Done!"
Exit Sub
Else
If [subfrmViolations].Form.CurrentRecord = Me.txtNumberOfRecords And Me.Form.CurrentRecord < Me.txtRecordCount Then
Me.txtNumberOfRecords.SetFocus
Form.SetFocus
DoCmd.GoToRecord , , acNext
[subfrmViolations].SetFocus
Else
If [subfrmViolations].Form.CurrentRecord < Me.txtNumberOfRecords Then
[subfrmViolations].SetFocus
DoCmd.GoToRecord , , acNext
End If
End If
End If
Next i
i = rs(1)
Loop


Again, the second to last line of the code is where the "No Current Record" error's happening. I'm not sure how to keep it cycling through the main records and the subform's records

Just in case someone's wondering, I can't use the TransferSpreadsheet command because some of the fields on the subform aren't bound to a table and are based on the values that are in current view of the form and subform.

Thanks in advance for any help you can give.

 

[Lokoono]

Nevermind, I figured it out after a few more trial and errors.

I created this line:
Dim x as integer

Replaced:
For i = 0 To Me.txtNumberOfRecords

With:
For x = 1 to 100

And replaced Next i with Next x (while also getting rid of that last line that was causing me headaches).

So I got it working good now. The code stops when it reaches the last record (ZZ Top...which works, but I'll think of a more professional way of stopping it later).

 

[ClulessChris]

I mostly use

Do until rs.eof

Never knock on Death's door: ring the bell and run away! Death really hates that!