Looking for suggestion

[wh00t]

I currently have working within a small access db a form which contains an overview of a trailer park, which is a series of boxes containing dlookups which obtain the number of the trailer that is occupying that space, the background of these boxes also changes colour depending on the status of the trailer, the colour values are also held in boxes using dlookups. This refreshes every 60 seconds to provide an up to date image for the user, the following script also activiates on the refresh in order to update the box colours.

Dim db As Database
Dim rs As Recordset
Dim tID As String
Dim ctID As String
Dim Num As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Bay")

rs.MoveFirst

Do While Not rs.EOF
On Error Resume Next

tID = "t" & rs![Bay]
ctID = rs![Bay]

If IsNull(Me.Controls(tID)) Then
    Me.Controls(ctID).BackColor = 12632256
End If

If Not IsNull(Me.Controls(tID)) Then
    Me.Controls(ctID).BackColor = Me.Controls(tID)
End If

rs.MoveNext

Loop

I have been requested to provide this on a larger scale for a trailer park with 140 spaces and many more users, again they require it to refresh every 60 seconds, I am concerned that the amount of data being refreshed every 60 seconds will cause performance issues for the users, but I am stuck for any alternative.

Does anyone have a suggestion as to another way to do this, or other programs that could link to the database and display this whilst updating the view every 60 seconds?

 

[The_Doc_Man]

My first thought is that you should be able to handle 140 basic entities in 60 seconds without help unless you are running on Windows 3.1 and Access 2.0 as your basis - on a 80486 machine at 33 MHz. THEN you would have a bit of a problem. Any modern machine should be able to do this amount of work in a snap.

However, you can help yourself in various ways by pre-computing certain factors when something changes rather than re-evaluating everything when you do the scan. I.e. MOST of your data is static until someone moves, right? For the stuff that is static, pre-compute your display parameters and other stuff. If you are worried about performance, then separate the tasks of formatting and displaying. Do the formatting at data entry time. Do the displaying once every 60 seconds.

I.e. if you are going to show a slot in a particular color based on occupancy, then when you make the data entry changing occupancy, store the color code for the field. Then you just ALWAYS load the color to the appropriate slot when doing your refresh operation. No logic required here. All the if-then-else stuff is at data entry time, which doesn't matter if it isn't super fast. It can take an extra 1/10th of a second.

 

[wh00t]

I'm having trouble with the concept of a form that displays the layout of a trailer park being dynamic enough to require a timer event to update it every 60 seconds. Are we talking about slots that are rented by the quarter hour? I heard of a parking lot, in Denmark I think, that operated like that. You chose the companion of your choice, drove in, did your business, and then left.

similar Pat, there is one trailer park and there are 5 areas of 2 warehouses that change the status and positions of the trailers, there is also 1 point (which require this form) that monitor when trailers are in a certain state so that they can inform the drivers that trailer xxx is ready, go collect it from xxx.