Hi!
Being a newbie to reports, I'd like to dynamically fill the textboxes on a report from Recordsets that i've grabbed in vba code. Like this:

me.textboxName.Value = rsPerson.Fields("Name").Value

But the textboxes on a report differ from textboxes on a form, in that they dont seem to be able to set its value dynamically..

Please help me out, as i have 5 recordsets with data i'd like to populate the report with... ;)

You could try the following, the main reason that I use this approach is so that I can create a filter on the fly depending upon the choices made on a form.

Set up a global module with a public variable.

Global Module
Public gstrReportRecordSource As String

In the report set an "on open" event as follows...

Me.RecordSource = gstrReportRecordSource

Just before you call the report make sure that you assign a sql statement to the variable. (Kept my original code below for reference only.) You would do this in the form on the on-click event of a command button.

'i.e. Create Record Source for Report
gstrReportRecordSource = "SELECT BL.DEPOT_CODE, AF.KEYCODE, UCASE(CL.CAMPAIGN_DESC) as CampaignDesc, AF.BEST_BRANCH, " & _
"IIf(BS.BRANCH_TITLE Is Null,BS.ParentBranch,BS.BRANCH_TITLE) AS BranchTitle, BS.ParentBranch, AF.CUSTOMER_ID, AF.TITLE & "" "" & AF.FIRSTNAME & "" "" & AF.SURNAME AS CustomerName, AF.AGE, AF.HOME_PHONE, " & _
"AF.RISK_BAND, AF.MTA_BRANCH, AF.MTA_ACCOUNT, AF.MTA_CLEARED_BAL, AF.MTA_JOINT_ACCOUNT, AF.MTA_ACCOUNT_DESC, " & _
"AF.SUM_CREDIT_CARD_BAL, AF.EXPRESS_PAL, AF.SUM_LOAN, AF.SUM_SAV, AF.SUM_MORT, AF.SUM_MTA, CL.PRIORITY, " & _
"'" & strFileDate & "' AS RUN_DATE FROM ((tblCampaignLookup CL INNER JOIN tblArchivedFile AF ON " & _
"CL.KEYCODE = AF.KEYCODE) INNER JOIN tblBranchLookup BL ON AF.BEST_BRANCH = BL.BEST_BRANCH) INNER JOIN " & _
"tbl_RBS_Branch_Structure_All BS ON BL.BEST_BRANCH = BS.BRANCH_NO ORDER BY BL.DEPOT_CODE;"

'Then you would open the report
DoCmd.OpenReport "ReportName", acViewPreview, , strWhereClause

'The where clause is what I use to apply the filter.

HTH

J.

Thanks Jibbadiah, I will try your solution as soon as possible.
Well,, I've set the global sqlstring now, and in the report_open() sub, i have set: Me.Recordset = strMySQLstring (that is global).

But: How do I assign a textbox a value from within the VB code? A textbox only has a Rowsource, and no "Value" property, as on Forms.

I very much appreciate your code-snippet, but could use with some more code from your report too (If you have the time.)

Thanks in advance Jibbadiah! (Or someone else, just feel welcome joining this thread with info. ;)

Because in the Report_Open() Sub, I've written this:

Me.RecordSource = myGlobalSQLstring

'Then I want to set one textbox like this:
Me.Text0 = Me.RecordSource.Field("Name")
'But it does not work out for me..

Thanks for any tip..

Looks like you would like to specifically change the value of a report field to a specific value from your form - by just assigning the value... not sure that that will work. Believe that you could instead try to filter your recordsource (myGlobalSQLstring) so that it only selects records where a given name is shown from your forms text box. My reports are based on the selections made from 2 list boxes on a form. You can select one/many choices from each list box, or leave both completely blank... the code will then apply the report filter based on the selections made.Will give you a portion of the code to see if you can make sense of it. Have included what the form looks like that launches the report.

Private Sub cmdGenerateReport_Click()
PROC_DECLARATIONS:
Const sProc_Name As String = "cmdGenerateReport"
Dim strFileName As String
Dim strWhereClause As String
Dim i As Variant 'portfolio listbox
Dim strBestBranch As String
Dim ii As Variant 'keycode listbox
Dim strKeycode As String
Dim strFilter As String
Dim intFilterFlag As Integer
Dim dblAnythingThere As Double

PROC_START:
On Error GoTo PROC_ERROR

