I am writing the following code to pass a recordset in openargs but it displays a message "No Recodset being passed" always.
Any help would be much appreciated.
Code:
Private Sub btnViewReport_Click()
On Error GoTo Err_btnViewReport_Click
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim stDocName As String
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data source=J:\abs\Pennywell\» Case Trackers\DO NOT USE 2010 test version\2003 Backend Database.mdb;"
Set rs = CreateObject("ADODB.Recordset")
rs.Open "tbldocumentrequest", cn, adOpenKeyset, adLockOptimistic
stDocName = "Detailed Report On RequestP2PID"
DoCmd.OpenReport stDocName, acViewPreview, , , acWindowNormal, rs
Exit_btnViewReport_Click:
Exit Sub
Err_btnViewReport_Click:
If Err.Number = 2501 Then
Else
MsgBox Err.Description
End If
Resume Exit_btnViewReport_Click
End Sub
Code in the report as below:
Code:
Private Sub Report_Open(Cancel As Integer)
If Me.OpenArgs <> "" Then
'msgbox OpenArgs
Else
MsgBox "Not recordset being passed."
Exit Sub
End If
End Sub
Not possible to pass a more complex object than a string.
I would suggest, if you are able, to develop a class, create an instance of it, and on the called form reference that global class to receive in the complex object types - like a recordset object.
And if you're not as OOP savvy as our good friend Michael, create a Public ADODB recordset variable and set it in the form's Open event. "Destroy" it in the form's Unload event.
I’m not so sure about ‘OOP savvy’ but it is true that an Object can not be passed through OpenArgs. But it is also true that an Object can not be passed anywhere. An Object is not passed but rather a Pointer to the Object is passed.
The distinction makes a difference.
An Object could be 2 or 3 hundred Mb but the pointer to it is only 4 bytes. (Or maybe 8 in a 64 bit system.) Now we wouldn’t want to go passing 2 or 3 hundred Mb around the place because of the size and time factors involved. So pointers are used to reduce the overhead to the ‘Address Of’ the Object and not the Object itself.
In the code below we pass the pointer to the Object (4 bytes only) through OpenArgs and convert it back to an Object pointer in the Report. The Object pointer in the Report can be Private and therefore there is no need to have a Public Object pointer at all.
Now, because all Object pointers are the same 4 byte size, any Object pointer can be handled the same way, even a pointer to an ADODB.Recordset.
The method of passing Objects (Pointers to Objects) through OpenArgs is a follows:-
Behind the calling Form:-
Code:
Option Explicit
Option Compare Text
Private Sub btnViewReport_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db5.mdb;"
Set rs = CreateObject("ADODB.Recordset")
rs.Open "tbldocumentrequest", cn, adOpenKeyset, adLockOptimistic
DoCmd.OpenReport ReportName:="Detailed Report On RequestP2PID", _
View:=acViewPreview, _
WindowMode:=acWindowNormal, _
OpenArgs:=GetPointerToObject(rs)
End Sub
Behind the Report:-
Code:
Option Explicit
Option Compare Text
Private rs As ADODB.Recordset
Private Sub Report_Open(ByRef intCancel As Integer)
If Len(Me.OpenArgs) Then
Set rs = GetObjectFromPointer(Me.OpenArgs)
Else
MsgBox "Not recordset being passed."
End If
End Sub
In a standard Module:-
Code:
Option Explicit
Option Compare Text
[color=green]' All API's from http://allapi.mentalis.org/apilist/apilist.php[/color]
Private Const POINTERSIZE As Long = 4
Private Const ZEROPOINTER As Long = 0
Private Declare Sub RtlMoveMemory Lib "kernel32" (ByRef Destination As Any, _
ByRef Source As Any, _
ByVal Length As Long)
Public Function GetPointerToObject(ByRef objThisObject As Object) As Long
Dim lngThisPointer As Long
RtlMoveMemory lngThisPointer, objThisObject, POINTERSIZE
GetPointerToObject = lngThisPointer
End Function
Public Function GetObjectFromPointer(ByVal lngThisPointer As Long) As Object
Dim objThisObject As Object
RtlMoveMemory objThisObject, lngThisPointer, POINTERSIZE
Set GetObjectFromPointer = objThisObject
RtlMoveMemory objThisObject, ZEROPOINTER, POINTERSIZE
End Function
Note also that, when the received Object pointer is assigned to a like type Object, Intellisense also works. It does not need to be assigned to a like type Object, it can be late bound. It does not even need a receiving Object pointer since the GetObject(Me.OpenArgs) function can be used in a With Block.
I have documented this method on this site (and other sites) for a while now. It is still not well known but, as time goes by, it may be picked up and used. It then will become more acceptable and the usage will grow. But there will be some people that will regard it as some sort of ‘trick’ as if to say ‘there must be something wrong with it because I haven’t seen it before’. They will not test it; they will simply try to denigrate it.
So, please be aware that the method will be attacked by people who have no understanding of it. They will simply be waiting for some ‘super guru’ to sanction it and then they will embrace it as their own. That, sad to say, is the way things work on the net these days.
So, forget about terms like ‘OOP savvy’ and ‘super gurus’, they will only attempt to distroy what you write and, in the process, slow you down. Do your own testing, decide for yourself, become your own ‘super guru’.
of course, passing a pointer, as opposed to passing a variable or structure is similar to the difference between passing an argument byref and byval
an important distinction is that if you change an item passed byref, you are REALLY changing the stored data, but if you change an item passed byval, you just change a temporary copy, and not the "real" data.
Dave, the point of this thread is to pass a pointer through OpenArgs.
People pass pointers as arguments to procedures all the time but not through OpenArgs.
Most people do not recognise that a line of code such as:-
Dim X As Object
is not dimensioning X as an Object but as a Pointer to an Object.
X doesn’t even become an Object when it is Set to an Object but rather the Pointer is given the Address of an Object. This means that all Pointers are the same size, namely 4 bytes, irrespective of the size of the Object. And that fact is not common knowledge even amongst those who should know.
In this case when the Pointer is passed through OpenArgs it is indeed the same as passing by reference. Changes made to the Object are immediate and therefore those changes do not need to be passed back to the caller. So if, for example, a Dialog Form is opened with a Pointer as OpenArgs then the Dialog Form can write back to the caller and the caller does not need to extract information from the Dialog Form.
But once we get the idea that a true pointer is being passed then it’s all up to the programmer’s imagination how to use it.
Well you and I may understand the principles of OOP and use it in other environments that "fully" support it, but you will find that most Access developers don't.
@ChrisO: That's a good example. There isn't exactly an overhead with using a Public variable in this case? I don't think your example answers the question of passing a recordset via OpenArgs, but it presents a way of referencing and pointing to the memory address of the object through OpenArgs by the aid of a function.
>>I don't think your example answers the question of passing a recordset via OpenArgs, but it presents a way of referencing and pointing to the memory address of the object through OpenArgs by the aid of a function.<<
Well, how would you pass a recordset anywhere? Please give an example.
Now I didn't say you could pass a recordset anywhere Chris I'm just saying that Dim obj as Object doesn't carry with it any overhead until you Set it. Perhaps you could explain the advantage of pointing obj to address location using the interesting method you proposed?
There is very little to explain. A pointer is the address of the Object it is not the Object itself. When we put an address of a house on an envelope we are writing a pointer to the house, we are not putting the house on the envelope.
When we appear to be passing an Object we are not passing the Object at all but passing a pointer to the Object. All the talk about OOP is wasted on me if the people doing the talking don’t know the difference between an Object and a pointer to that Object.
You're more familiar with it than you realise, even though it only implements a limited subset of the principles and functionality VBA could be considered an OO language.
Considering that Access World Forums and others like it are primarilly for the Teaching/Help of others, I wonder why somoeone would use things like OOP. Perhaps this is a good example of Redundant Data.
Years ago I had an employee that sent an important letter to a client that used a lot of OOPs. I ended up giving him the DCM.
You use the principles of OOP all the time, if you removed the functionality from Access, You'd have a lot more confusion.
Think of a recordset, that's a Class Object that handles a set of data. Knowing nothing else about what I'm doing with some data you can tell me, for example:
How to check if I'm at the end of my data
how to move through the records
how to find out how many rows of data I have
How many columns of data I have
How to get data from a Table or a Query into that Recordset
How to search that recordset
How to change the data that's in that recordset (and hence the underlying Table that it refers to)
etc
Because the Recordset class exposes a set of common methods for manipulating data. You neither know, nor probably care, HOW the recordset object achieves that functionality, and in some respects that's part of the point. It's a Black box of functionality that you don't need to worry about.
if everyone had to write their own Recordset object (or other means of manipulating data) whenever they wanted to move data around you'd have a much more interesting time trying to help someone.
Adding references to VBA extends the functionality by making ADO objects, Office Objects etc available to (Access) VBA. Again imagine the fun and games if you have to write your own code every time you wanted to talk to Excel.
