Refresh ComboBox After Adding New Values via a Separate Form

[bitsnbytes64]

Hi,

I've been having a similar issue to what is described in the "refresh a form" post with a ComboBox that is not being refreshed after adding a new value on a seperate form. The second form is being opened via the standard DoCmd call.

I've tried adding Requery in AfterUpdate, Form_Current, etc. to no avail.... The test form has the combobox and a command button.

Here's my test code:

Private Sub cmdAddCatgCd_Click()
On Error GoTo Err_cmdAddCatgCd_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "subfrmHierCatgCd"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_cmdAddCatgCd_Click:
    Exit Sub
 
Err_cmdAddCatgCd_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddCatgCd_Click
End Sub
 
Private Sub Form_AfterUpdate()
    Me.Combo21.Requery
End Sub
 
Private Sub Form_Current()
    Me.Combo21.Requery
End Sub

cmdAddCatgCd_Cl ick opens another form to maintain the code values that are populated in Combo21.

So far, the only way I've been able to get this to work is by adding a "Refresh" command button with the following code:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
 
    Me.Combo21.Requery
 
Exit_Command25_Click:
    Exit Sub
 
Err_Command25_Click:
    MsgBox Err.Description
    Resume Exit_Command25_Click
 
End Sub

I'd prefer having the requery happen automatically for both forms and subforms.....

Thanks, Ed.

[Rabbit]

Hi,

I've been having a similar issue to what is described in the "refresh a form" post with a ComboBox that is not being refreshed after adding a new value on a seperate form. The second form is being opened via the standard DoCmd call.

I've tried adding Requery in AfterUpdate, Form_Current, etc. to no avail.... The test form has the combobox and a command button.

Here's my test code:

Private Sub cmdAddCatgCd_Cl ick()
On Error GoTo Err_cmdAddCatgC d_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "subfrmHierCatg Cd"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddCatg Cd_Click:
Exit Sub

Err_cmdAddCatgC d_Click:
MsgBox Err.Description
Resume Exit_cmdAddCatg Cd_Click
End Sub

Private Sub Form_AfterUpdat e()
Me.Combo21.Requ ery
End Sub

Private Sub Form_Current()
Me.Combo21.Requ ery
End Sub

cmdAddCatgCd_Cl ick opens another form to maintain the code values that are populated in Combo21.

So far, the only way I've been able to get this to work is by adding a "Refresh" command button with the following code:

Private Sub Command25_Click ()
On Error GoTo Err_Command25_C lick

Me.Combo21.Requ ery

Exit_Command25_ Click:
Exit Sub

Err_Command25_C lick:
MsgBox Err.Description
Resume Exit_Command25_ Click

End Sub

I'd prefer having the requery happen automatically for both forms and subforms.....

Thanks, Ed.

Have you tried the solutions that were posted in that other thread?

[bitsnbytes64]

Have you tried the solutions that were posted in that other thread?

Yes. Originally, I replied to that post to imply this but NeoPa told me I had to start a new thread so I did.

I think part of the problem I'm having is that there's no direct link between the forms. Once the second form is opened, the code being executed in the main form (DoCmd) continues to completion. It doesn't wait for the user to close the second form....

Code from Command Button to open second form:

Private Sub cmdAddCatgCd_Click()
On Error GoTo Err_cmdAddCatgCd_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "subfrmHierCatgCd"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_cmdAddCatgCd_Click:
    Exit Sub
 
Err_cmdAddCatgCd_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddCatgCd_Click
End Sub
 

Any ideas ?

Thanks, Ed.

[Rabbit]

What about putting the refresh/requery code in the second form? Perhaps in the On Close event it can also refresh/requery the first form.

[Denburt]

I saw the other thread Bits and don't take offense to the tone that you might think someone came across in, we all see what is typed but what we don't see are facial expressions etc. Many times when I was a forum noob (new guy) I took a completely harmless comment or even a stern one, and thought it seemed harsh. Most of us here (if not all) are simply volunteers helping each other.

On to your issue as Rabbit stated use the "on close" event of the second form to save the record on the second form, and then requery the main forms combo box.

DoCmd.RunCommand (acCmdSelectRecord)
    DoCmd.RunCommand (acCmdSaveRecord)
Forms!FirstForm!ComboBox1.requery

