Hanging Forms, Collections.Type

[FmrVBAJunkie]

Hi All,



Very new to Access VBA environment. Was looking for some help.

Issue: Form won't display and Access Freezes

VBA Methodology Being Implemented: I'm using the collections.controltype to auto-bound fields and to pull record information. The forms don't have a lot of fields. Prior to this, I was calling each textbox and using dlookup to get the record information, and never had a problem. The overall code is very simple, I'm just passing the primary key through the main module. I'm also not .repainting or doing any other type of "screenupdating = FALSE" stuff.

What I've Noticed: A) It works intermittently. B) I used the immediate window and saw that the code is executing and ending. What I can't find is why the form does not show up. C) I even put a closeform command at the end of the sub, and that fires off and Access doesn't freeze. I did this because I thought that Access might be stuck in a loop.

*I am aware that there are other VBA techniques that I can use. But, for now I'm comfortable with this way; so I want to figure out what's happening within the scope of using the collections.controltype object reference.

Below is a sample of the code:

    strAr() = Split(Me.Name, "_", -1, vbTextCompare)
    aString = strAr(LBound(strAr))

    For Each ctL In Me.Controls
        With ctL
            If .ControlType = acTextBox Then
                    .Enabled = True
                If Not (.Name = "FieldNameA") Then
                    dString = DLookup("[" & ctL.Name & "]", aString, "[PrimaryKey] = " & PrimaryKey)
                    ctL = dString
                Else
                End If
            End If
        End With
    Next ctL

Can anyone provide any guidance?

Thanks!
FrmVBAJunkie

[FmrVBAJunkie]

uhhhhhhhhh, ok. In Excel I used Application.ScreenUpdating = FALSE. But, in the access environment, I it's Application.Echo FALSE. I just remembered that the screenupdating in excel prevented these types of errors. Is this what I'm missing?

Tanks!

[hapm]

Only to complete the information needed to eventually answer your question: What is the event you use to call your code? Form.Load?

[FmrVBAJunkie]

Yes, I'm using the form.load event. The form can appear underneath two different types of usecases. Either to create a record or to edit a record. I put the application.echo FALSE line in all of my form modules, as well as application.echo TRUE at the end of the sub. So far it looks like that's the culprit. Am I on the right track?

Tanks!

[hapm]

First: Don't spam Echo False, especially when in development, and double especially not if you don't do proper error handling. Access defaults are quite good when it comes to Form repainting, and forcing this to disabled isn't a good idea ad long as you don't know for sure that it will be enabled afterwards.

If you get stuck in a access not reacting state after executing Echo False you can use the direct Input window in the vbe to run Echo True afterwards to restore. Use breakpoints or Debug.Print to check if Echo True is executed in all corner cases.

In your Case I would give Form.Open a try, as long as you don't access the Forms Recordset in your code. Open happens bevor the Form is renderer and you will not need Echo False this way.

[FmrVBAJunkie]

I'll give Form.Open a try. Thanks for the help!