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
tblLockerName - All 108 locker's names live here
qryActiveLocker -to separate currently assigned lockers from past records
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)
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
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
This works, but does not fill LockerID if there is no records currently associated.
Any assistance or insight is truly and greatly appreciated