MS Access vba Class For Label = 0 to 2 return RecordSet 0 to 2

[MRdNk]

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.
[INDENT]
.MoveFirst
Do
...
.MoveNext
Loop While Not rs.EOF
[/INDENT]
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.

 

[MarkK]

- 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.

 

[MRdNk]

- 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.