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_Click 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_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_Click 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.

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.

[bitsnbytes64]

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.

Thanks for the compliment !

One more if you don't mind.... If the user does not close the maint form and returns to the calling form, where would the requery function be called from ?

Thanks, Ed.

[bitsnbytes64]

Thanks for the compliment !

One more if you don't mind.... If the user does not close the maint form and returns to the calling form, where would the requery function be called from ?

Thanks, Ed.

Hey Denburt,

In addition to my question above, if I call the routine to determine if a form is opened from the maintenance form, where would I get the form's name from, in order to determine whther it's opened or not ?

For example: User clicks on command button on FormA which uses DoCmd to open MaintForm1. The user updates MaintForm1 then closes it.

What should happen is MaintForm1's Form_Close event triggers which calls SysCmd to determine if FormA is opened in order to Requery FormA's ComboBox.

Since the call to SysCmd occurs in MaintForm1, how will / does FormA's name get passed into the SysCmd call to check FormA's state ?

Thanks, Ed.

[Denburt]

Thanks for the compliment !

One more if you don't mind.... If the user does not close the maint form and returns to the calling form, where would the requery function be called from ?

Thanks, Ed.

Private Sub Form_LostFocus()

Make sure you save the record if it is completed otherwise let them know it isn't complete in a message box.

:)

[NeoPa]

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

CurrentDB.Forms is a collection of all the open forms for the current database.
Referring to a form in this collection by name is a very quick way of determining if a form is open.

Function IsLoaded(strFrmName As String) As Boolean
    Dim strName as String
 
    On Error Resume Next
    strName = CurrentDB.Forms(strFrmName)
    IsLoaded = (strName = strFrmName)
End Function

Your Forms() is the same, as it also refers to CurrentDB.Forms.

[Denburt]

CurrentDB.Forms is a collection of all the open forms for the current database.
Referring to a form in this collection by name is a very quick way of determining if a form is open.

Function IsLoaded(strFrmName As String) As Boolean
    Dim strName as String
 
    On Error Resume Next
    strName = CurrentDB.Forms(strFrmName)
    IsLoaded = (strName = strFrmName)
End Function

Your Forms() is the same, as it also refers to CurrentDB.Forms.

Yes you are absolutely right I really was tempted to remove that post... It was a really OLD piece of code that I haven't looked at since forever.

Thats one thing about being in here I get to clean out the closets, and have help doing it. :)

[Denburt]

Hey Denburt,

In addition to my question above, if I call the routine to determine if a form is opened from the maintenance form, where would I get the form's name from, in order to determine whther it's opened or not ?

For example: User clicks on command button on FormA which uses DoCmd to open MaintForm1. The user updates MaintForm1 then closes it.

What should happen is MaintForm1's Form_Close event triggers which calls SysCmd to determine if FormA is opened in order to Requery FormA's ComboBox.

Since the call to SysCmd occurs in MaintForm1, how will / does FormA's name get passed into the SysCmd call to check FormA's state ?

Thanks, Ed.

Use the on click event and do something like:

Docmd.open forms!MaintForm1
Forms!MaintForm1.tag = me.name
 

Then you know which form called maintForm1.


BTW Sorry I didn't see your question yesterday i don't know how I missed it.

[bitsnbytes64]

Use the on click event and do something like:

Docmd.open forms!MaintForm1
Forms!MaintForm1.tag = me.name
 

Then you know which form called maintForm1.


BTW Sorry I didn't see your question yesterday i don't know how I missed it.

Howdy Denburt,

I'm assuming that Forms!MaintForm1.Tag would contain the form name of the calling form (e.g. FormA) and not the maint form (MaintForm1) ? If so, shouldn't it be before DoCmd since DoCmd is being executed from the calling form FormA ?

Then in the maint form's Form_Close, Form_LostFocus events when I call the IsLoaded routine, the form name parameter being passed would be Forms!MaintForm1.Tag ?

BTW, what's the difference between ! and . ? I've seen both used in combination with Form (e.g. Form!, Form.) and Me (e.g. Me!, Me.). I have noticed when using . , the context menu pops up in the Visual Basic editor when referencing objects, methods, etc. in the code......

Thanks, Ed.

[NeoPa]

BTW, what's the difference between ! and . ? I've seen both used in combination with Form (e.g. Form!, Form.) and Me (e.g. Me!, Me.). I have noticed when using . , the context menu pops up in the Visual Basic editor when referencing objects, methods, etc. in the code......

This is a thorny issue that's been discussed before.
I believe Denburt has also created a thread (Article) related to this but here is a link I found which discusses it from various angles.
Bang vs. Dot - final answer & '!' vs. '.'

[NeoPa]

Managed to find Denburt's thread too.
Control Object Reference (Me!) & Referencing Controls on a Form

[Denburt]

Thanks for posting the links Neo.

I'm assuming that Forms!MaintForm1.Tag would contain the form name of the calling form (e.g. FormA) and not the maint form (MaintForm1) ? If so, shouldn't it be before DoCmd since DoCmd is being executed from the calling form FormA ?

Then in the maint form's Form_Close, Form_LostFocus events when I call the IsLoaded routine, the form name parameter being passed would be Forms!MaintForm1.Tag ?

You have to open Maintform before you can set the tag property, once the Maintform is open (from formA) then me.name would return FormA since it is in the code for formA.