PROC_MAIN:
strFileName = Me.cbSelectFile.Value

' Create Record Source for Report
gstrReportRecordSource = "SELECT BL.DEPOT_CODE, AF.KEYCODE, UCASE(CL.CAMPAIGN_DESC) as CampaignDesc, AF.BEST_BRANCH, " & _
"IIf(BS.BRANCH_TITLE Is Null,BS.ParentBranch,BS.BRANCH_TITLE) AS BranchTitle, BS.ParentBranch, AF.CUSTOMER_ID, AF.TITLE & "" "" & AF.FIRSTNAME & "" "" & AF.SURNAME AS CustomerName, AF.AGE, AF.HOME_PHONE, " & _
"AF.RISK_BAND, AF.MTA_BRANCH, AF.MTA_ACCOUNT, AF.MTA_CLEARED_BAL, AF.MTA_JOINT_ACCOUNT, AF.MTA_ACCOUNT_DESC, " & _
"AF.SUM_CREDIT_CARD_BAL, AF.EXPRESS_PAL, AF.SUM_LOAN, AF.SUM_SAV, AF.SUM_MORT, AF.SUM_MTA, CL.PRIORITY, " & _
"'" & strFileDate & "' AS RUN_DATE FROM ((tblCampaignLookup CL INNER JOIN tblArchivedFile AF ON " & _
"CL.KEYCODE = AF.KEYCODE) INNER JOIN tblBranchLookup BL ON AF.BEST_BRANCH = BL.BEST_BRANCH) INNER JOIN " & _
"tbl_RBS_Branch_Structure_All BS ON BL.BEST_BRANCH = BS.BRANCH_NO ORDER BY BL.DEPOT_CODE;"

strFilter = ""
intFilterFlag = 0

'Select Contents from Portfolio Listbox
strBestBranch = ""
For Each i In Sortcode_Param.ItemsSelected
strBestBranch = strBestBranch & Sortcode_Param.ItemData(i) & ","
Next i

If Len(strBestBranch) > 0 Then
strBestBranch = Left(strBestBranch, Len(strBestBranch) - 1) ' remove trailing comma and quotes
intFilterFlag = 1
End If

'Select Contents from Keycode Listbox
strKeycode = ""
For Each ii In Prospect_Param.ItemsSelected
strKeycode = strKeycode & Prospect_Param.ItemData(ii) & "','"
Next ii

If Len(strKeycode) > 0 Then
strKeycode = Left(strKeycode, Len(strKeycode) - 3) ' remove trailing comma and quotes
intFilterFlag = intFilterFlag + 2
End If

'Define Filter/Where requirements for recordset and Record Source of Report respectively
Select Case intFilterFlag
Case Is = 0
strWhereClause = "(CUSTOMER_ID) IS NOT NULL"
Case Is = 1
strWhereClause = "(BEST_BRANCH) in (" & strBestBranch & ")"
Case Is = 2
strWhereClause = "(Keycode) in ('" & strKeycode & "')"
Case Is = 3
strWhereClause = "(BEST_BRANCH) in (" & strBestBranch & ") and (Keycode) in ('" & strKeycode & "')"
End Select

'Check that there are records for report, otherwise bug-out

dblAnythingThere = Nz((!Count), 0)

If dblAnythingThere = 0 Then
DoCmd.Hourglass False
MsgBox "No records found.", vbExclamation
GoTo PROC_EXIT
Else:
DoCmd.OpenReport "rptProspectsFromArchive", acViewPreview, , strWhereClause 'open report
DoCmd.Maximize
Reports("rptProspectsFromArchive").Visible = True
Reports("rptProspectsFromArchive").ZoomControl = 80
End If
End With

PROC_EXIT:
' Perform cleanup code here, set recordsets to nothing, etc.
On Error Resume Next
DoCmd.Hourglass False
Exit Sub

PROC_ERROR:
Select Case iErrorHandler(Err.Description, Err.Number, sProc_Name, sModule_Name)
Case iERROR_DEBUG
Stop
Resume
Case iERROR_RETRY
Resume
Case iERROR_IGNORE
Resume Next
Case iERROR_ABORT
Call CloseApplication
Case Else
' Add "last-ditch" error handler.
MsgBox "Error: " & Err.Description
End Select
Resume PROC_EXIT

End Sub