Using a reports Record Source in code

[wallis1905]

Hello, all hope somebody can help with this. I have a report, and in its header I want to display field values from a table. This is a different table to the reports records ource. However, the record read from the table depends on a field value from the reports record source. I presume there is a way to extract field values from the reports recordset, making your code "syncronysed" with the code you are executing on each page event for the report. Instead of pulling in the same recorset , used by the report sorted in the same manner to allign the two?

I have tried using,

Dim MyRstReport As Recordset
Dim MyDbs As DAO.Database
Set MyDbs = CurrentDb
Set MyRstReport = Me.Recordset

but it gives an error "This feature is not available in a MDB"
Am I being silly again?

 

[MStef]

Try this:
Dim MyRstReport As DAO.Recordset
.
.

Set MyRstReport = MyDbs.OpenRecordset............

 

[wallis1905]

Yeah I've tried that,
if I use Set MyRstReport = MyDbs.OpenRecordset(me.recordource) it works, but the recordset is not sorted in the same way as the recordset used by the report. I need to ensure that my code is looking at the same record as the report on each new Page event. Is there a way to include the sort order for the report in the sql statement? Thanks for the help....

 

[wallis1905]

Hello again
My readings tell me that access reports do no support the recordset property. This appears to cause problems!
I want users to be able to set the record source for their reports, as well as the group levels. Anybody know how to use these user defined settings to pull a identical recordset through VBA code?
I can use me.recordsource for the name of the query etc, the problem is with sorting.
I know there is me.grouplevel(x) available, but I can't seem to build this into an SQL statement, as I have no way of telling how many group levels are set!
For examle, if three group levels are set, and I check to see if the fourth is set (So I know to stop looping and concatinating the grouplevels to SQL) the program crashes, saying something like "Group level not set"

Any ideas lads???

 

[MStef]

Try this:

Dim Con As Connection
Dim Rs As ADODB.Recordset

Set Con = CurrentProject.Connection
Set rs = new ADODB.Recordset
rs.Open "TableName", Con,................Look at Access help "Open"

 

[wallis1905]

Thanks,
I gave it a try but I get an error on Dim Rs As ADODB.Recordset
"User- defined type not defined". Do I need to use a special reference or something?

 

[MStef]

I have this REFERENCES:

Visual Basic For Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft Activex Data Object 2.1 Library

 

[wallis1905]

Ok,
Well now that resolves that error, but there is more! Now the declarations are not causing problems, but the line Set Con = CurrentProject.Connection gives a type mismatch error. Any idea what the problem is.
Appreciate the effort anyway boss. This seems a lot harder than it should be I think. Surely it's something people need to do often?