Hi All,

Here is why I'm trying to do, but I just can't work it out.

I have a main page, and on the left I hand side, I have a list of labels, that represent stores, the name of the label = StoreName, and the backcolor = integer defined by StoreStatus String

I'm basing it, while getting the features to work, on 3 labels, store 1,2 & 3.
Currently I have a class that adds the captions for sList1, sList2 & sList3 - which works, however these are individually set and doesn't use a loop, in fact the class next navigates record 1 with .MoveFirst, then record 2 with .MoveNext, and finally record 3 with .MoveNext (again).

You can see the obvious problem, in that I want it to display more or less records on the main form based on the number of records in recordset.

Considered and attempted a



With rs.

.MoveFirst
Do
...
.MoveNext
Loop While Not rs.EOF

End With


However, how do I let the object of this class know which one to return, I'm guessing that some sort of abstract class, class within a class, or array would be good, but how?! Also looked at Collections, but need more info.

Currently my class looks like this:

Public Function LoadList(ListWeek As String)
On Error GoTo HandleError

Dim rs As Recordset
Dim sQry As String
Dim iRecCount As Integer
Dim i, ciRec As Integer
' Dim cStatus As clsStatus
' cStatus = New clsStatus

LoadList = False

sQry = "SELECT * FROM " & scTableListWeek & " WHERE ([ListWeek] = #" & ListWeek & "#);"

Set rs = CurrentDb().OpenRecordset(sQry)

With rs
If .RecordCount = 0 Then
MsgBox "Cannot find Week List", vbCritical
GoTo Done
End If

i = 1
.MoveFirst

'Do

'cStatus.IndicateStatus (!StoreStatus)
'Me.iStatusColour = cStatus.iStatusColour
Me.sList1 = !StorePrem
.MoveNext
' Me.iStatusColour = cStatus.iStatusColour
Me.sList2 = !StorePrem
.MoveNext
' Me.iStatusColour = cStatus.iStatusColour
Me.sList3 = !StorePrem

'Loop While Not rs.EOF
.Close
End With

Set rs = Nothing

LoadList = True

Done:
Exit Function

HandleError:
MsgBox "Error: Loading List by Week", vbCrLf, Err.Description, vbCritical
Resume Done

End Function



And accessed via Event Procedure behind, combo box select:

Private Sub cmbViewWeek_Change()
Dim cViewWeekOpt As clsStores
Set cViewWeekOpt = New clsStores

Dim dWeek As String
dWeek = cmbViewWeek.Value

cViewWeekOpt.LoadList (dWeek)


lblStore1.Caption = cViewWeekOpt.sList1
' lblStore1.BackColor = cViewWeekOpt.iStatusColour1
lblStore2.Caption = cViewWeekOpt.sList2
' lblStore2.BackColor = cViewWeekOpt.iStatusColour2
lblStore3.Caption = cViewWeekOpt.sList3
' lblStore3.BackColor = cViewWeekOpt.iStatusColour3

If cmbViewWeek.Value = "This Week" Then
lblWeek.Caption = "This Week"
Else
lblWeek.Caption = cmbViewWeek.Value
End If
End Sub


As you can see, I attempted to call another class called clsStatus and use the method IndicateStatus, but my head started to hurt, so I stopped, but here's the attempt anyway:

Option Compare Database
Option Explicit

Private sStatus As String
Public iStatusColour As Integer

Property Get Status() As String
Status = sStatus
End Property

Public Function IndicateStatus(Status As String)
On Error GoTo HandleError

If Status = "Actual" Then
Me.iStatusColour = 65280
ElseIf Status = "Planned" Then
Me.iStatusColour = 33023

End If

Done:
Exit Function

HandleError:
MsgBox "Error: Cannot find store status ", vbCrLf, Err.Description, vbCritical
Resume Done

End Function

Note this code works at getting the 3 values into the labels captions but not quite as I want it, and I want to add the backcolour.

- Check out the DefaultView property of a form--I assume you're talking about forms--which if set to Datasheet or Continuous Forms, automatically repeats the detail section of your form for each record in the recordsource. -- Having a bunch of labels that you explicitly set values to one at a time is just not a workable strategy unless you have way to much time on your hands, and even then it seems sort of masochistic.

- Check out the DefaultView property of a form--I assume you're talking about forms--which if set to Datasheet or Continuous Forms, automatically repeats the detail section of your form for each record in the recordsource. -- Having a bunch of labels that you explicitly set values to one at a time is just not a workable strategy unless you have way to much time on your hands, and even then it seems sort of masochistic.

I'm trying to really get to grips with classes, and I want to be able to run everything via vba, rather then via Access's standard options. They're labels, so that they can be on the main page, and can have a click event. It may seem long winded but it'll really save time and effort later on.