Refactoring advice needed to improve speed of big and dirty lookup chain

[Jon-G]

Im looking for some guidance on how the way I have structured a forms 'status' update can be streamlined to make it execute much faster.

OK here's the grit: I have a homescreen which represents the top down view of a real estate project consisting of >700 plots. Each plot is represented by a label so that I can change the back colour depending on the status of the sale of the land.

On the forms load I call a 'StatusUpdate' procedure which is looping through all controls on the form. If I find a label that Im using to represent a plot (I know because the label.name is in a <block>_<plot> format i.e A_1) then I pass the control to procedures which parse the name and return get the block and plot numbers back.

These values are then used to setup the onClick Event and onMouseMove for each button. I then also pass these to another function which looks up the statusID value for that plot within the plots table. This function then hits up the StatusLookupTable to get the RGB values that I have assigned against each statusID, and returns a byte array with the RGB value. This RGB lookup is happening for every plot.

My loop then sets the back colour of the label with this colour, and moves onto the next control.

At the moment this works, but it takes about 15 seconds to load up this form. I want to improve the loading time, and thought about creating a global 2d array of the each status and its RGB value which I populate on startup of my application, meaning I dont have to keep hitting up another two tables for each control found on my form every time the map form is refreshed.

I've not yet implemented updating the form when the status of a plot is changed by a user either. Once rolled out the database will have around 5 users on the network, and I will somehow need to reflect the changes from one user to the other users through the 'map' form.

If the map is taking this long to load, then I cant really achieve the status update this using a timer calling the StatusUpdate function as the system is unusable while the refresh is running. Ideally Id want to use conditional formatting on the labels and bind these to their corresponding records in the Plots table, then use sort of subscription to refresh the form when the table is changed. However, my understanding at the moment is as follows:

• Labels cannot be bound
• Labels cannot use conditional formatting
• The jet database doesn't give the option to fire any code on table update

Any ideas on how to streamline this would be greatly appreciated. Pseudo code or code samples would also help me very much!!

TIA

[June7]

True, labels can't use conditional formatting.

However, a textbox can emulate an unassociated label and that can have conditional formatting. If the ControlSource is an expression (textbox is not bound), could only use this in Single View (not Datasheet or Continuous).