Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!
  • Students Click Here

*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.

Students Click Here


referencing a field in a datasheet subform

referencing a field in a datasheet subform

referencing a field in a datasheet subform

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?

RE: referencing a field in a datasheet subform

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

RE: referencing a field in a datasheet subform

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.

RE: referencing a field in a datasheet subform

Did some more research...

there is also


RE: referencing a field in a datasheet subform

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

RE: referencing a field in a datasheet subform

  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! :)

RE: referencing a field in a datasheet subform

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.

RE: referencing a field in a datasheet subform

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.

RE: referencing a field in a datasheet subform

   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

RE: referencing a field in a datasheet subform

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,

RE: referencing a field in a datasheet subform

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.  

RE: referencing a field in a datasheet subform

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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!

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