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]

@SjaakBanaan

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,

@ADezii
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.

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

@ADezii
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.
        Call .Close
    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]

@SjaakBanaan
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!