setting public objects of parent form

[SjaakBanaan]

Hey guys (m/f),

I've got a problem that's been having me pulling out hairs and losing sleep quite some time now.

The thing is this. I have a container form with a subform control. This control can take several sub forms as its source object. To make the information, that is generated on one sub form, available to a later sub form, I'm trying to store data in public variables and objects of the container form.

In other words, I'm using a form (i.e. the container form) as if it were a class. Which is what is giving me a headache.



To explain:
On subform A, there is a textbox of which I need to have the text available for subform B.

I therefore have on the container form:

Option Compare Database
Option Explicit

Public textboxtext As String

On subform A:

Private Sub cmdSave_Click()
    Me.Parent.textboxtext = Me.textbox1.Value
End Sub

And on subform B:

Private Sub cmdShowMsg_Click()
    Dim text As String
    text = Me.Parent.textboxtext
    Call MsgBox("The text on the previous subform was: " & text)
End Sub

Now, this works like a charm.
The problem arises when trying to do this with an OBJECT instead of a VARIABLE.


E.g.
Container form:

Option Compare Database
Option Explicit

Public rst As DAO.Recordset

On subform A:

Private Sub cmdSave_Click()
    Set Me.Parent.rst = CurrentDb.OpenRecordset("SELECT * FROM table1 WHERE ID = " & Me.textbox1.Value & ";", dbOpenSnapshot)
End Sub

And on subform B:

Private Sub cmdShowMsg_Click()
    Dim rsttemp As dao.Recordset
    Set rsttemp = Me.Parent.rst
    Call MsgBox("The name of the record is: " & rsttemp.Fields("Name"))
End Sub

I've tried all different variants; passing the recordset through a function, or a sub, or a property set; using private objects, etc.
It seems that the reference to the recordset is lost as soon as the code jumps back to subform A, even before subform A is closed. ("<Out of context>" shows in watch window.)

Am I doing something wrong? Or is this a fundamental limitation of VBA? If so, is there a workaround?

When I write the code in a CLASS module instead of the FORM, it works just fine. Ah, and I'm using access 2007, btw.

Thank you for replying!

[FishVal]

I couldn't reproduce it in Access2003.
Would you like to attach a sanitized copy of your db to the thread?

Regards,
Fish.

[SjaakBanaan]

Fishval, thanks for replying. I'll post a sample db asap - just give me about 8 to 9 hrs to go to work and come back home :)

[FishVal]

No worries, post it when you like.

The only problem is that I don't have Access 2007. So it would be nice to get both original version (for any other willing to help and having Access 2007) and version downgraded to Access 2003 (for me and, I guess, for most of the forum staff).

[ADezii]

Hey guys (m/f),

I've got a problem that's been having me pulling out hairs and losing sleep quite some time now.

The thing is this. I have a container form with a subform control. This control can take several sub forms as its source object. To make the information, that is generated on one sub form, available to a later sub form, I'm trying to store data in public variables and objects of the container form.

In other words, I'm using a form (i.e. the container form) as if it were a class. Which is what is giving me a headache.



To explain:
On subform A, there is a textbox of which I need to have the text available for subform B.

I therefore have on the container form:

Option Compare Database
Option Explicit

Public textboxtext As String

On subform A:

Private Sub cmdSave_Click()
    Me.Parent.textboxtext = Me.textbox1.Value
End Sub

And on subform B:

Private Sub cmdShowMsg_Click()
    Dim text As String
    text = Me.Parent.textboxtext
    Call MsgBox("The text on the previous subform was: " & text)
End Sub

Now, this works like a charm.
The problem arises when trying to do this with an OBJECT instead of a VARIABLE.


E.g.
Container form:

Option Compare Database
Option Explicit

Public rst As DAO.Recordset

On subform A:

Private Sub cmdSave_Click()
    Set Me.Parent.rst = CurrentDb.OpenRecordset("SELECT * FROM table1 WHERE ID = " & Me.textbox1.Value & ";", dbOpenSnapshot)
End Sub

And on subform B:

Private Sub cmdShowMsg_Click()
    Dim rsttemp As dao.Recordset
    Set rsttemp = Me.Parent.rst
    Call MsgBox("The name of the record is: " & rsttemp.Fields("Name"))
End Sub

I've tried all different variants; passing the recordset through a function, or a sub, or a property set; using private objects, etc.
It seems that the reference to the recordset is lost as soon as the code jumps back to subform A, even before subform A is closed. ("<Out of context>" shows in watch window.)

