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.

[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!MaintFor m1.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.Reque ry vs. If FormIsLoaded(Me .Tag) Then Forms!FormA.Req uery

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