Confusing Form_Open error after opening dialog form

[Katerine]

Hi,
I'm getting an odd error that I was hoping somebody could shed some light on.

I've got a form, ContactsForm (naming standard is not mine). The first thing this form does in the OnOpen routine is open another form, called ContactsFilterSortForm, as dialog.

The purpose of ContactsFilterSortForm is to set a really, really complicated filter. It does this by getting the criteria, making an SQL statement based on the criteria, then setting a boolean "Selected" field, for all contacts records that fit the criteria, to True. This is done in cmdOK_Click, just before ContactsFilterSortForm closes.

All this works fine. All the contacts form needs to do is open the form, then set the recordsource to show all contacts where "Selected" = True. Or all contacts period, depending on whether a certain drop-down says "Filter" or "All." The drop-down's default value is "Filter."

Here is the code:
Private Sub EvaluateShowFilterAll()
On Error GoTo Err_EvaluateShowFilterAll

Dim strRS As String

Select Case Me.ComboShowWhat
Case "Filter"
strRS = "ContactsQrySelected"
Case "All"
strRS = "ContactsQry"
End Select

SetRS:
Me.RecordSource = strRS
Me.Requery

Exit_EvaluateShowFilterAll:
Exit Sub

Err_EvaluateShowFilterAll:
MsgBox Err.Description, , "EvaluateShowFilterAll: " & Err.Number
Resume Exit_EvaluateShowFilterAll
End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim bolAdminUser As Boolean

'Get filter information from ContactsFilterSortForm
DoCmd.OpenForm "ContactsFilterSortForm", , , , , acDialog
If Not bolProceed Then
'cancel if the user cancelled out of ContactsFilterSort
Cancel = True
DoCmd.OpenForm "frmSwitchboard"
Exit Sub
End If

EvaluateShowFilterAll

'Determine whether the current user is admin.
'Set admin-level tabs to visible or invisible,
'depending on value of (whether user is admin)
bolAdminUser = _
(UCase(GetUserAccessLevelString) = "ADMIN&quot

Me.Payments.Visible = bolAdminUser
Me.Receivables.Visible = bolAdminUser
Me.Dues.Visible = bolAdminUser
Me.MergeTo.Visible = bolAdminUser
Me.ANA_info.Visible = bolAdminUser

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Description, , "Form_Open: " & Err.Number
Resume Exit_Form_Open
End Sub

The red lines are the ones getting the error, "2467: The expression you entered refers to an object that is closed or doesn't exist." The error, and the places it occurs, indicate that ContactsForm is no longer open. When I close the message, the ContactsForm is indeed not open.

If I comment out the entire area in blue, everything runs fine.

bolProceed (a global variable set by ContactsFilterSortForm) = True. So it isn't that.
It also has nothing to do with the GetUserAccessLevelString function (a global function that returns a string indicating user's access level). Again, if I comment out the area in blue, everything works fine.
One odd thing: when troubleshooting, I set a breakpoint on the "EvaluateShowFilterAll" command line. The first thing I saw, before even starting to step through, was the "This action will reset the current code in break mode. Do you want to stop the running code?" dialog box. I usually only see that if I'm stepping through and hit the stop button.

Any insights? I work with dialog forms all the time, and I've never seen anything like this. Katie

 

[kraxmo]

Are you expecting code execution to stop while the user is entering data in the filter form?

    DoCmd.OpenForm "ContactsFilterSortForm", , , , , acDialog

If so, you need to check if the form is still open with a Do While...Loop:

    DoCmd.OpenForm "ContactsFilterSortForm", , , , , acDialog
    Do While SysCmd(acSysCmdGetObjectState, acForm, &quot;ContactsFilterSortForm&quot;) <> 0
        DoEvents
    Loop
    If Not bolProceed Then ...

Also, what sets variable bolProceed to TRUE? The

If Not bolProceed Then

will always execute, closing the current form and returning to the Switchboard. Jim Kraxberger
Developing Access solutions since 1995
jkraxberger@scp4me.com

 

[kraxmo]

Sorry about the bolProceed question--I didn't see that you were setting it to true!!! Jim Kraxberger
Developing Access solutions since 1995
jkraxberger@scp4me.com

 

[Katerine]

Hi kraxmo,
Thanks for the DoEvents tip... unfortunately, it had no effect. I'm still getting the same errors in those two places. (I kind of didn't think it would fix it... the whole point of opening a form as dialog is to make code pause execution until the opened form is closed again.)

Again, many thanks... any other ideas for what could be wrong? Katie

 

[Katerine]

Oops. I found it. It's always the obvious things

There was a holdover in ContactsFilterSortForm's code (from when it performed a slightly different function, where, in its OnClose event, it also closes ContactsForm.

Again, many thanks Katie

 

[kraxmo]

Instead of using the dot "." syntax to reference control information (an Access 97+ NO-NO, BTW!), try using the bang "!" syntax.

BEFORE:

   Me.Payments.Visible = bolAdminUser
   Me.Receivables.Visible = bolAdminUser
   Me.Dues.Visible = bolAdminUser
   Me.MergeTo.Visible = bolAdminUser
   Me.ANA_info.Visible = bolAdminUser

AFTER:

   Me!Payments.Visible = bolAdminUser
   Me!Receivables.Visible = bolAdminUser
   Me!Dues.Visible = bolAdminUser
   Me!MergeTo.Visible = bolAdminUser
   Me!ANA_info.Visible = bolAdminUser

Jim Kraxberger
Developing Access solutions since 1995
jkraxberger@scp4me.com