Then in the MaintForm you can type out the whole thing (Forms!MaintForm1.Tag) however me.tag should be fine.

'For Both Form_Close, Form_LostFocus events
Debug.Print IsLoaded(Me.Tag)
 

Happy coding...

[bitsnbytes64]

Thanks for posting the links Neo.



You have to open Maintform before you can set the tag property, once the Maintform is open (from formA) then me.name would return FormA since it is in the code for formA.

Then in the MaintForm you can type out the whole thing (Forms!MaintForm1.Tag) however me.tag should be fine.

'For Both Form_Close, Form_LostFocus events
Debug.Print IsLoaded(Me.Tag)
 

Happy coding...

Thanks for the help Denburt & NeoPa ! I'll let you know how I make out !

Ed.

[NeoPa]

Thanks for the help Denburt & NeoPa ! I'll let you know how I make out !

Ed.

Hey, I'm a father of two. I don't need to know how you make out - Been there, Done that :D
(Sorry for that poor joke in even poorer taste - I just couldn't resist it.)

Seriously, we'd be glad to hear your results Bits.

[Denburt]

Hey, I'm a father of two. I don't need to know how you make out - Been there, Done that :D
(Sorry for that poor joke in even poorer taste - I just couldn't resist it.)

Seriously, we'd be glad to hear your results Bits.

As a father of two I would have to agree.

I was curious though is it a bit of a byte or is it a byte of the bit? j/k More dry humor.

[bitsnbytes64]

As a father of two I would have to agree.

I was curious though is it a bit of a byte or is it a byte of the bit? j/k More dry humor.

Maybe even a "nibble" ? LOL

BTW - When I left MaintForm1 open and either Alt-Tab'd or directly clicked on the calling form (FormA), the MaintForm1's Lost_Focus event never fired. Additionally, FormA's GotFocus event never fired either. Any ideas ?

Thanks, Ed.

[Denburt]

There are a lot of MS Access features I don't use much, however I just tested it with activate deactivate and it seems to work alright. I have also had mixed results with using the Activate/Gotfocus events.

[bitsnbytes64]

There are a lot of MS Access features I don't use much, however I just tested it with activate deactivate and it seems to work alright. I have also had mixed results with using the Activate/Gotfocus events.

Hey Denburt,

In MaintForm1's close event, what should the code be in order to use the Tag vs. the actual form name ?

e.g. If FormIsLoaded(Me.Tag) Then Forms!Tag.Requery vs. If FormIsLoaded(Me.Tag) Then Forms!FormA.Requery

If I can't use Tag, then I have to add a line of for every form that calls MaintForm1....

Thanks, Ed.

[Denburt]

Forms(Me.tag).Requery  

Although you seem to be using it a fair amount so I would use either a local or a global variable to hold the form name then call that variable.

strFrm = Me.tag
Forms(strFrm).Requery

[bitsnbytes64]

Forms(Me.tag).Requery  

Although you seem to be using it a fair amount so I would use either a local or a global variable to hold the form name then call that variable.

strFrm = Me.tag
Forms(strFrm).Requery

Howdy,

Should I use an array in case more than one form is open ? I'd hope that the user would only have one form open but if that turns out not to be the case, I'd like to allow for it ? Or should I not consider it at all ?

Thanks, Ed.

BTW, I'm also a father of two and everyday is a new experience !

[Denburt]

That sounds like an idea it just depends on how things are set up in your db. I usually make sure that when i open 1 form I hide or close the calling one then I don't need to worry about it that much. I find it easier to manage that way.

[bitsnbytes64]

That sounds like an idea it just depends on how things are set up in your db. I usually make sure that when i open 1 form I hide or close the calling one then I don't need to worry about it that much. I find it easier to manage that way.

I just found out this only works when FormA calls MaintForm1. If they are opened seperately (via a menu for example) then add / update MaintForm1's values (which is possible as this is a code / decode form that can be accessed stand-alone as well as via another form), the combobox on FormA does not get updated..... This is turning out to be more work than it's worth and I'm sure this is mainly due to inexperience, but to make it easier, I'm going to add a refresh button on the calling form.....

[Denburt]

If they are opened seperately (via a menu for example)

It won't happen if you hide the menu after they click to open a form. Take a look at the article below Neo has written an article that might help out with form management:

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

[NeoPa]

It won't happen if you hide the menu after they click to open a form. Take a look at the article below Neo has written an article that might help out with form management:

Cascading Forms.

Feel free to ask any questions on this.
It does involve a certain amount of rethinking of how to run forms. It's a bit like a more hierarchical / Object Oriented (OO) approach.

[bitsnbytes64]

Feel free to ask any questions on this.
It does involve a certain amount of rethinking of how to run forms. It's a bit like a more hierarchical / Object Oriented (OO) approach.

Thanks for the code ! I like the suggestion about hiding the menu, I didn't think of that.... As far as Hierarchical / Object Oriented, I understand hierarchies very well, but I'm new to the OO world. I have some of the basic concepts but.....

In any case, thanks for the help. Hopefully I'll be able implement this and if I do, I'll post the code, sequence of events, etc.

Ed.

[Denburt]

Cool, hope everything goes well let us know.

[NeoPa]

Yeah, especially if you need any help understanding or implementing.