All,
I'm always looking for ways to sharpen the saw when it comes to programming. I recently came across a tutorial that has a section on optimizing VBA, and specifically recommends the following:
If you refer to an object more than once in a section of code, assign it to an object variable. Every time you reference an object, Access has to perform some work to figure out which object you are referring to. This adds overhead to your code each time the object is referenced. But if you assign the object to an object variable, Access "finds" the object once and caches the reference in memory. So after the first reference, you can refer to the object through the object variable and your code will run faster.
Source: http://etutorials.org/Microsoft+Prod...rate+VBA+Code/
Makes sense to me.
I have a sub that updates around 250 labels on an unbound form via a loop through a returned SQL recordset. (Basically, updating 6 labels per day on a 42 day calendar.) Currently the code explicitly states the path to each label control, like this:
Code:
Forms!frmCalendarRecord_UserMonth.txtKeyDate = myTable.Fields.Item(1)
Based on what I read, I thought I could speed it up a bit by first declaring the Form as an object, and then just updating the controls from there.
I got as far as setting my form as an object, then I got stuck.
Code:
Dim myProject As Object
Dim myForm As AccessObject
Set myProject = Application.CurrentProject
Set myForm = myProject.AllForms("frmCalendarRecord_UserMonth")
It appears that I can access the properties of the form itself, but not the controls on it. Am I barking up a nonexistent tree, misunderstanding what I read, or just approaching it wrong?