Re: Adding controls to a VBA Collection

On Nov 29, 8:40 pm, "Neil" <nos...@xxxxxxxxxx> wrote:
"Nunzio" <g...@xxxxxxxxxxx> wrote in message

news:c9290594-4ec1-4a25-ab8f-d1c6a3cfef90@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



I am trying to add specific form controls to a VBA collection, so that
I can pass the entire collection to another function, and access the
individual controls later. I have tried several different syntax
variations. In each case, the receiving function produces the error
'Object Required' when I try to access any property of a control in
the collection. If I reference a collection item in the debugger
using its ordinal position, I see the value of the control. So, on
the surface at least, it appears that I am only adding the control's
'value' to the collection, not the control itself; or maybe my syntax
for retrieving the control's properties is at fault. Any help would
be greatly appreciated.

Dim colControls As Collection
Dim ctl As Control

Set colControls = New Collection

'I've tried this
Set ctl = Me.txtDosage
colControls.Add (ctl)

'And this
colControls.Add Me.controls("txtLabel")

'And this
colControls.Add Me.txtLabel

'The code to receive the collection look like this
Public Function fcnLogAudit(lngRecordType As enuRecordType,
lngRecordKey As Long, _
colControls As Collection) As Boolean

Dim ctl As Control

'Error occurs on this line. If I look at ctl(1) I can see the
control's
'value, but I can't access any of it's properties
For Each ctl In colControls

If ctl.Text <> ctl.OldValue Then...

Just use an array. Define an array of controls, as follows:

Dim arrControls(10) As Control

Replace 10 with whatever number, or leave blank for a dynamic array.

Then, in your function, define the parameter using a variant type, as
follows:

Public Function fcnLogAudit(lngRecordType As enuRecordType,
lngRecordKey As Long, arrControls As Variant) As Boolean

Use the UBound() function to get the upper bound of the array if you use a
dynamic array. And you're done!

Neil

Thanks Neil. That hadn't occurred to me.

I've been trying different approaches, and I've found that by using a
scripting dictionary object instead of a collection, the technique
that I've been using now works. I like your idea better though.

-Dean
.

Relevant Pages

  • Re: Dynamic Array Problems
    ... >that I stored in the array. ... >Private Sub Set() ... > For Each ctl In Me.GroupHeader3.Controls ... >form control is to large for this location". ...
    (microsoft.public.access.formscoding)
  • Tab Index Order, Setting With VBA
    ... The code below is supposed to assign control tab index order. ... Nth position in array ... For Each ctl In Forms ...
    (microsoft.public.access.formscoding)
  • Re: Line Pass
    ... In the class I need to Load additional lines. ... So far all the syntax I tried except pass as object fails. ... Is the ctlLine an array of Line controls, a Line control part of an array, or a single Line control? ...
    (microsoft.public.vb.general.discussion)
  • Re: Duplicate UC during Runtime
    ... array is created. ... Add it in the CTL. ... Add it to the control once placed on the FRM. ...
    (microsoft.public.vb.general.discussion)
  • Dynamic Array Problems
    ... that I stored in the array. ... Private Sub Set() ... For Each ctl In Me.GroupHeader3.Controls ... Restore it loads 3 values back then gives me the error; "The Control or Sub ...
    (microsoft.public.access.formscoding)