VBA Question on Detail Line 1

[benaround2]

In creating a recordset for use in a MS Access 2000 report, I am going through the recordset OK. I know this because I use debug.print and everything looks good.

I take the query results and put them into unbound fields on the report but the the report only displays the last line.

As a FYI, here is the attached code:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "query1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable

rs.MoveFirst
Do While ((Not rs.Status) And (Not rs.EOF))
[FormName]!fielda = rs.Fields("fieldaID")
rs.MoveNext
Loop

rs.Close


BTW, this loop also does not stop at EOF. If you know that fix, thanks double.


Can you give me the line of VBA code to display the detail line on a report?

Thanks ahead of time

 

[lupins46]

This looks like hard work.
Why can't you base your (bound) report on query1?

 

[RoyVidar]

I'd say .eof is sufficient to test for eof ...

Access reports - mdb's that is - are constructed to use recordsource, and works fairly well with that, why not just assigne "query1" as the recordcource and use bound controls in stead?

In 2002+ versions, using ADP, you can assign recordsets to reports, but again you need
* ADP
* 2002+ version

Else you need to "draw" the report. There should be some samples in the report forum (forum703), here's a very simple sample thread703-1105456.

Roy-Vidar

 

[benaround2]

In reference to q1: I was using recordsets because I need to lookat all rows in a table, based on a criteria in many fields, I would "print out" beginning inv # and ending inv # on the same output line.

This "stage" of the report is not the final version for the end-user.

In reference to Reporting and recordsets, I cant use recordsets and reporting with Access 2000? Please say this is not so?

I guess then I have to build tables ahead of time. Let me know. Thanks for your help.

 

[rubbernilly]

1) You are only printing the last line because you are constantly overwriting the data in the fields. When you arrive at the last record, you are left with that data in the fields.

2) You can have dynamic SQL with a Report and Query. The same way you would dynamically construct the SQL that made up your Recordset (looking at the table, as you say, and making comparisons), you can then take that SQL and apply it to the QueryDef object.

dim qd as DAO.QueryDef
set qd = CurrentDB.QueryDefs("YourNewQueryName")
qd.SQL = strYourSQLString

Leave the query created and in the database, and base your report off of it. Once you have the SQL applied to the query, you can step through the Fields() collection to dynamically bind your report fields, I believe.

Open the report in design view, hidden from the user, and set the properties for the various fields that you need to set, including (most importantly) the field that each is bound to.

Then open your report in print preview mode, visible to the user, and you should have all of your data.