1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Software Development' started by MRdNk, Sep 28, 2008.

Thread Status:
Not open for further replies.
  1. MRdNk

    MRdNk Thread Starter

    Apr 7, 2007
    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.
    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
                 'cStatus.IndicateStatus (!StoreStatus)
                 'Me.iStatusColour = cStatus.iStatusColour
                 Me.sList1 = !StorePrem
                ' Me.iStatusColour = cStatus.iStatusColour
                 Me.sList2 = !StorePrem
                ' Me.iStatusColour = cStatus.iStatusColour
                 Me.sList3 = !StorePrem
            'Loop While Not rs.EOF
        End With
        Set rs = Nothing
        LoadList = True
        Exit Function
        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"
            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
        Exit Function
        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.
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 733,556 other people just like you!

Similar Threads - Solved Access Class
  1. leachim
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/754084

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice