Populate report fields with SQL? Is it possible?

[VirginiaMan]

I am using Access 2003 and am creating a report. The report will have about 400 fields/calculations on it. Since the report must display so much data, I did not want to populate the fields using domain aggregate functions such as: DSum("Balance","LoanTable"). Is there a way to populate fields on a report using SQL? Or can I create custom VBA functions (maybe using SQL) to return values to the fields? Domain aggregate functions may be the best way to go, but I am not sure. Anyone have advice?

 

[dhookom]

You may be able to create a recordset in code and place values into unbound text boxes or just print them to your report.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[VirginiaMan]

Thanks for the idea! Would I use the reports On Open event to populate the fields? and use something like "GetRows" to return a value from the recordset? Such as the following:

Private Sub Report_Open(Cancel As Integer)
Dim rst As ADODB.Recordset
Dim GrabValue As Variant

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.Open "SELECT Sum(LoanBalance) FROM
LoanTable", , , , adCmdText

GrabValue = rst.GetRows()
End With

and then use something like

Me.txtLoanBalance = GrabValue

to populate the report textbox.

End Sub

Does this make sense or is there a better way?

 

[dhookom]

I would expect something like:
Me.txtLoanBalance = .Fields(0)
I don't use ADO much as I am still practicing DAO. There wouldn't be much performance difference if all you want to do is grab a single values from a recordset for a single text box.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[VirginiaMan]

Thanks for the feedback!