How to assign the data contained in a recordset in memory to a report? (1 Viewer)

[amorosik]

I have a continuous form, with two fields on each line,
-read_number
-table_name
The form is fed from a recordset that I loaded earlier with the following code
---------------------------------------
Set MyRsAdoDb = New ADODB.Recordset
MyRsAdoDb.Fields.Append "read_number", adInteger
MyRsAdoDb.Fields.Append "table_name", adVarChar,250
MyRsAdoDb.Open , , adOpenDynamic, adLockOptimistic
MyRsAdoDb.AddNew Array("read_number", "table_name"), Array(count, tdf.name)
MyRsAdoDb.Sort = "asc table_name"
Set Me.Recordset = MyRsAdoDb
---------------------------------------
in the form the data are seen correctly, as expected
Now it's time to print and so the question is:
how to pass the recordset as created above to the report (the MyrsAdoDb to understand)?
There is a way to make the report see it as a table/query of the db and therefore the possibility to choose the name of the field on the ControlSource of the textbox placed on the report?

 

[sonic8]

how to pass the recordset as created above to the report (the MyrsAdoDb to understand)?

Unfortunately, there is no way to assign a Recordset to a report. You'll have to write it to a (temporary) table and then base the report on that table.

 

[Minty]

You could create a generic query def modify it and use that as the recordsource.
Alternatively, this method works:

Reports: Use a recordset as report's datasource

access.mvps.org

 

[sonic8]

Alternatively, this method works:
Reports: Use a recordset as report's datasource

It doesn't!
It is not setting the Recordset of the report, it is setting the Recordsource of the report to the first 255 characters of the query that was used to open a DAO.Recordset.

 

[CJ_London]

you could just print the form if it is laid out similar to what you want the report to look like

 

[Pat Hartman]

What is the point of creating a recordset in memory? Why can you not bind the report to a query?

 

[amorosik]

What is the point of creating a recordset in memory? Why can you not bind the report to a query?

Because data are received via network, not resident on db

 

[Pat Hartman]

So, every time you want to open the report again, you need to redownload the data and rebuild the recordset?

 

[amorosik]

No
Every time the data is entered via the network, in the quantity foreseen by the program, a report is started to print

 

[Pat Hartman]

Because data are received via network

What does that mean? Are you working with an Access FE? How does it know when data is entered "via the network"? Entered WHERE?

 

[amorosik]

What does that mean? Are you working with an Access FE? How does it know when data is entered "via the network"? Entered WHERE?

Yes, i am working with Access FE
Data are received via network
Then fed on MyRsAdoDb, like described on post#1
What is not clear?

 

[Pat Hartman]

Data are received via network

I have no clue what that means. Does someone whisper in your ear? Do you pick up the network cable and "feel" the pulsing data? Does someone send you a file? Do you have a TSR "listener" program that receives a message from outer space? How does Access know it has gotten a message?

 

[amorosik]

Ah now I get it, you don't know how to receive information about a front end Access via network
Ok, then follow these simple instructions:
- take a form and name it frmSocketServer
- inside the form you add a texbox and name it txtTextFromOuterSpace
- in Access References you need to add MsWinsck.ocx
- in the form_load event add

Set tcpServer = New MSWinsockLib.winsock
tcpServer.Protocol = sckTCPProtocol
tcpServer.LocalPort = "12345"
tcpServer.Listen

- add a sub to receive the information

Private Sub tcpServer_DataArrival(ByVal bytesTotal As Long)
Dim DATA As String
tcpServer.GetData DATA
txtTextFromOuterSpace= DATA & Chr(13) & Chr(10) & Me.txtTextFromOuterSpace
End Sub

- start the frmSocketServer running
- you place yourself in front of the monitor and wait patiently for the information arriving from outer space, on my monitor I assure you they arrive

"...How does Access know it has gotten a message?..."
Access knows that data has been received when the tcpServer_DataArrival event arrives
It's a very simple system

Then, if you need more information on how to receive data on an Access front end, without going through the db tables, feel free to ask
I know many others
I apologize for not having clarified in the first post how I would receive the information from outer space, but I thought I could assume it, since this information is of no importance in relation to the request initially posed

Is it clearer to you now what I meant?

 

[The_Doc_Man]

Something is not quite right with this. I believe you are thinking of some kind of WinSock event because there is no DataArrival event native to Access. (I just checked the exhaustive list of Form events.)

