-
Solved: More array questions
I'm thinking I outsmarted myself. When I began outlining this OL2003 app, I named my control suffixes to match the consts I use in the following array.
[VBA]
Const DAY180 = 180
Const DAY120 = 120
Const DAY90 = 90
Const DAY60 = 60
Const DAY30 = 30
Const ExpireDay = 1
arrayRenewalControlNames = Array(DAY180, DAY120, DAY90, DAY60, DAY30, ExpireDay)
[/VBA]
Thanks to the help in an earlier thread, this array is behaving as I need returning the Const values as integers used in date calculations after I removed the "" marks from the CONST in the array.
What I also intended to do with this array was use it in a loop to construct the TextBox and Checkbox control names I placed on the custom form. The side effect of removing the "" marks from the array elements prevents the following code from working. I get a Type Mismatch error.
[VBA]
Dim i As Integer
For i = LBound(arrayRenewalControlNames) To UBound(arrayRenewalControlNames)
If arrayRenewalDates(i, 1) = vbNull Then
'There was no date set, so disable the controls
With "txt" & arrayRenewalControlNames(i) 'Build the control name used on the form
.Value = arrayRenewalDates(i, 1)
.Enabled = False
.BackColor = vbGrayText
End With
With "cbox" & arrayRenewalControlNames(i) 'Build the control name used on the form
.Enabled = False
.Value = arrayRenewalDates(i, 2)
.BackColor = vbGrayText
End With
Else
'Set the date and enable the controls
With "txt" & arrayRenewalControlNames(i) 'Build the control name used on the form
.Value = arrayRenewalDates(i, 1)
.Enabled = True
.BackColor = vbWindowBackground
End With
With "cbox" & arrayRenewalControlNames(i) 'Build the control name used on the form
.Enabled = True
.Value = arrayRenewalDates(i, 2)
.BackColor = vbWindowBackground
End With
End If
Next i[/VBA]
I tried casting the array element to get around the type mismatch by using:
[VBA]
With "txtDay" & CStr(arrayRenewalControlNames(i))
AND
With "txtDay" & Str(arrayRenewalControlNames(i))
[/VBA]
Both give me an error message that I must declare an object, etc. When I examine the resulting concatenation in the immediate window, the name constructs correctly, so I am not sure why I get the error message.
So I am seeking suggestions for resolving my need to use both the CONST values in one procedure and the CONST names in another - hopefully without creating another array for each set of controls.
Thanks for any advice!
James
-
The problem is you are referring to a string rather than an object in your with statement. Replace your with lines with:
[vba]
With Me.Controls("txt" & arrayRenewalControlNames(i))
[/vba]
etc.
"Computers are useless. They can only give you answers." - Pablo Picasso
Mark Rowlinson FIA |
The Code Net
-
Mark - right on the money! I'm cruising now...
Thanks!
James
-
Can we mark this one solved, then, James?
What about the second one? Is that solved?
-
Dreamboat - yes, both are resolved!
-
Glad I could help.
"Computers are useless. They can only give you answers." - Pablo Picasso
Mark Rowlinson FIA |
The Code Net
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules