Resolve Dynamic Reference

[2646186]

Hello. If anyone can help me out, I would greatly appreciate it!
I'm using Access 2000.

I have created a form with 60 text boxes side by side to represent a
large "status bar". The code below works great for 20 text boxes, but
when I copy the code for the other 40 Cases (each case representing a
text box for a total of 60 Cases), the program is too large to
compile. 64k is the limitation. My program is around 153k.

Private Sub Form_Timer()

Dim MyDB As Database
Dim tblHold As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set tblHold = MyDB.OpenRecordset("tbl_Hold", DB_OPEN_TABLE)

DoCmd.OpenQuery "qry_Update_tbl_Hold Paused False"
DoCmd.OpenQuery "qry_Update_tbl_Hold Complete False"

Dim BarTime, FlashTime, StartTime, StopTime, CurrentTime,
HoldPaused, PausedTime, PausedTimeTotal, PausedSec, PausedMin, Finish,
TotalTime
Dim c_Blue, c_Orange, c_Yellow, c_Red, c_Green, c_Pink, c_Brown,
c_Black, c_Flesh, c_Grey, c_White, c_dk_Grey
Dim Bar As Integer

c_Blue = 16711680
c_Orange = 26367
c_Yellow = 65535
c_Red = 255
c_Green = 65280
c_Pink = 16711935
c_Brown = 13209
c_Flesh = 10079487
c_Grey = 12632256
c_White = 16777215
c_dk_Grey = 8421504
c_Black = 0

'Bartime = 180 seconds when Goal is 3.00 hours
'Bartime = 165 seconds when Goal is 2.75 hours
'Bartime = 150 seconds when Goal is 2.50 hours
'Bartime = 135 seconds when Goal is 2.25 hours
'Bartime = 120 seconds when Goal is 2.00 hours
'Bartime = 105 seconds when Goal is 1.75 hours
'Bartime = 90 seconds when Goal is 1.50 hours
'Bartime = 75 seconds when Goal is 1.25 hours
'Bartime = 60 seconds when Goal is 1.00 hours
'Bartime = 45 seconds when Goal is .75 hours
'Bartime = 30 seconds when Goal is .50 hours
'Bartime = 15 seconds when Goal is .25 hours
'Bartime = 1 second when Goal is 1 minute
BarTime = 1
HoldRun = 0
StartTime = Timer

For Bar = 1 To 61 'case 61 is used in Case Else to end the
Form_Timer() so it runs only one time
Select Case Bar
Case 1
Me![CurrentTask] = "Start Wash-up"
CurrentTask.BorderColor = c_Blue
CurrentTask.ForeColor = c_Blue
Do While HoldRun < BarTime
DoEvents
Set tblHold = MyDB.OpenRecordset("tbl_Hold",
DB_OPEN_TABLE)
If Not tblHold![Complete] Then
If Not tblHold![Paused] Then
t1.BackColor = c_Blue
HrsRun.ForeColor = c_Blue
MinRun.ForeColor = c_Blue
SecRun.ForeColor = c_Blue
Flash = Timer
Do While Timer - Flash < 1
DoEvents
Loop
HoldRun = Timer - Flash + RunTimeTotal
RunHrs = Int(HoldRun / 3600)
RunMin = Int((HoldRun - (RunHrs * 3600)) / 60)
RunSec = Int(HoldRun - (RunHrs * 3600) -
(RunMin * 60))
Me![HrsRun] = RunHrs
Me![MinRun] = RunMin
Me![SecRun] = RunSec
RunTimeTotal = HoldRun
Else
PausedTime = Timer
GoSub Blink
End If
Else
GoTo MakeReadyComplete
End If
If Not tblHold![Complete] Then
If Not tblHold![Paused] Then
t1.BackColor = c_White
Flash = Timer
Do While Timer - Flash < 1
DoEvents
Loop
HoldRun = Timer - Flash + RunTimeTotal
RunHrs = Int(HoldRun / 3600)
RunMin = Int((HoldRun - (RunHrs * 3600)) / 60)
RunSec = Int(HoldRun - (RunHrs * 3600) -
(RunMin * 60))
Me![HrsRun] = RunHrs
Me![MinRun] = RunMin
Me![SecRun] = RunSec
RunTimeTotal = HoldRun
Else
PausedTime = Timer
GoSub Blink
End If
Else
GoTo MakeReadyComplete
End If
Loop
t1.BackColor = c_Blue
Case 2 thru 61...

 

[Albert D. Kallal]

t1.BackColor = c_Blue 'is used in the code above


You can use a string to refecne any text box....

dim i as interger
dim strCPtr as string

for i = 1 to 61

strCPtr = "t" & i

me(strCPTr). BackColor = c_Blue 'is used in the code above
next i

The above would set contorl t1 to t61 as c_blue