Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2013

    Unanswered: Locker Administration

    I am making a locker administration access db in 2013. This db is to track, and log, users who request a locker.

    There are currently three tables:

    LUtblDept -Table to Store Departments
    tblLockerAdmin -Storage
    tblLockerName - All 108 locker's names live here

    One query:

    qryActiveLocker -to separate currently assigned lockers from past records

    Two forms:
    frmALAdmin -dialog form for maintaining locker assignment status
    frmLockerView - map view of all lockers and current status (Available, Occupied, Overdue)

    I am having difficulty with the loop required to check the status of lockers on frmLockerView on load (or focus). Any help would be appreciated:

    Private Sub Form_Open(Cancel As Integer)
    Const conSQL = "SELECT LockerID, Status FROM tblLockerAdmin"
    Dim rst As DAO.Recordset
    Dim ctrl As Control
    Set rst = CurrentDb.OpenRecordset(conSQL)
    With rst
        Do While Not rst.EOF
            Set ctrl = Me.Controls("cmdLocker" & .Fields("LockerID"))
            If .Fields("Status") = "Occupied" Then
                ctrl.BackColor = vbYellow
                ElseIf .Fields("Status") = "Overdue" Then
                    ctrl.BackColor = vbRed
                    ctrl.BackColor = vbGreen
           End If
        End With
    End Sub
    Any and all help is appreciated.

    This is what I have for assignments per locker cmd:

    DoCmd.OpenForm "frmALAdmin", _
        WhereCondition:="Lockerid = 1", _
    If DLookup("[Status]", "qryActiveLocker", "LockerID = 1") = "Occupied" Then
        cmdLocker1.BackColor = vbYellow
    ElseIf DLookup("[Status]", "qryActiveLocker", "LockerID = 1") = "Overdue" Then
        cmdLocker1.BackColor = vbRed
        cmdLocker1.BackColor = vbGreen
        End If
    This works, but does not fill LockerID if there is no records currently associated.

    Any assistance or insight is truly and greatly appreciated

  2. #2
    Join Date
    Jun 2007
    Maitland NSW,Australia
    In the form open code place the End if before the .MoveNext.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts