Must textboxes in a report have a rowsource?

[wcoast]

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...

 

[Jibbadiah]

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.

 

[wcoast]

Thanks Jibbadiah, I will try your solution a.s.a.p.

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.

 

[wcoast]

Reply continued.

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..

 

[Jibbadiah]

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