Repeating unbound subreport in main report detail

[Carol G]

Hi all,
This is my first attempt with an ubound report. Using Access 2002.

My main report is bound to a table that has two fields: one has the
SQL string I want to use, the other some heading text. Both of these
fields are bound to text boxes on the detail of the main form.

My subreport is unbound and embedded in the detail of the main report.
Data is entered into unbound text boxes via public functions that set
and get data from the DAO recordset, which is created on the subform's
open event, using a sql string it gets from the main form text box. No
recordsource is assigned to this form, although I do use
Me.NextRecord=False to keep the detail printing.

The subform works fine independent of the main form. When I do a
preview, only the first instance of the subform prints correctly. The
other instances only print the last record. Anybody know what is going
on and/or have some suggestions?

Thanks,
Carol G

Code from subform below:
__________________________

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Err_Detail_Format

'If Val(Me.Tag) = 0 Then -- This is hardcoded into the property now
If Not rs.EOF Then
set_string1 (rs.Fields(0))
set_string2 (rs.Fields(1))
set_string3 (rs.Fields(2))
rs.MoveNext
Me.txt1 = get_string1()
Me.txt2 = get_string2()
Me.txt3 = get_string3()
If Not rs.EOF Then
Me.NextRecord = False
Else
Me.NextRecord = True
Me.Tag = 1
End If

End If

Exit_Detail_Format:
Exit Sub

Err_Detail_Format:
MsgBox Err.Description
Resume Exit_Detail_Format


End Sub


Private Sub Report_Close()
Set rs = Nothing
Set db = Nothing
'DoEvents -- didn't help

End Sub

Private Sub Report_Open(Cancel As Integer)
'test string below for opening w/out main report
'strSQL = "SELECT Place.Assigned_To, Systems.Make, Systems.SerialNo
FROM
'Place INNER JOIN Systems ON Place.Place_Key = Systems.PlaceKey;"
strSQL = Reports!rptqueryText.txtQryText
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst

Me.Tag = 0

End Sub

 

[SA]

Carol:

Understand that the open event only happens once for the sub report, not
each time it is displayed. As a result, you have your recordset
instantiation as welll as the MoveFirst code in the On Open event; it only
first once, and then the recordset moves on the first display through to the
last record and stays there. You might try creating a group header
(possibly using the =1 basis for the group) and refresh the recordset there.

 

[Carol G]

Steve,
OK, that certainly explains what I am seeing on screen. My logic was
off. I'll move the recordset creation to a header event and see what
happens. If that doesn't work, I am getting some other ideas even as I
type.

Thanks so much!
Carol G