Smart questions
Smart answers
Smart people
Join Tek-Tips Forums

Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

netbumbler (ISP) (OP)
6 Oct 05 11:28
I know I've seen this before, but I can't find the reference and I've looked everywhere!

I'm trying to reference a form!subform1!subform2!field(firstrecord, secondfield) value for a function originating at the form level.

I swear I can't find a good reference for these syntaxes - anyone have a good VBA summary site that shows this?

rubbernilly (Programmer)
6 Oct 05 11:34
try this...


dim rs as dao.recordset

set rs = me.subformname.form.Recordset

if rs.absoluteposition = -1 then
   rs.absoluteposition = 1
end if


Set rs = nothing
netbumbler (ISP) (OP)
6 Oct 05 11:44
I'll work on this and see if I can make it work.  
I was hoping there was a way to directly reference the existing data like in a listbox.. ie:  
form!main!sub!sub2!record(0).item(2) or something like that.
rubbernilly (Programmer)
6 Oct 05 11:54
Did some more research...

there is also

Helpful Member!  MajP (TechnicalUser)
6 Oct 05 12:18
Here is the confusion there is no such thing as

Assume that you have a field in your recordset called "strLastName".  By default when you put this on a form the control gets the name "strLastName".  If I type:

Debug.print (Me.strLastName)
I get a value from the field.  Actually not, I am getting a value from the control that has the same name as the recordset field.
Me.strLastName is short for

You can drop controls, index, and value because they are all default methods or properties.  So here is how to reference a control on a subform without any shorthand so you can see what is really happening:


Now it looks like you have a nested subform


FormInTheSubformControl1.Controls.index("subFormCntl2") _

Notice a subform control and the form within the control are two different things
netbumbler (ISP) (OP)
6 Oct 05 12:36
  Thanks for the information.  The subdatasheet is based on a  query.  So is there a method for pulling the first record, field "strLastName"?  

I guess my problem now is I've been switching back and forth from PERL to VBA and I feel I'm getting a bit SCHIZO, if you know what I mean! :)

6 Oct 05 12:59
Have a look here:

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

netbumbler (ISP) (OP)
6 Oct 05 13:09
I guess I'm being stupid here.  I looked at the article - but again, I'm trying to reference the first record of the subdatasheet (preferrably in an absolute way).  This tells me how to reference a field (aka control) but not how to reference the row.
rubbernilly (Programmer)
6 Oct 05 13:31
you could set the AbsolutePosition property, but that would actually move you to that record in the subform. Don't know if you want that.
MajP (TechnicalUser)
7 Oct 05 0:14
   Your subform is a query datasheet which is just a form.  Every form has a RecordsetClone property which provides a copy of the recordset that the form is based In A2k and beyond there is also a Recordset property which returns a pointer to the recordset not a copy.  You can actually get the value out of the the control or go directly to the recordset.  I will demonstrate both.  Normally you would use the recordset directly:

Sub form control - "subFrmChildren"
The field I am interested in - "strName"


  Dim rs As DAO.Recordset
  dim strValue
 'Get the recordset clone of the subform
 'You could get the Recordset but since it is not a copy
 'whatever you do effects the form as well
 Set rs = Me.Controls("subFrmChildren").Form.RecordsetClone
 rs.MoveFirst 'Out of habit
 'Now move to the record you want.  Reference Move,   
 'MoveFirst, MoveLast, MoveNext
 'You said the second record so I will use a Move
 rs.Move 1
 strValue = rs.Fields("strName").Value

No need to, but here is how you use the control.


  dim cntrlOnSub as control
  set cntrlOnSub = _
  'although it has the same name as my field it is a control
  'not a field name
  'Now I will have to use the recordset property. To
  'actually move on the subform
  Set rs = Me.Controls("subFrmChildren").Form.Recordset
  rs.Move 1
  strValue = cntrlOnSub.value
netbumbler (ISP) (OP)
7 Oct 05 12:40
Thanks everyone for your help and knowledge!!!

Here's what MajP's last post directed me to do... and maybe this will help someone down the road...  On MajPs information regarding controls, I used my "A2002 VBA handbook" and searched for controls - and came across this:

?forms![expense reports]![employees subform].form.controls![total expenses]

So, on a hunch, I tried this

?forms![expense reports]![employees_

Then I used this:
?forms![expense reports]![employees_
response: 6/24/2005

AND, I didn't have to establish any additional variables - I reference this [servicedate] value directly!
Very cool!
Thanks so much,
MajP (TechnicalUser)
7 Oct 05 15:51
But Netbumbler here is the quiz.  Why does this work:


?forms![expense reports]![employees_
because the default obj for a form is the controls collection.  The default for a recordset is the fields collection.
The bang returns a member of the default collection
So all this is shorthand for what is really happening


Forms.index("expense reports").controls.index("employees subform").form.recordsetclone.fields.index("servicedate")
Or in English.  Go to the forms collection and get form "expense reports", get its control collection, get the control named "employees subform", get the subform control's form, get the sub form's recordsetclone, get the recordset's field collection, and get the field "servicedate" from the collection.  
This is why I personally never use bang notation, and often  use complete notation.  Then it is just a matter of following the chain.  
netbumbler (ISP) (OP)
7 Oct 05 16:33
I understand the object and control and recordset concepts - there's an 'instance' of the query accessible through the subform datasource - and accessing it through a recordsetclone (maps to that instance).  I understand that concept - but what I think I was getting hung up on - was the creation of a DAO recordset (btw - I was having problems with this - and I think the library isn't installed/activated or something).  But - I had seen the chain references before and I KNEW, I just KNEW! there had to be a way to access it directly!  What I didn't understand is why everyone was giving examples to create an additional recordsetclone.  Not sure if I'm missing the reason for this extra instance here - but my function is not interactive - so I feel I can depend on a static 'chain' reference, if you will.
This stuff is really cool when it works, hehe.  Sometimes half the pain is finding the right syntax for what you're trying to do!  You just have to live, eat and breath this stuff.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close