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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

MRdNk

Thread Starter
Joined
Apr 7, 2007
Messages
439
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

Code:
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:
Code:
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:
Code:
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:

Code:
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top