Too many controls (1 Viewer)

[steve1111]

Hello all,

I am looking for some basic suggestions on how to increase the speed in which my form and subforms load in my particular scenario. Attached is a screenshot of the form I am using to bring in a drivers work schedule for each day.

I already query just the records from the rides table pertaining to just that work week rather than the whole table. The issue is each driver schedule has 130 textboxes that cross-reference the time, vs the day and matches up to the driver. I have a function that loops through all the controls on the subform to lookup that combination.

My issue is that when the form loads or updates it takes 10-15 seconds for all three drivers showing to load.

Is there anything I can do to speed this up more without compromising the layout? Would a report help? Would changing the control source of each textbox help rather than the function looping through all the controls on the subform?

This form would be updated often with added, edited and delete records so I need to keep the data from just being a snapshot. Each cell has a right click menu so a variety of options for that ride can be triggered.

I am hoping for some suggestions to help out, this is exactly how I need the form to work, just faster, I know 130 controls per subform is too many.

Thanks

 

[arnelgp]

You have subforms on the first screen. And i think those bottons on top are for tab controls which host another subform whi h may cintain sub subforms.

Try delaying the loading of records on the forms load event by setting the recordsource to blank record and only populate on the click of those buttons.

 

[steve1111]

Hi arnelgp,

I do have the top of the form load when the main form loads, takes under 1 sec. The update button them loads each "grid" subform under the driver only when the update button is clicked. This step is where it is taking so long because there are three subforms (on for each driver) that all have 130 textboxes in them looking up the results of a query.

My VBA code uses dlookup and dcount and i heard that does slow it down a bit, but i am not sure how to structure this differently to speed it up. the basic code for each textbook the form loops is:

dcount rideID for driver, date and time
if > 1 then textbox.value = dcount & Rides
else
textbox.value = dlookup the rideID number

 

[arnelgp]

Hi again.

Instead of lookups, use query which output the fields you need. Add the count there also. Make the form's driver id as criteria. Then you only need to open the query as recordset to fill those textboxes.

 

[steve1111]

Ah, I see where you are going. in VBA setting the recordset only runs it once for the rest of the module? Then do I just write a SQL query referencing the open recordset? I have done recordsets and SQL queries in VBA just never together.

 

[arnelgp]

Create a query of all the info you need based on your form's driver id.
Ooen the recirdset in vba and gather the info you need. Close the rs. On diff driver do the same.