Access VBA

[beruken]

I am trying to set the Label Caption consisting of 12 labels (not an array)Named LABEL1 - LABEL12 on an access 2000 form. I would like to set the caption for those 12 labels base on the current month and year. I determined how to evaluate the caption value, but can't figure out how to set LABEL(i) since Access VBA thinks this is a function. Also Access doesn't prompt to create a control array when cutting and pasting a current Label control. Example code below.

Private Sub Form_Load()

Dim curMonth As Date
Dim curYear As Date
Dim strMonthYear As String
Dim remainMonths As Integer
Dim i As Integer
'Dim Label(1 To 12) As Label

curMonth = Month(Date)
curYear = Year(Date)


For i = curMonth To 12
strMonthYear = MonthName(i, True) & curYear
Label(i).Caption = strMonthYear

Next

' Determine how many months in the following year
If curMonth > 1 Then
remainMonths = (curMonth - 1)

For i = 1 To remainMonths
strMonthYear = ((MonthName(i, True)) & " " & (curYear + 1))
Label(i).Caption = strMonthYear
Next
End If

End Sub

Thanks, KEn

[coolbiz]

Looked through VBA in Access2000 and does not seem to support Control Array. Anyway the code below can get you the Label that you're looking for:


private Function GetLabel(szLabelName as string) as Label
' init return value
set GetLabel = nothing

' loop through all controls on the form
dim oCtrl as Control
for each oCtrl in me.Controls
if (TypeName(oCtrl) = "Label") then
' check the name
if (ucase(oCtrl.Name) = ucase(szLabelName)) then
set GetLabel = oCtrl
exit function
end if
end if
next oCtrl
End Function

' modification of you code
private Sub Form_Load()
' your previous code

dim oLabel as Label ' hold the label object
for i = curMonth to 12
strMonthYear = MonthName(i, true) & curYear
set oLabel = GetLabel("Label" & i)
oLabel.Caption = strMonthYear
next

' your later code
End Sub




Well the idea is there. You can play with code a little more to ensure that it is robust. Have not tested the code so play with it.

-Cool Bizs