Access VBA

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

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