recordset picks up wrong Form

[rick roberts]

I have a main form called frmCustomer and a button that opens a second form called frmOutstanding3. While the second form is open over the first I have a button that prints a report (rptLetter3) for all records in the second form. However the code seems to be choosing records from the underlying form (frmCustomer). If I try the procedure with the first form closed l get a message box asking for the frmCustomer.ID.

Option Compare Database
Dim rs As DAO.Recordset

Private Sub Command13_Click()

Dim stDocName As String
Set rs = Me.Form.RecordsetClone
While Not rs.EOF
stDocName = "rptLetter3"
DoCmd.OpenReport stDocName, acViewPreview, "ID = " & Me.ID

DoCmd.OpenReport "rptLetter3", acViewPreview
rs.Edit
rs!CheckLetter1 = True
rs.Update
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub

Any ideas about whats going wrong please?

 

[pbaldy]

Does something in the report refer to that form? If you close them all and open the report from the database window/navigation pane, does it ask for that form? Also, this line:

DoCmd.OpenReport stDocName, acViewPreview, "ID = " & Me.ID

is not going to increment with the recordset, as I assume you want. It will always refer to the record displayed on the form. My guess is that you want:

DoCmd.OpenReport stDocName, acViewPreview, "ID = " & rs!ID

Of course, in a loop opening it in preview mode, I suspect you'll end up with one instance of the report anyway.

 

[rick roberts]

there is indeed a reference to that form in my query i think i need to clean this up since ive tried so many different ways to make it work
that specific line you refer to has always seemed problematic and ill try your version
youve been a great help - thanks

 

[rick roberts]

i just made the changes as advised and in a flash it all works perfectly -- i hope
thanks again

 

[pbaldy]

Happy to help!