Disconnected Recordset and Reports

[timb94]

Hi all

I'm starting to play around with disconnected recordsets to see if they can be used in any of my applications.

The recordset is built in a common module. I can create a form and display the data but I can't get it to work in a report. Code is below:

Public Sub BuildRS
Dim rsCount as DODB.Recordset
Dim lngVarChar As Long
lngVarChar = 50
rsCount.Fields.Append "Division", adVarChar,lngVarChar
rsCount.Fields.Append "TotalCount", adInteger
rsCount.Open
rsCount.AddNew
rsCount!Division = "Home"
rsCount!TotalCount = 5
End Sub

I created a report with two unbound fields. In the reports open event I entered the following:

me.division = rsCount!division
me.totalcount = rsCount!totalcount

When I run the report it tell me that I cannot assign a value.

Is it possible to use a disconnected recordset in a report and if so how is it set up?

Thanks for any help or suggestions.

 

[JoeAtWork]

Unfortunately, you can't bind a report to a disconnected recordset like you can with a form.

For what you want to do, you could make public functions that return the value you want. Then in the query that the report binds to, add fields that call the public functions, e.g.

SELECT Field1, Field2, MyField: MyFunction() FROM MyTable

 

[timb94]

Thank you JoeAtWork

I tried your suggestion and it works ok. Fortunatly there were only 6 fields and 1 record that I was dealing with.

Thanks again.

 

[CautionMP]

timb94,
This might help. The code below came from a report I developed several years ago. The report has no record source, it uses an ADO recordset that is returned as a property of [tt]frmMemberLC_Main[/tt].

Dim rstReportData As New ADODB.Recordset
Dim curCumulative As Currency
Dim intCounter As Integer

Private Sub Report_Open(Cancel As Integer)
  Set rstReportData = Forms!frmMemberLC_Main.rstLCTrans
  rstReportData.Sort = "Obligation"
  rstReportData.MoveFirst
  intCounter = 0
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
  Me.txtCustomerName = Forms!frmMemberLC_Main.txtCustomerName & _
  " Obligor: " & Forms!frmMemberLC_Main.txtObligor
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Detail
  Me.txtObligation = rstReportData.Fields("Obligation")
  Me.txtTranCd = rstReportData.Fields("Tran_Code")
  Me.txtGlobalAmt = rstReportData.Fields("Global_Amt")
  Me.txtPSObgn = rstReportData.Fields("Eff_Date")
  Me.txtType = "New"
  Me.txtPSAmt = rstReportData.Fields("Global_Amt")
  curCumulative = curCumulative + Me.txtPSAmt
  intCounter = intCounter + 1
  rstReportData.MoveNext
'Detail end
If Not rstReportData.EOF Then
  Me.MoveLayout = True
  Me.PrintSection = True
  'There is no Report!Recordset so don't try to move
  Me.NextRecord = False
End If
End Sub

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
  Me.txtFPSObgn = "Net Dummy Adj:"
  Me.txtFTranCd = "5521"
  Me.txtFPsAmt = Abs(curCumulative)
End Sub

Private Sub Report_Close()
  Set rstReportData = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)