By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,213 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,213 IT Pros & Developers. It's quick & easy.

setting public objects of parent form

P: 6
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Public textboxtext As String
On subform A:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2.     Me.Parent.textboxtext = Me.textbox1.Value
  3. End Sub
And on subform B:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdShowMsg_Click()
  2.     Dim text As String
  3.     text = Me.Parent.textboxtext
  4.     Call MsgBox("The text on the previous subform was: " & text)
  5. End Sub
Now, this works like a charm.
The problem arises when trying to do this with an OBJECT instead of a VARIABLE.

Container form:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Public rst As DAO.Recordset
On subform A:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2.     Set Me.Parent.rst = CurrentDb.OpenRecordset("SELECT * FROM table1 WHERE ID = " & Me.textbox1.Value & ";", dbOpenSnapshot)
  3. End Sub
And on subform B:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdShowMsg_Click()
  2.     Dim rsttemp As dao.Recordset
  3.     Set rsttemp = Me.Parent.rst
  4.     Call MsgBox("The name of the record is: " & rsttemp.Fields("Name"))
  5. 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!
Jun 2 '09 #1
Share this Question
Share on Google+
7 Replies

Expert 2.5K+
P: 2,653
I couldn't reproduce it in Access2003.
Would you like to attach a sanitized copy of your db to the thread?

Jun 3 '09 #2

P: 6
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 :)
Jun 3 '09 #3

Expert 2.5K+
P: 2,653
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).
Jun 3 '09 #4

Expert 5K+
P: 8,679
  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.
Jun 3 '09 #5

P: 6
@ 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!


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.

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

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!
Jun 3 '09 #6

P: 6
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function varCharacteristic(byval lngField as Long, Optional ByVal varID As Variant) As Variant
  2.         'Get a Field value.
  3.         Dim varResult As Variant
  4.         Dim rst As DAO.Recordset
  6.         On Error GoTo ErrorHandler
  8.         '1: Use the private recordset if no particular ID is specified.
  9.         '   (If a particular export option IS specified, it is only used in this procedure, and not selected as THE export option.)
  10.         If IsMissing(varID) Then
  11.             Set rst = p_rstApp
  12.         Else
  13.             Set rst = CurrentDb.OpenRecordset("SELECT * FROM table1 WHERE ID = " & CStr(CLng(varID)) & ";", dbOpenSnapshot)
  14.         End If
  16.         '2: Retrieve field value.
  17.         With rst
  18.             If (.BOF And .EOF) _
  19.                 Then varResult = Null _
  20.                 Else varResult = .Fields(strField)                 'Causes (trapped) error if field doesn't exist.
  21.             Call .Close
  22.         End With
  24.     Quit:
  25.         'cleanup
  26.         Set rst = Nothing
  28.         varCharacteristic = varResult
  29.         Exit Function
  30.     ErrorHandler:
  31.         varResult = Null
  32.         Resume Quit
  33.     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!

Expand|Select|Wrap|Line Numbers
  1.     '2: Retrieve field value.
  2.     With rst
  3.         If (.BOF And .EOF) _
  4.             Then varResult = Null _
  5.             Else varResult = .Fields(strField)                 'Causes (trapped) error if field doesn't exist.
  6.         Call .Close
  7.     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!
Jun 3 '09 #7

Expert Mod 15k+
P: 31,707
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!
Jun 4 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.