Results 1 to 2 of 2
  1. #1
    GeekInOhio's Avatar
    GeekInOhio is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2012
    Posts
    25

    VBA Optimization - Forms in Object Variables

    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?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What happened - error message, wrong results, nothing?

    Try using the Controls collection. I haven't tried setting a form/report as an object (maybe I should) but I do set variables to form/report name and dynamically refer to form/report controls. Examples from my db:
    ........................
    If Not IsNull(Forms(strForm).Controls("tbxLabNum")) Then
    ....................
    FinenessModulus = Format(( _
    IIf([Form_DataSoilsAgg1].Controls(S & "OneHalf") > 0, 100 - [Form_DataSoilsAgg1].Controls(S & "OneHalf"), 0) _
    + IIf(Nz([Form_DataSoilsAgg1].Controls(S & "ThreeFourth"), 0) > 0, 100 - [Form_DataSoilsAgg1].Controls(S & "ThreeFourth"), 0) _
    + IIf(Nz([Form_DataSoilsAgg1].Controls(S & "ThreeEighth"), 0) > 0, 100 - [Form_DataSoilsAgg1].Controls(S & "ThreeEighth"), 0) _
    + IIf(Nz([Form_DataSoilsAgg1].Controls(S & "No4"), 0) > 0, 100 - [Form_DataSoilsAgg1].Controls(S & "No4"), 0) _
    + IIf(Nz([Form_DataSoilsAgg1].Controls(S & "No8"), 0) > 0, 100 - [Form_DataSoilsAgg1].Controls(S & "No8"), 0) _
    + IIf(Nz([Form_DataSoilsAgg1].Controls(S & "No16"), 0) > 0, 100 - [Form_DataSoilsAgg1].Controls(S & "No16"), 0) _
    + IIf(Nz([Form_DataSoilsAgg1].Controls(S & "No30"), 0) > 0, 100 - [Form_DataSoilsAgg1].Controls(S & "No30"), 0) _
    + IIf(Nz([Form_DataSoilsAgg1].Controls(S & "No50"), 0) > 0, 100 - [Form_DataSoilsAgg1].Controls(S & "No50"), 0) _
    + IIf(Nz([Form_DataSoilsAgg1].Controls(S & "No100"), 0) > 0, 100 - [Form_DataSoilsAgg1].Controls(S & "No100"), 0)) _
    / 100, "0.0")
    ........................
    If CurrentProject.AllForms("Sample").IsLoaded Then
    Form_Sample.Controls("cbx" & Me.cbxField).Requery
    End If
    ........................
    I often use the Me qualifier:
    With Me.
    .cbxFor1.Enabled = False
    .cbxFor1.TabStop = False
    .cbxField2.Enabled = False
    End With
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using Public variables between forms/queries/reports
    By dcrake in forum Sample Databases
    Replies: 2
    Last Post: 12-25-2015, 05:44 PM
  2. Forms missing from Object Browser
    By hertfordkc in forum Access
    Replies: 1
    Last Post: 10-17-2011, 05:18 PM
  3. Replies: 1
    Last Post: 09-03-2011, 07:01 PM
  4. Query Optimization Inquiry
    By Nobody in forum Queries
    Replies: 1
    Last Post: 07-29-2010, 08:53 AM
  5. Variables in Forms
    By NewDeveloper in forum Forms
    Replies: 1
    Last Post: 06-20-2010, 08:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums