Sending data from a table to a report

[vivamario]

I want to take my data from a table and send it to a report. To do this, I have a small form where I select a couple of inputs to properly filter the data. This data is always changing, the number of selected columns, and rows will never be the same.

The first step is to select my data:


CurrentDb.OpenRecordset "SELECT My Fields FROM My Table WHERE My Filters

I believe that all works fine.

Then I need to build the report. I already have the report page created, it is just blank. This is my best idea so far: sending a bunch of labels and textboxes for each value I selected, but that seems stupid and it doesn't work (2465 error, can't find my field).


Set lblHNum = CreateReportControl(Report Name, acLabel, acDetail, , Label Text, 0, 0, 0, 0)
Set txtHNum = CreateReportControl(Report Name, acTextBox, acDetail, , Field Name, 0, 0, 0, 0)

What is the best way to do this? Building reports is very new to me. Reading material is also good, I don't even know what to google to find the answer I need.

[ranman256]

You dont 'send' data to the report. The report is connected to the table/query, and you just open it.
Build a report,
In report design set the RECORD SOURCE to the table /query.
save

then DOCMD.OPENREPORT "rptMyRpt" ,acViewPreview

[vivamario]

So I set the record source of the query I want to use.

I'm still having trouble building the report programmatically. If I use the code above to make a textbox, I still get the same error I mentioned.

Set txtHNum = CreateReportControl(Report Name, acTextBox, acDetail, , Field Name, 0, 0, 0, 0)
What goes in the 'Field Name' parameter?

[vivamario]

I figured out what I wanted to do:

I took the data from the tables I wanted and sent it to a master temporary table. That table is already linked to my report template that I build in VBA.

I would still love comments on whether or not this is a good idea. It does get the job done.



'Clean Out the table
CurrentDb.Execute "DELETE * FROM Invoice_Data_Temp"
'Build the SQL string
strInitialSql = "INSERT INTO Invoice_Data_Temp(AnchorDesignation, DrillLogType, Start, Finish, TotalDepth) " & _
"SELECT [AnchorDesignation], [DrillLogType], [Start], [Finish], [TotalDepth] FROM Driller_Notes " & _
"WHERE [Start] >= #" & Me.txtBegin & "# And [Finish] <= #" & Me.txtEnd & "# And [DrillLogType] = 'Initial Drill';"
'Execute the SQL string
CurrentDb.Execute strInitialSql
'Build the report
DoCmd.OpenReport "Invoice_Report", acViewDesign
'Set the 'Hole Number'
Set lblHNum = CreateReportControl("Invoice_Report", acLabel, acPageHeader, , "Hole Number", 0, 0, 2000, 300)
Set txtHNum = CreateReportControl("Invoice_Report", acTextBox, acDetail, , , 0, 0, 2000, 300)
txtHNum.Name = "txtHNum"
Reports!Invoice_Report!txtHNum.ControlSource = "AnchorDesignation"
'Set the 'Total Depth'
Set lblTotalDepth = CreateReportControl("Invoice_Report", acLabel, acPageHeader, , "Total Depth (ft)", 2000, 0, 2000, 300)
Set txtTotalDepth = CreateReportControl("Invoice_Report", acTextBox, acDetail, , , 2000, 0, 2000, 300)
txtTotalDepth.Name = "txtTotalDepth"
Reports!Invoice_Report!txtTotalDepth.ControlSource = "TotalDepth"