This should resolve your problem. Also a link to the other thread would be nice I am sure you were probably looking for a way to do that. It's the round world looking thing next to the square pic in the toolbar on top.

http://www.thescripts.com/forum/thread639998.html

[bitsnbytes64]

I saw the other thread Bits and don't take offense to the tone that you might think someone came across in, we all see what is typed but what we don't see are facial expressions etc. Many times when I was a forum noob (new guy) I took a completely harmless comment or even a stern one, and thought it seemed harsh. Most of us here (if not all) are simply volunteers helping each other.

On to your issue as Rabbit stated use the "on close" event of the second form to save the record on the second form, and then requery the main forms combo box.

DoCmd.RunCommand (acCmdSelectRecord) DoCmd.RunCommand (acCmdSaveRecord) Forms!FirstForm!ComboBox1.requery
 

This should resolve your problem. Also a link to the other thread would be nice I am sure you were probably looking for a way to do that. It's the round world looking thing next to the square pic in the toolbar on top.

http://www.thescripts.com/forum/thread639998.html

First off, thanks for the advice !

Regarding the questions I had.... If the user does not close the form even after adding a record or two, the Requery would not get executed as the Close event was not triggered ? I may have to add that to other events as well (most likely as a public routine) ?

Additionally, for every form with that combobox that calls the maint form I'm assuming I'd have to add a Requery statement for each and every form ?

DoCmd.RunCommand (acCmdSelectRecord) DoCmd.RunCommand (acCmdSaveRecord) Forms!FirstForm!ComboBox1.requery Forms!SecondForm!ComboBox1.requery Forms!ThirdForm!ComboBox1.requery Forms!FourthForm!ComboBox1.requery Forms!SubForm1!ComboBox1.requery Forms!SubForm2!ComboBox1.requery
 

Will an error be generated if a form that's being requeried (e.g. FourthForm) is not open ?

Thanks for the help !

Ed.

[Denburt]

Why would you want so many forms open at once? That approach does not seem to be very efficient at all. You can check to see if a form is open I utilize the following function.

Function IsLoaded(strFrmName As String) As Boolean
    Const conFormDesign = 0
    Dim intX As Integer
    IsLoaded = False
    For intX = 0 To Forms.Count - 1
        If Forms(intX).FormName = strFrmName Then
            If Forms(intX).CurrentView <> conFormDesign Then
                IsLoaded = True
                Exit Function
            End If
        End If
    Next
End Function

Then simply call the function:

if isloaded("YourFormName") then Forms!YourFormName.requery

[bitsnbytes64]

Why would you want so many forms open at once? That approach does not seem to be very efficient at all. You can check to see if a form is open I utilize the following function.

Function IsLoaded(strFrmName As String) As Boolean
    Const conFormDesign = 0
    Dim intX As Integer
    IsLoaded = False
    For intX = 0 To Forms.Count - 1
        If Forms(intX).FormName = strFrmName Then
            If Forms(intX).CurrentView <> conFormDesign Then
                IsLoaded = True
                Exit Function
            End If
        End If
    Next
End Function

Then simply call the function:

if isloaded("YourFormName") then Forms!YourFormName.requery

Thanks, I'll try your suggestion.

Not all of the forms will be opened at once, hence my question about when the form is not opened. The user should only have the main data entry form and the code maintenance form opened at a given time....

Thanks again, Ed.

[bitsnbytes64]

Thanks, I'll try your suggestion.

Not all of the forms will be opened at once, hence my question about when the form is not opened. The user should only have the main data entry form and the code maintenance form opened at a given time....

Thanks again, Ed.

Hi Denburt,

A quick question. I have a routine that checks is a Form is opened already but it doesn't use a FOR loop. It uses SysCmd to check for the specified form. Is one better than the other ?

Public Function FormIsOpen(ByVal strFormName As String) As Boolean
 
Const conDesignView = 0
Const conObjStateClosed = 0
 
FormIsOpen = False
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
    If Forms(strFormName).CurrentView <> conDesignView Then
        FormIsOpen = True
    End If
End If
 
End Function
 

Thanks, Ed.

[Denburt]

I think your method would be much more efficient, thanks for posting it. A lot of my code including the code I posted travels around in my databases I start a new db and the first thing I do is import several modules and off I go. The code I posted has been with me since I started with MS Access and that was way to many years now.... I think I found it on one of Microsoft's examples somewhere.