Retreiving a field from the current record in a subform

[Galaxiom]

I have a subform whose record source is a rather complex unbound query.
On the main form is a text box where I want to load a field value from the current record in the subform query.

Currently I have a button with the code:
Me!SubformName.Form.CurrentRecord

This returns the current record line number so I must be correctly referring to the form.

But how do I refer to the value of a field in the Current record?

Thanks

 

[pbaldy]

Here's a handy reference on the syntax:

http://www.mvps.org/access/forms/frm0031.htm

 

[Galaxiom]

Thanks pbaldy. That page you linked was where I got started. It shows controls and properties really clearly.

However I can't find how to refer to the values in the query results.

I am using a query as the datasource for the form rather than the form the Access Wizard makes. I presume that refering to that form would have been easier than addressing a query directly.

I did it this way because I really wanted the flyout subtable structure which is not available unless the subform's datasource is a table or query itself.

I need to get hold of the value in a particular field of the current record of the query so I can populate a set of textboxs for editing records in another table based on the value in a field.

 

[gemma-the-husky]

for the current row any field value will be, i think

me.recordsource("fieldname")

recordsource IS the underlying query.

 

[pbaldy]

Referring to a control would have gotten the corresponding field value in the currently selected record. Perhaps you can expand on what you're trying to do. If you're only using the form to get at the query values, you're probably better off with a DLookup or recordset, depending on circumstances. Since you say "set of textboxes", I'm guessing a recordset.

 

[Galaxiom]

Thanks for the suggestion Gemma. However this results in an error saying it can't find the field by the name of the record source.

Me!subformname("fieldname") works. Somehow I either managed to miss this or misspelled something when I tried. It should have been obvious to me and worked straightup when I tried again this morning. Put it down to mental fatigue I guess. Sure you have all been there when working on database design.

Thanks

 

[Davisk]

simply use the control as source:
me.givecontrolnamehere.text or
incase the of a subform
me.nameofthesubform.form.controls.item(controlname or index).text

 

[Galaxiom]

simply use the control as source:
me.givecontrolnamehere.text or
incase the of a subform
me.nameofthesubform.form.controls.item(controlname or index).text

That won't work under any circumstances where the code is applied to a button event because the Text property is only available if the control has the focus which it won't because the button will have the focus.

Controls.Item(index) is a very long winded expression to refer to a control. Just the name of the control is sufficient.

BTW This thread is eight years old.

 

[JimMcL]

@Galaxiom And it's still a hard problem to solve.

 

[pbaldy]

And another 2.5 years old.

 

[JimMcL]

It's a complex series of steps, but I figured it out!

To get to a 'field' within a record on a 'subform' of a 'mainform', this works:

[Forms]!['mainform']!['subform'].[Form].[Controls]!['field']

The key is that 'Form.Controls' reference.

I didn't find this through the manual, but just looking at and trying the methods that come up when typing the reference into a macro Where clause.

It IS documented in the manual, but since I'm new here, I can't post a URL.

:banghead:

 

[Deleted member 147267]

It IS documented in the manual, but since I'm new here, I can't post a URL.

At the risk of contributing to the life of a thread that should probably be allowed to rest in peace I'll say don't worry about it. It's a common topic around here and it's easily found if you 'Google' it. Since you posted a version, I'll point out that to some, the words you've used are a bit ambiguous. Some will read that and actually use ['mainform'] etc. I think it's more accurate to explain it as

Forms!nameOfMainForm!nameOfSubformControl.Form and then either the control name, form property or form method and whatever follows that. You don't actually need the [Controls] part. In fact, I would have thought that would not work because it's Controls("nameOfControl") OR Controls(index) where index is a number representing the ordinal position of the control in the Controls collection for the parent object. The collection would be zero based by the way. Out of curiosity I tried
- ...form!dsid
- ...form.Controls!dsid
where dsid is the name of the control, and it did work. However, if you replace ! with . (dot) as I normally would write that, it does error. Because I don't know enough about what the default members of every collection are, I would not use ! in place of the more specific dot and will stick with what I already use that I know works. Was interesting though.