Am I doing something wrong? Or is this a fundamental limitation of VBA? If so, is there a workaround?

When I write the code in a CLASS module instead of the FORM, it works just fine. Ah, and I'm using access 2007, btw.

Thank you for replying!

1. Dim rsttemp As dao.Recordset within the confines of a Click() Event essentially makes rsttemp a Local Variable and is destroyed as since as the Click() Event is terminated.
2. Dim text As String is never a good idea, Declaring a Variable with the same name as a Built-In Data Type.
3. Public rst As DAO.Recordset - Declaring a Variable as Public within the context of a Form makes it a Property of the Form which may not be the approach to take. Try Declaring it Privately within the Form where it is now accessible throughout.

[SjaakBanaan]

@ Fishval,

I have created a small db (1 table, 1 container form, 2 sub forms) from scratch, and the problem seems to not appear....

I will now try to cut pieces out of the the larger db that I have the problems with, to the point where it does work, to see where the problem lies.

Thanks for your help sofar!



@ADezii,

1. Dim rsttemp As dao.Recordset within the confines of a Click() Event essentially makes rsttemp a Local Variable and is destroyed as since as the Click() Event is terminated.

True; rsttemp is a local object that is destroyed once the click-event is terminated. Still, the recordset is retained when another object (i.e. the one on the container form) is still pointing to it.

1. Dim text As String is never a good idea, Declaring a Variable with the same name as a Built-In Data Type.

Sorry, my bad. That was an unfortunate naming example.

1. Public rst As DAO.Recordset - Declaring a Variable as Public within the context of a Form makes it a Property of the Form which may not be the approach to take. Try Declaring it Privately within the Form where it is now accessible throughout.

I want it to be a property of the form, so that the sub forms can actually change it! In practice I will of course have the rst object be private, and only modifiable through a 'property set' (where data validation checks are in place), or through a public subroutine (again, with all data validation checks in place).

I do not see how I can pass information between subforms in any other way than by storing them on the container form. If you have suggestions... they're very welcome! :)

(But you are right in that all information that is relevant only to ONE subform should be kept on this form, and not be shared).

Thanks for your help as well!

[SjaakBanaan]

Alright people,

I understand now. This is what happened.

On the parent form, there is the public recordset object 'rst'. Then, also on the parent form, there is a number of functions that retrieve data from this recordset, or from another recordset if specified. In code:

Public Function varCharacteristic(byval lngField as Long, Optional ByVal varID As Variant) As Variant
	    'Get a Field value.
	    Dim varResult As Variant
	    Dim rst As DAO.Recordset
	 
	    On Error GoTo ErrorHandler
	 
	    '1: Use the private recordset if no particular ID is specified.
	    '   (If a particular export option IS specified, it is only used in this procedure, and not selected as THE export option.)
	    If IsMissing(varID) Then
	        Set rst = p_rstApp
	    Else
	        Set rst = CurrentDb.OpenRecordset("SELECT * FROM table1 WHERE ID = " & CStr(CLng(varID)) & ";", dbOpenSnapshot)
	    End If
	 
	    '2: Retrieve field value.
	    With rst
	        If (.BOF And .EOF) _
	            Then varResult = Null _
	            Else varResult = .Fields(strField)                 'Causes (trapped) error if field doesn't exist.
	        Call .Close
	    End With
	 
	Quit:
	    'cleanup
	    Set rst = Nothing
	 
	    varCharacteristic = varResult
	    Exit Function
	ErrorHandler:
	    varResult = Null
	    Resume Quit
	End Function

Now, as I try to take care of all the objects and their memory use, I 'set' them all to 'nothing' before exiting the function (yes, not completely necessary. call me an autist.). This is not the problem, however. The problem lies in that I also 'close' all recordsets that I create within a function!

    '2: Retrieve field value.
    With rst
        If (.BOF And .EOF) _
            Then varResult = Null _
            Else varResult = .Fields(strField)                 'Causes (trapped) error if field doesn't exist.
        [B]Call .Close[/B]
    End With

This closes the private recordset... and makes it unavailable for later data retrieval!

Arhg! This has taken years off my life - but at least i'm a wiser man now.

Thanks again for trying to help me out!

[NeoPa]

(yes, not completely necessary. call me an autist.)

Never underestimate the value of being thorough. Even excessively thorough is a good thing.

It is a trait that marks out the very good programmers from the ordinary. Particularly important for database work.

Well done for getting the solution, and Welcome to Bytes!