Conditional code is stopping at first condition

[banderson]

Hello,
I am not sure why this code is not working. I have a pop-form to edit a drop down list that is found in a control (combo box) on 8 different forms.

On this pop-up form, I have added a "Done" command button. When this button is clicked, I want to requery the control (combo box) on whichever main form is open so that the drop down list includes any edits or new entries that were made on the pop-up form.

I have created a conditional code (If, ElseIf, Else, End If) that looks to see which of the 8 forms is loaded, captures the open form name as a variable, "stForm", and then requeries the control on that form.

Form(stForm).cb oAddr2TypeLKUP. Requery

The problem that I am having is that the code only checks the first condition. After the If condition, it jumps to End If rather than cycling through the rest of the ElseIf conditions. What it means is that when the If condition is false, you get an error message: "The expression you entered refers to an object that is closed or doesn't exist."

When debugging the code, it shows stForm is equal to the form name in the If condition - even if that condition is false (that form is not open).

I cannot figure out why this is happening. Below is the code. Any advice/suggestions would be greatly appreciated.

Banderson

Private Sub btnDone_Click()
On Error GoTo Err_btnDone_Click

Dim stForm As String

If CurrentProject.AllForms("frmSiteAdd").IsLoaded = True Then
    stForm = "frmSiteAdd"
ElseIf CurrentProject.AllForms("frmSiteBasicsEdit").IsLoaded = True Then
    stForm = "frmSiteBasicsEdit"
ElseIf CurrentProject.AllForms("frmContactAdd").IsLoaded = True Then
    stForm = "frmContactAdd"
ElseIf CurrentProject.AllForms("frmContactBasicsEdit").IsLoaded = True Then
    stForm = "frmContactBasicsEdit"
ElseIf CurrentProject.AllForms("frmStaffAdd").IsLoaded = True Then
    stForm = "frmStaffAdd"
ElseIf CurrentProject.AllForms("frmStaffEdit").IsLoaded = True Then
    stForm = "frmStaffEdit"
ElseIf CurrentProject.AllForms("frmBMCAdd").IsLoaded = True Then
    stForm = "frmBMCAdd"
ElseIf CurrentProject.AllForms("frmBMCEdit").IsLoaded = True Then
    stForm = "frmBMCEdit"
Else
    stForm = ""
End If

    DoCmd.Close
    Form(stForm).cboAddr2TypeLKUP.Requery

Exit_btnDone_Click:
    Exit Sub

Err_btnDone_Click:
    MsgBox Err.Description
    Resume Exit_btnDone_Click
    
End Sub

[TheSmileyCoder]

Hi there

1) Assuming your form is opened as a result of NotInList, and the form is open in dialog mode, I think you could simply do in your button:

Docmd.Close
Screen.ActiveControl.Requery

2) You dont need to compare to true. The IsLoaded will return either true or false, so just use:

If CurrentProject.AllForms("frmSiteAdd").IsLoaded Then

3) The IsLoaded will still return true, if the form is open in designview. A custom function can take care of that:

Public Function testOpen(strFormName As String) As Boolean
    
    If CurrentProject.AllForms(strFormName).IsLoaded Then
        If CurrentProject.AllForms(strFormName).CurrentView = acCurViewFormBrowse Then
            testOpen = True
            Else
            MsgBox "Please close [" & strFormName & "].", vbOKOnly
            testOpen = False
        End If
        
        Else
        testOpen = False
    End If

End Function

I hope this solves your problem or at least gives a nudge in the right direction :)

4) Another option alltogether would be to pass the forms name in the OpenArgs.

[ADezii]

Try:

Dim obj As AccessObject
Dim dbs As Object

Set dbs = Application.CurrentProject

'Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms
  If obj.IsLoaded = True Then
    Forms(obj.Name).cboAddr2TypeLKUP.Requery
  End If
Next obj

[banderson]

Thanks for your responses SmileyOne and ADezii!

The pop up form is opened directly from the combo box, which means the combo box is the active control. Thus, the simple code SmileyOne suggested worked like a charm!

Docmd.Close 
Screen.ActiveControl.Requery

I love when the answer makes it more simple!
For what is is worth, I tried ADezii's code and that worked as well. Will keep it in my "toolbox" for the future.

Thanks again,
Banderson