Now I would EASILY believe that you just open your port and let the form sit there waiting for some type of I/O completion. But you can't declare a new external type of event to Access that wasn't originally part of the form's normal list of events.

When I look this up online, it SEEMS like it is either a VB situation or a .NET situation, but not a VBA situation. There IS no "standard" data arrival event. That perhaps is contributing to at least some of the confusion you are getting as feedback. Our confusion over what you are doing is therefore the reason that we cannot so easily answer you.

 

[amorosik]

I don't understand, do you mean that from an Access form it's not possible to read a web page because it's not a 'native feature'?
I have given just one example, but there are many 'non-native' libraries for Access, and luckily for us
I remember that it is possible to use the Ado functionalities thanks to a 'non-native' library
Or do you mean to tell me that you have never used any external library in your programs?

But this thing has nothing to do with the problem posed
Which I remember is "it is possible to pass an in memory data recordset to a report"

 

[arnelgp]

post #2 is your answer.
after filling your ADODB.Recordset, save it to a Local table and
use the Local table as Recordsource of your Report.

Set MyRsAdoDb = New ADODB.Recordset
MyRsAdoDb.Fields.Append "read_number", adInteger
MyRsAdoDb.Fields.Append "table_name", adVarChar, 250
MyRsAdoDb.Open , , adOpenDynamic, adLockOptimistic
MyRsAdoDb.AddNew Array("read_number", "table_name"), Array(Count, tdf.Name)
MyRsAdoDb.Sort = "asc table_name"
Set Me.Recordset = MyRsAdoDb

' delete old table and create new one
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fd As DAO.Field
Dim rs As DAO.Recordset
Set db = CurrentDb
On Error Resume Next
DoCmd.DeleteObject acTable, "adoRsTable"
On Error GoTo 0
db.TableDefs.Refresh
Application.RefreshDatabaseWindow
Set td = db.CreateTableDef("adoRsTable")
With td
    Set fd = .CreateField("read_number", dbInteger)
    .Fields.Append fd
    Set fd = .CreateField("table_name", dbText, 255)
    .Fields.Append fd
End With
db.TableDefs.Append td
db.TableDefs.Refresh
Application.RefreshDatabaseWindow

Set rs = db.OpenRecordset("adoRsTable")
' save the record to adoRsTable
With MyRsADdb
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        rs.AddNew
        rs!read_number = !read_number
        rs!table_name = !table_name
        rs.Update
        .MoveNext
    Loop
End With
rs.Close
Set rs = Nothing
Set td = Nothing
Set fd = Nothing
Set db = Nothing

Now, Add code to Open event of your report:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "adoRsTable"
End Sub

 

[CJ_London]

if you just need to print it per post#1- perhaps post#5 is all you need to do

 

[amorosik]

post #2 is your answer.

Ok, then it seem that is not possible

 

[amorosik]

if you just need to print it per post#1- perhaps post#5 is all you need to do

Thank you
I know this possibility
But I wanted to understand if it was possible to do what was initially asked

 

[KitaYama]

Ah now I get it, you don't know how to receive information about a front end Access via network
Ok, then follow these simple instructions:
- take a form and name it frmSocketServer
- inside the form you add a texbox and name it txtTextFromOuterSpace
- in Access References you need to add MsWinsck.ocx
- in the form_load event add

Set tcpServer = New MSWinsockLib.winsock
tcpServer.Protocol = sckTCPProtocol
tcpServer.LocalPort = "12345"
tcpServer.Listen

- add a sub to receive the information

Private Sub tcpServer_DataArrival(ByVal bytesTotal As Long)
Dim DATA As String
tcpServer.GetData DATA
txtTextFromOuterSpace= DATA & Chr(13) & Chr(10) & Me.txtTextFromOuterSpace
End Sub

- start the frmSocketServer running
- you place yourself in front of the monitor and wait patiently for the information arriving from outer space, on my monitor I assure you they arrive

"...How does Access know it has gotten a message?..."
Access knows that data has been received when the tcpServer_DataArrival event arrives
It's a very simple system

Then, if you need more information on how to receive data on an Access front end, without going through the db tables, feel free to ask
I know many others
I apologize for not having clarified in the first post how I would receive the information from outer space, but I thought I could assume it, since this information is of no importance in relation to the request initially posed

Is it clearer to you now what I meant?

I hoped someone will ask. But seems everyone has the answer and I'm the only one who has no clue. Ok, I'll step forward and ask it.

How Sub tcpServer_DataArrival is executed? Do you have a timer in your form to fire it?

thanks