referencing a field in a datasheet subform 1

[netbumbler]

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?
Thanks,
Chris

 

[rubbernilly]

try this...

dim rs as dao.recordset

set rs = me.subformname.form.Recordset

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

MyFunction(rs!FieldName)

Set rs = nothing

 

[netbumbler]

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

 

[rubbernilly]

Did some more research...

there is also

me.subformcontrolname.Controls("ControlName")

 

[MajP]

Here is the confusion there is no such thing as
formName.FieldName

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
Me.Controls.index("strLastName").value

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:

Me.controls.index("theSubFormControl")._
theFormInTheSubformControl.Controls.index("nameOfControl)

Now it looks like you have a nested subform

Me.controls.index("SubFormControl1")._
FormInTheSubformControl1.Controls.index("subFormCntl2") _
.form.Controls.index("cntlOnSubForm2")

Notice a subform control and the form within the control are two different things

 

[netbumbler]

MajP
Thanks for the information. The subdatasheet is based on a query. So is there a method for pulling the first record, field "strLastName"?
Thanks,
Chris

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!

 

[PHV]

Have a look here:

http://support.microsoft.com/?kbid=209099

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

 

[netbumbler]

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.
Thanks,
Chris

 

[rubbernilly]

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]

netbumbler,
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 = _
  Me.Controls("subFrmChildren").Form.controls("strName")
  '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]

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_
subform].form.recordsetclone.movefirst

Then I used this:
?forms![expense reports]![employees_
subform].form.recordsetclone![servicedate]
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,
Chris

 

[MajP]

But Netbumbler here is the quiz. Why does this work:

?forms![expense reports]![employees_
subform].form.recordsetclone![servicedate]

Answer
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]

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