Can a Select query feed an Access Report? 2

[rodgerfields]

I've been fighting this for days and can't make it work. Now it appears I may be trying something that's not allowed!

The simple question is: Will Access allow me to run a stored Select query and return the result to a text box in the report. If so, how?

I'm doing this is the Detail_Format section of the Report code; maybe that's my problem.

I'd love to have one query pass all the results into their respective text boxes, but right now, I'd settle for doing one query passing one result to one text box!! (Don't shoot me, guys! I know how bad that sounds! But I'm desparate.)

Among the 10-12 things I've tried are:
- putting the query text in the text box Control Source
- putting the query in the code and returning it to the
report via "me.(txtboxname) = curResultOfQuery"
- opening the database in the code and running the query
(which I can't get to work AT ALL)

Obviously, there's a fundamental piece of this puzzle that I missed.

Any suggestions?

 

[danvlas]

1. Have you based your report on your query?
2. Or you need to have the report based on one query and fill just a tiny text box, at run-time, with some totally different data?

1. Display the field list and drag the necessary field to the desired place on the report.

2. Use the Format event of the section where you have your textbox.

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("YourQueryName&quot
Me("yourTextBoxName&quot = rst("ResultFieldName&quot
rst.Close
Set rst = Nothing
End Sub

I wrote this for the report header, but it works for any report section.

HTH

Dan


End Sub

 

[rodgerfields]

Thanks VERY much, Dan. That did the trick!!

I think I was mixing apples and oranges (and throwing in a little tangerine!!). For example, I think I was using DAO syntax in an ADO line of code... that kind of stuff. (Any suggestions on books or other training resources to help me sort that stuff out?)

Thanks again. I owe you!!!
Rodger Fields
Austin, Texas