VBA Reference to field value in form recordset (1 Viewer)

BiigJiim

Registered User.
Local time
Today, 19:14
Joined
Jun 7, 2012
Messages
114
Hi,

I have a bound form which has a number of fields in it's recordset which are NOT bound to any controls on the form. In VBA, I need to get the value of one of these fields in the current record. However the field I need will change each time the procedure is run and is determined by the vba procedure itself.

I thought that each underlying field in a form recordset was also a property of the form itself. Therefore I tried

myform.properties(strMyFieldName).Value

where strMyFieldName is a string variable of the name of the field. However this gives an error 2455 Invalid reference to the property.

Can someone please tell me how to get the value of a field in a form recordset when the field selection is dynamic?

Many thanks for reading and for any help!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:14
Joined
Feb 28, 2001
Messages
26,999
Me.Recordset("fieldname") should work. And there is nothing wrong with using a variable rather than a literal fieldname as long as that variable contains the name of a legit field.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:14
Joined
May 21, 2018
Messages
8,463
Based on what @sonic8 explained to me.
Below information provided by @sonic8 )
However, what appears as an exception is not. These fields are actually added as "pseudo" controls in the controls collection.
Access automatically adds a control of type AccessField to the Controls collection of a form for each field in the current data source that does not have a "real" control of the same name already. (- This is done whenever the Recordsource of the Form changes. However, historically this did not work reliably in older Access Versions. In current versions (Access 2016+) it appears to be reliable. These "auto-controls" are not included in Controls.Count, but you can also reference them using Me.Controls("FieldName").
so myform.controls(strMyFieldName).Value
eventhough there is not an actual control
If not this works
me.recordset.fields(strMyFieldName).value
 

missinglinq

AWF VIP
Local time
Today, 15:14
Joined
Jun 20, 2003
Messages
6,423
Actually...Me.FieldName works for me, in v2007, for a Field in the underlying table but not bound to a Control on the form.

Or did I misunderstand the question?

Linq ;0)>
 

Micron

AWF VIP
Local time
Today, 15:14
Joined
Oct 20, 2018
Messages
3,476
MajP; thanks for the reminder on that. Was going to check it out when I saw it the last time but forgot. What's interesting is that as you type, you're prompted by intellisense to provide the index which I suspect is a numeric value. If you provide a value that exceeds the number of form controls, it fails. If you provide the name value, there doesn't have to be a control on the form as you say, yet in both cases you're providing a reference to something in a collection, yes? It's not that I'd use the index as a number over the name of the field, just pointing out what seems like an anomaly.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:14
Joined
May 21, 2018
Messages
8,463
by intellisense to provide the index which I suspect is a numeric value
Although intellisense says index it really should read index_or_Key
Required. An expression that specifies the position of a member of the collection. If a numeric expression, index must be a number from 1 to the value of the collection's Count property. If a string expression, index must correspond to the key argument specified when the member referred to was added to the collection.

So most vba collections have keys, for controls and fields of a recordset the key is the name.
This is the same in user defined collections. You can provide a key.
Code:
Private Sub DemoCollection()
  Dim col As New Collection
  col.Add 1234, "Item1"
  col.Add 4567, "Item2"
  col.Add 6789, "Item3"
 
  MsgBox col(1) & " " & col("Item1")
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:14
Joined
May 21, 2018
Messages
8,463
@missinglinq
Actually...Me.FieldName works for me, in v2007, for a Field in the underlying table but not bound to a Control on the form.
Or did I misunderstand the question?
I think you may have misread the question. The OP wants to use a variable for the field name because the field changes each time.
 

Micron

AWF VIP
Local time
Today, 15:14
Joined
Oct 20, 2018
Messages
3,476
Makes sense, but what I find odd is that it will accept a numeric value that corresponds only to the number of controls on the form, or a name that doesn't correspond to a control on the form as long as it's a field in the record source. It's as if the collection has both a key and an index but there might be more key values than there are index values.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:14
Joined
May 21, 2018
Messages
8,463
I find odd is that it will accept a numeric value that corresponds only to the number of controls on the form
I see what you are saying. The way @sonic8 described this to me is that the controls collection holds 2 "types" of objects. Each physical control and AccessField objects. An AccessField object gets added for every field in the recordsource that is not a physical control. This makes a lot of sense and answers a lot of questions. However the AccessFields do not add to the count. I can get how that could be done, but how the index could not work seems hard to understand. Seems you would have to force the collection not to return a value over the physical control count, but the question is why do that, Maybe Sonic8 can explain. None of this is documented anywhere.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:14
Joined
Jan 20, 2009
Messages
12,849
Based on what @sonic8 explained to me.

so myform.controls(strMyFieldName).Value
eventhough there is not an actual control
Wow. I knew they were added as Members of the Form Object (subordinate to the Controls if they share the same name) but being able to refer to them via the Controls Collection is a big surprise. I always thought the Recordset fields must have been a second tier of the default Members. It seems they are more like an understudy to the Controls. When the Control doesn't turn up the Field gets the role.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:14
Joined
May 21, 2018
Messages
8,463
A slight test to prove this
Code:
Debug.Print TypeName(Me.Controls("last_Name"))
With no control on form answer comes back
AccessField
After putting the control on form
Textbox
 

Users who are viewing this thread

Top Bottom