Requery Combobox after Updating List in Another Form

[Organizer]

I am using Microsoft Access 2007. I have a list in a dropdown combobox (Field_One) in a form (Form_One). Users have the option to click a button if the item they need does not appear in the combobox, and another form, (Form_Two) opens to which they can add the new item (in Field_Two).

After adding the new item in F_Two I want the combobox in F_One to requery without having to close and reopen F_One.

I think I can do this with an After_Update event, but to which object should the event apply? Or is there a better way to do it?

Thank you

[hype261]

I am using Microsoft Access 2007. I have a list in a dropdown combobox (Field_One) in a form (Form_One). Users have the option to click a button if the item they need does not appear in the combobox, and another form, (Form_Two) opens to which they can add the new item (in Field_Two).

After adding the new item in F_Two I want the combobox in F_One to requery without having to close and reopen F_One.

I think I can do this with an After_Update event, but to which object should the event apply? Or is there a better way to do it?

Thank you

There are numerous ways you can do this. One of the ways I have overcome this is to put some code in Form_Two On Close event. Form_Two would have to be a modal for this to work consistantly though.

This is just rough code, but should get you started.

Private Sub Form_Close()
    
    If (IsLoaded("Form_One")) Then
        Forms![Form_One]!myComboBox.Requery
    End If
End Sub

[Organizer]

There are numerous ways you can do this. One of the ways I have overcome this is to put some code in Form_Two On Close event. Form_Two would have to be a modal for this to work consistantly though.

This is just rough code, but should get you started.

Private Sub Form_Close()
    
    If (IsLoaded("Form_One")) Then
        Forms![Form_One]!myComboBox.Requery
    End If
End Sub

Here is what happens when I try this.
F_Organizations is the real name of Form_One, OrgNameID is the combobox within F_Organizations that I want to have requeried.

Here is the text that I put in the On Close event for Form_Two.

Private Sub Form_Close()

If (IsLoaded(F_Org anizations)) Then
Forms![F_Organizations]!OrgNameID.Requ ery
End If

End Sub

I get an error message that says "Compile Error: Sub or Function Not Defined" and the term "IsLoaded" is highlighted.

The combobox field is structured as a lookup in the table that underlies F_Organizations . Could this be the problem and would this code work only if the combobox was created within the form itself?

[Organizer]

I found something online that says that IsLoaded needs to be defined, and to copy it from the Northwind database and paste it into the module. It doesn't appear that Northwind was loaded on this computer (someone else set it up for me - I had it on my previous computer) and also I don't know exactly what "paste it into the module" means. Does anyone have that code and can you tell me where to paste it?

Thank you.

[hype261]

I found something online that says that IsLoaded needs to be defined, and to copy it from the Northwind database and paste it into the module. It doesn't appear that Northwind was loaded on this computer (someone else set it up for me - I had it on my previous computer) and also I don't know exactly what "paste it into the module" means. Does anyone have that code and can you tell me where to paste it?

Thank you.

isLoaded is a common function that alot of Access programmers use. I am surprised that it hasn't be made a standard access function.

Here is the isloaded function from one of the apps at my work. I don't know if it is the same as the Northwind one.

Function IsLoaded(MyFormName)
On Error GoTo Err_4

    '  Determines if a form is loaded.
    
    Const FORM_DESIGN = 0
    Dim i As Integer
    
    IsLoaded = False
    For i = 0 To Forms.Count - 1
        If Forms(i).FormName = MyFormName Then
            If Forms(i).CurrentView <> FORM_DESIGN Then
                IsLoaded = True
                Exit Function  ' Quit function once form has been found.
            End If
        End If
    Next

Exit_4:
    Exit Function

Err_4:
    MsgBox Error$
    Resume Exit_4
    
End Function

Just copy the code and create a new module in Access and paste it in.

[Organizer]

isLoaded is a common function that alot of Access programmers use. I am surprised that it hasn't be made a standard access function.

Here is the isloaded function from one of the apps at my work. I don't know if it is the same as the Northwind one.

Function IsLoaded(MyFormName)
On Error GoTo Err_4

    '  Determines if a form is loaded.
    
    Const FORM_DESIGN = 0
    Dim i As Integer
    
    IsLoaded = False
    For i = 0 To Forms.Count - 1
        If Forms(i).FormName = MyFormName Then
            If Forms(i).CurrentView <> FORM_DESIGN Then
                IsLoaded = True
                Exit Function  ' Quit function once form has been found.
            End If
        End If
    Next

Exit_4:
    Exit Function

Err_4:
    MsgBox Error$
    Resume Exit_4
    
End Function

Just copy the code and create a new module in Access and paste it in.

One problem solved. I don't get the error message now.

I got an error message and had to add "End Function" after "Resume Exit_4" but then it worked (perhaps that would have been obvious to most people - I'm pretty much of a novice with code).

However, it still doesn't seem to requery. If I make a change to an existing entry in Form_Two, those changes do show up in F_Organizations . But if I actually add a new entry, that still doesn't show up until I close and reopen F_Organizations .

I did check to be sure my event procedure is still there on close for Form_Two, and it is.

Thank you.

[hype261]

One problem solved. I don't get the error message now.

I got an error message and had to add "End Function" after "Resume Exit_4" but then it worked (perhaps that would have been obvious to most people - I'm pretty much of a novice with code).

However, it still doesn't seem to requery. If I make a change to an existing entry in Form_Two, those changes do show up in F_Organizations . But if I actually add a new entry, that still doesn't show up until I close and reopen F_Organizations .

I did check to be sure my event procedure is still there on close for Form_Two, and it is.

Thank you.

Have you made sure that the new entry was saved to the table or isn't being filtered out in the query for the combo box?