How to base Report Recordsource on Recordset 1

[webber]

I have an interesting problem that I haven't been able to find an answer to.

Basically, I would like to base a report's recordsource not on a table or query buton a VBA recordset. So the data that the report is based on would come from a recordset.

I have created an ADP in Access 2000 using SQL Server as the backend. The ADP is disconnected from the server per a recommendation in Microsoft Access Developer's Guide to SQL Server. That means that when I retrieve data I use code to connect to the server, load or save the data then disconnect. It works, and it's quick and very scalable.

Ok, the problem is that with reports, I'm not sure how to load the data in except via a recordset, which is filled from an SQL stored procedure. There are no tables or queries in the application...all that is on SQL Server and I connect via code to get them.

Note that because ADPs don't use Jet, I can't simply create a temp table then base the report on that. Thus my question regarding report recordsource from recordsets.

Thanks for any help with this. It's pretty much stumped me.

Webber

 

[Junior1544]

sounds like you know what your doing here, so i'll be short and sweet

use
Me.RecordSource = recordset_name

in the on open report event procedure...

personaly, i use linked tables to an access backend and in this one report, it used to be called by editing the report to modify filters in the report, then saving and closing it... i had to redesign it to base it on a recordset to turn the database front end into an mde... i had a hard time writing the conditional sql statment... (my first time doing it)

well, hope this helped you... if you need more help with it feel free to contact me...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[webber]

Thanks! I did try that and it didn't seem to work. Actually, I couldn't even get it to compile (says compile error, type mismatch). Here's the code:

Private Sub Report_Open(Cancel As Integer)

'-- Initialize
Dim rst As ADODB.Recordset
Dim fOK As Boolean

' Connect to appl
fOK = OpenConnection()
If fOK = False Then
MsgBox "Unable to Connect", , _
"Can't connect to the database."
Forms!Main.Visible = True
End If

' Create recordset from SQL Stored Procedure
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open Source:="EXEC inRpt_GenActiveList_WLegalWardStatCurr_sp", _
ActiveConnection:=gcnn

Me.RecordSource = rst


End Sub

Not sure what I am missing. Tried to set me.recordset to rst.name, but with no luck.

Thanks!

Webber

 

[Junior1544]

the type mismatch is that your tring to make a connection type variable into a Boolean variable...
you need to have the sql the makes the recordset go strait into the recordsouse statement... not into a recordset, and then into the recordsource...

--Junior (sorry for my confustion, i have just done this one time...)


Private Sub Report_Open(Cancel As Integer)

'-- Initialize
Dim MySQL As String

MySql = "put sql statment in here that will return the records you want."

Me.RecordSource = MySql


End Sub
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Junior1544]

here is an exact example strait from my active database...

hope this information helps...



Private Sub Report_Open(Cancel As Integer)

'-- Initialize
Dim MySQL As String

MySql = &quot;SELECT inventory.* FROM Sechist WHERE(((inventory.REC_ID)<> null ));&quot;

Me.RecordSource = MySql


End Sub
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[tombergevin]

I tried something similar with a problem I'm having. I got the recordset to work, but the data does not appear in my report text boxes. All I get is #NAME? in each box.

 

[Junior1544]

show me your code... and i'll take a look...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[webber]

Junior:

Thanks for your help. I can assign an SQL string easily enough, but I need to go right from a recordset to a recordsource. That is, use ADO to get the records and not attached tables etc.

Hope this helps...

Webber

 

[Junior1544]

i'm not sure if what it is you want to do is possable... i can work with you to get a sql string to be your source, and even getting the sql to be dynamic, if you have one or 5 requirments... i have a report that i have 6 different filters for, and any where from one to 6 of them may be used... the sql didn't like my zero lenth strings, so i made some if statments to add some sql together depending on if the field was filled on my selection form... it's not hard to do it that way... it just takes a little working with the code to get it to work right...

how is it that you are figuring out the recordset?? is it sql, or coping from a query... i'm not exacly sure what you are tring to do, but if i can help i would be happy to... you can contact me directly if you want to with what it is you need...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.