Referencing record set fields to a report with unbound fields

[icewiper]

Hello,

I am trying to print out timesheets in access through reports. I am running a record set and can pull the information with no problem doing debug.print. I really don’t work with reports much I am trying to figure away I can reference the record set fields to unbound fields in the report to print the timesheets accordingly.
An example of the unbound field names in the report is rptName, rptEmpID, rptSite, etc.
I tried the Forms!rpt_TS_Emp!rptEmpID.text but still no luck. Any suggestions would be greatly appreciated.
Oh, I have it working with a query but I would really like to get it to work with straight code. Is this possible?

Private Sub ClickButton()

    Dim ddbase As Database
    Dim rs As Object
    Dim sql As String
    Dim SiteBox As String

sql = "Select * from tblSiteSelect where(tblSiteSelect.autoid = 1)"

Set ddbase = CurrentDb
Set rs = ddbase.OpenRecordset(sql, dbOpenSnapshot)
rs.MoveFirst

[green]' the sitebox holds the criteria to pull out the distinct location to print the employees from[/green]
SiteBox = rs(1)

sql = "SELECT tblMainEmp.EmpID, tblMainEmp.LastName, tblMainEmp.FirstName, tblMainEmp.MI, tblMainEmp.PermSiteLoc, tblJobCode.JobCode, tblJobCode.JobCodeBunker, tblJobCode.TeamName, tblsite.Name AS SiteName"

sql = sql & " FROM tblsite RIGHT JOIN (tblMainEmp LEFT JOIN tblJobCode ON (tblMainEmp.TeamName = tblJobCode.TeamName) AND (tblMainEmp.PermSiteLoc = tblJobCode.Site)) ON tblsite.Site = tblJobCode.Site"
sql = sql & " WHERE (((tblMainEmp.PermSiteLoc)= """ & SiteBox & """));"

Set ddbase = CurrentDb
Set rs = ddbase.OpenRecordset(sql, dbOpenSnapshot)
Do While Not rs.EOF

[green]' the Debug.Print works with no problem in the immediate window [/green]
Debug.Print rs.Fields("EmpID") & " " & rs.Fields("LastName") & " " & rs.Fields("PermSiteLoc") & " " & rs.Fields("JobCode")
rs.MoveNext
Loop

DoCmd.OpenReport "rpt_TS_Emp", acPreview, , sql

rs.Close
Set rs = Nothing
Set ddbase = Nothing

End Sub

 

[Crowley16]

you need to set the recordsource of the report to the query string you just generated...

--------------------
Procrastinate Now!

 

[icewiper]

crowley,

I can't believe i over looked that. Works like a charm.

Thanks again