Control Name Referencing

[grinnZ]

I have 5 form controls named...

Deposit Duration Dep48 Dep24 Dep12

Deposit is simply an entered value.
Duration is combo box with the value list of 48, 24, 12.
Based on the user choice in the Duration combo the appropriate DepX field is updated.

How do I refer to the DepX controls much like this?

me.txtDep(me.cboDuration) = Deposit

 

[boblarson]

me.txtDep(me.cboDuration) = Deposit

Where'd txtDep come from? You said your controls were named Dep48, Dep24, Dep12 and not txtDep48, txtDep24, txtDep12.

But regardless it would be

Me.Controls("Dep" & Me.cboDuration).Value = Deposit

or

Me.Controls("txtDep" & Me.cboDuration).Value = Deposit

depending on which way they really are named.

 

[grinnZ]

Thank you Bob. I knew as soon as I scrolled and saw your name that I had my answer.

 

[grinnZ]

By the way, txtDep came from the same magical bin from whence came cboDuration. Slipped that one by you!

 

[Galaxiom]

In VBA you should ALWAYS unambiguously refer to the control.
Me.Deposit

Access guesses the context if just given the control name and you definitely don't want Access to be guessing anything. I have seen it get confused where two forms have controls with the same name and the Me reference is not included. It might also find a variable instead without the Me.

BTW you can omit the Value property because it is the default property of the control.

 

[boblarson]

BTW you can omit the Value property because it is the default property of the control.

Not if you use this syntax:

Me.Controls("txtDep" & Me.cboDuration).Value = Deposit


You MUST include the .Value because the Controls collection does not have .Value as its default because lines, labels, boxes, etc. do not have a .Value property.

 

[Galaxiom]

Not if you use this syntax:

Me.Controls("txtDep" & Me.cboDuration).Value = Deposit

You MUST include the .Value because the Controls collection does not have .Value as its default because lines, labels, boxes, etc. do not have a .Value property.

Maybe this has changed since earlier versions but Access 2007 certainly accepts the direct reference to the item in the collection without including the Value property.

Clearly there is an implicit property due to a default because a reference like this to a control that has no Value property returns a 438 error, "Object does not support this property or method".

 

[boblarson]

Maybe this has changed since earlier versions but Access 2007 certainly accepts the direct reference to the item in the collection without including the Value property.

Clearly there is an implicit property due to a default because a reference like this to a control that has no Value property returns a 438 error, "Object does not support this property or method".

Hmm, I wonder why I ended up with an error using that before then. I just ran a series of experiments and couldn't get it to error.

 

[Galaxiom]

Hmm, I wonder why I ended up with an error using that before then. I just ran a series of experiments and couldn't get it to error.

I recall having been caught before where I ran a test that used a reference to a control on a form that had not yet been saved. I can't exactly remember the circumstances and I didn't try to replicate the error but this was my impression at the time.

I intended to pass the control value (using the default syntax) as an argument in a function that included an IsMissing test. The test worked with the function apparently being passed the Null from the control so I posted the function only to be told it didn't work.

It turned out that after saving the form, the function was passed the control object itself as the argument (IsMissing only works on Variants).