vba run time error 2465 - trying to make changes to combo boxes

[bris]

Hi

I'm trying to make some changes to a few combo box.
In hard values it works:



i.e. Form_ExistingForm!nab_rt_7d_drop.SetFocus
This works fine.

However I need to do similar changes to 33 combo box so I want to do a loop.
This doesn't work:

Name = "nab_rt_" & LengthInMonths(i) & "_drop"
Form_ExistingForm!Name.SetFocus
Neither does this:
Form_ExistingForm![Name].SetFocus

The concatenation in Name works fine I get the good name.
I'm wondering, is it a type problem?
I tried dim name as string and as variant.


many thanks
Bris

[bris]

Please let me know if i didn't explain properly, been stuck with this for a while...

[June7]

Don't understand, why are you setting focus to controls in a loop?

Name is a reserved word in Access/VBA. Do not use it for name of anything, including variables.

Use Controls collection to refer to controls by variable.

Form_ExistingForm.Controls("nab_rt_" & LengthInMonths(i) & "_drop").SetFocus

[bris]

Please let me know if i didn't explain properly, been stuck with this for a while...

[bris]

Hi thanks a lot for your reply!

I set focus and then do other things like

'Type of quote combo box --------------------------------------------------------------------------
Name = "nab_rt_" & LengthInMonths(i) & "_drop"
Form_ExistingForm![Name].SetFocus
Form_ExistingForm![Name].Locked = False
Form_ExistingForm![Name].Value = Null
If TERM = LengthInDays(i) Then
'Preselect the Optrate option in the type of quote combo box
Form_ExistingForm!Name.Value = "Optrate"
End If
Form_ExistingForm!Name.Locked = True
(I actually don't know if I need the setfocus, I was going to try to remove it and see if it works after it actually works)

Oh thanks a lot for the Name info I'll change the name of my variable and retry.

I had already tried:

Form_ExistingForm.Controls("nab_rt_" & LengthInMonths(i) & "_drop").SetFocus

and that actually made me lose my variable -> LengthInMonths(i) was equal to ""

I had also tried

Me.Controls("nab_rt_" & LengthInMonths(i) & "_drop").SetFocus

which also gave the same error than with Name error 2465 field not found.

Many thanks for your reply I'm going to try with another variable "name"

[bris]

Ok I named my variable NameOfBox and it still gives the same error... :/

[bris]

I looked at lot of things before posting which is how I had found the controls thingy.

Now that you said to use it, I again tried things with that and it seems that

NameOfBox = "nab_rt_" & LengthInMonths(i) & "_drop"
Form_ExistingForm.Controls(NameOfBox).SetFocus is working !!!

(however I checked again and
Form_ExistingForm.Controls(N"nab_rt_" & LengthInMonths(i) & "_drop".SetFocus
is not working cos my variable loses its value and becomes "")


yeyyyyyy
Thanks so much

[Rod]

i.e. Form_ExistingForm!nab_rt_7d_drop.SetFocus
This works fine.

Name = "nab_rt_" & LengthInMonths(i) & "_drop"

Have another look at the second expression: I suspect it will resolve to "nab_rt_7_drop" and not "nab_rt_7d_drop"

June7 is absolutely correct: avoid reserved names especially 'Name' and 'Date' which are favourites for causing mahem.

You seem to be building intelligence into the control names. Sometimes this is unavoidable but I would always first consider whether the control's Tag property is better suited to my needs.

I am also an advocate of investigating my environment and using what I find rather than predicting what should be there. What I mean by that is I believe it better to iterate through the controls on the form and see whether each control is one I want rather than looking for a particular explicitly named control. Here's the rough (untested, off the top of my head) sort of thing I mean.

Dim ctl as Access.Control

For Each ctl in Me.Controls
If TypeOf ctl is Access.ComboBox Then
'Make what other tests are necessary to see whether you
'are interested in this control. All properties are available
'including Name. Change whatever property values you wish.
End If
Next

[June7]

I thought LengthInMonths was a custom function you were calling. Just what is that entity? Show the complete procedure. Use CODE tags in post to preserve indentation.

If you want to provide db for analysis, follow instructions at bottom of my post. Indicate the objects involved in this issue.

The SetFocus should not be necessary.

[bris]

Have another look at the second expression: I suspect it will resolve to "nab_rt_7_drop" and not "nab_rt_7d_drop"

Actually I do get the right expression with a d thanks.

Many thanks for your advices, much appreciated.

Cheers
Bris

[bris]

About the LengthInMonths entity:
I do:

Const Number As Integer = 33
Dim LengthInMonths(Number) As String

'Then I give values to my variables: (I put that in an InitialisationOfMyVariables sub)
LengthInMonths(1) = "7d"
LengthInMonths(2) = "14d"
LengthInMonths(3) = "1m"
'...

Now I just realised, I hope number isn't a key word either.

Cheers
Iris

[June7]

It is - http://support.microsoft.com/kb/286335

So not solved?

LengthInMonths is an array. Did you declare Option Base 1 in the procedure header?

[bris]

Yes yes it is solved, it's working even with the "Number" word.

I only declared Option Compare Database.
Should I declare Option Base 1?

[June7]

Default start index for array is 0. Without declaring Option Base 1 the array has 33 elements 0 to 32. You don't seem to use index 0 in your code. Do you use index value of 33? Think that 33 would error.

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx