By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Refresh ComboBox After Adding New Values via a Separate Form

P: 31
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddCatgCd_Click()
  2. On Error GoTo Err_cmdAddCatgCd_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "subfrmHierCatgCd"
  8.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  9.  
  10. Exit_cmdAddCatgCd_Click:
  11.     Exit Sub
  12.  
  13. Err_cmdAddCatgCd_Click:
  14.     MsgBox Err.Description
  15.     Resume Exit_cmdAddCatgCd_Click
  16. End Sub
  17.  
  18. Private Sub Form_AfterUpdate()
  19.     Me.Combo21.Requery
  20. End Sub
  21.  
  22. Private Sub Form_Current()
  23.     Me.Combo21.Requery
  24. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command25_Click()
  2. On Error GoTo Err_Command25_Click
  3.  
  4.     Me.Combo21.Requery
  5.  
  6. Exit_Command25_Click:
  7.     Exit Sub
  8.  
  9. Err_Command25_Click:
  10.     MsgBox Err.Description
  11.     Resume Exit_Command25_Click
  12.  
  13. End Sub
I'd prefer having the requery happen automatically for both forms and subforms.....

Thanks, Ed.
May 7 '07 #1
Share this Question
Share on Google+
34 Replies


Rabbit
Expert Mod 10K+
P: 12,349
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?
May 7 '07 #2

P: 31
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddCatgCd_Click()
  2. On Error GoTo Err_cmdAddCatgCd_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "subfrmHierCatgCd"
  8.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  9.  
  10. Exit_cmdAddCatgCd_Click:
  11.     Exit Sub
  12.  
  13. Err_cmdAddCatgCd_Click:
  14.     MsgBox Err.Description
  15.     Resume Exit_cmdAddCatgCd_Click
  16. End Sub
  17.  
Any ideas ?

Thanks, Ed.
May 7 '07 #3

Rabbit
Expert Mod 10K+
P: 12,349
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.
May 7 '07 #4

Denburt
Expert 100+
P: 1,356
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.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand (acCmdSelectRecord)
  2.     DoCmd.RunCommand (acCmdSaveRecord)
  3. 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
May 7 '07 #5

P: 31
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.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand (acCmdSelectRecord) DoCmd.RunCommand (acCmdSaveRecord) Forms!FirstForm!ComboBox1.requery
  2.  

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 ?

Expand|Select|Wrap|Line Numbers
  1. 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
  2.  

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

Thanks for the help !

Ed.
May 8 '07 #6

Denburt
Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1. Function IsLoaded(strFrmName As String) As Boolean
  2.     Const conFormDesign = 0
  3.     Dim intX As Integer
  4.     IsLoaded = False
  5.     For intX = 0 To Forms.Count - 1
  6.         If Forms(intX).FormName = strFrmName Then
  7.             If Forms(intX).CurrentView <> conFormDesign Then
  8.                 IsLoaded = True
  9.                 Exit Function
  10.             End If
  11.         End If
  12.     Next
  13. End Function
Then simply call the function:
Expand|Select|Wrap|Line Numbers
  1. if isloaded("YourFormName") then Forms!YourFormName.requery
May 8 '07 #7

P: 31
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.

Expand|Select|Wrap|Line Numbers
  1. Function IsLoaded(strFrmName As String) As Boolean
  2.     Const conFormDesign = 0
  3.     Dim intX As Integer
  4.     IsLoaded = False
  5.     For intX = 0 To Forms.Count - 1
  6.         If Forms(intX).FormName = strFrmName Then
  7.             If Forms(intX).CurrentView <> conFormDesign Then
  8.                 IsLoaded = True
  9.                 Exit Function
  10.             End If
  11.         End If
  12.     Next
  13. End Function
Then simply call the function:
Expand|Select|Wrap|Line Numbers
  1. 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.
May 8 '07 #8

P: 31
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 ?

Expand|Select|Wrap|Line Numbers
  1. Public Function FormIsOpen(ByVal strFormName As String) As Boolean
  2.  
  3. Const conDesignView = 0
  4. Const conObjStateClosed = 0
  5.  
  6. FormIsOpen = False
  7. If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
  8.     If Forms(strFormName).CurrentView <> conDesignView Then
  9.         FormIsOpen = True
  10.     End If
  11. End If
  12.  
  13. End Function
  14.  
Thanks, Ed.
May 8 '07 #9

Denburt
Expert 100+
P: 1,356
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.
May 8 '07 #10

P: 31
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.
May 8 '07 #11

P: 31
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.
May 8 '07 #12

Denburt
Expert 100+
P: 1,356
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.

:)
May 8 '07 #13

NeoPa
Expert Mod 15k+
P: 31,347
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.

Expand|Select|Wrap|Line Numbers
  1. Function IsLoaded(strFrmName As String) As Boolean
  2.     Const conFormDesign = 0
  3.     Dim intX As Integer
  4.     IsLoaded = False
  5.     For intX = 0 To Forms.Count - 1
  6.         If Forms(intX).FormName = strFrmName Then
  7.             If Forms(intX).CurrentView <> conFormDesign Then
  8.                 IsLoaded = True
  9.                 Exit Function
  10.             End If
  11.         End If
  12.     Next
  13. End Function
Then simply call the function:
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. Function IsLoaded(strFrmName As String) As Boolean
  2.     Dim strName as String
  3.  
  4.     On Error Resume Next
  5.     strName = CurrentDB.Forms(strFrmName)
  6.     IsLoaded = (strName = strFrmName)
  7. End Function
Your Forms() is the same, as it also refers to CurrentDB.Forms.
May 8 '07 #14

Denburt
Expert 100+
P: 1,356
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.
Expand|Select|Wrap|Line Numbers
  1. Function IsLoaded(strFrmName As String) As Boolean
  2.     Dim strName as String
  3.  
  4.     On Error Resume Next
  5.     strName = CurrentDB.Forms(strFrmName)
  6.     IsLoaded = (strName = strFrmName)
  7. 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. :)
May 9 '07 #15

Denburt
Expert 100+
P: 1,356
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:
Expand|Select|Wrap|Line Numbers
  1. Docmd.open forms!MaintForm1
  2. Forms!MaintForm1.tag = me.name
  3.  
Then you know which form called maintForm1.


BTW Sorry I didn't see your question yesterday i don't know how I missed it.
May 9 '07 #16

P: 31
Use the on click event and do something like:
Expand|Select|Wrap|Line Numbers
  1. Docmd.open forms!MaintForm1
  2. Forms!MaintForm1.tag = me.name
  3.  
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.
May 9 '07 #17

NeoPa
Expert Mod 15k+
P: 31,347
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. '.'
May 9 '07 #18

NeoPa
Expert Mod 15k+
P: 31,347
Managed to find Denburt's thread too.
Control Object Reference (Me!) & Referencing Controls on a Form
May 9 '07 #19

Denburt
Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1. 'For Both Form_Close, Form_LostFocus events
  2. Debug.Print IsLoaded(Me.Tag)
  3.  
Happy coding...
May 9 '07 #20

P: 31
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.

Expand|Select|Wrap|Line Numbers
  1. 'For Both Form_Close, Form_LostFocus events
  2. Debug.Print IsLoaded(Me.Tag)
  3.  
Happy coding...
Thanks for the help Denburt & NeoPa ! I'll let you know how I make out !

Ed.
May 10 '07 #21

NeoPa
Expert Mod 15k+
P: 31,347
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.
May 10 '07 #22

Denburt
Expert 100+
P: 1,356
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.
May 10 '07 #23

P: 31
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.
May 11 '07 #24

Denburt
Expert 100+
P: 1,356
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.
May 11 '07 #25

P: 31
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.
May 11 '07 #26

Denburt
Expert 100+
P: 1,356
Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. strFrm = Me.tag
  2. Forms(strFrm).Requery
May 11 '07 #27

P: 31
Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. strFrm = Me.tag
  2. 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 !
May 11 '07 #28

Denburt
Expert 100+
P: 1,356
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.
May 11 '07 #29

P: 31
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.....
May 11 '07 #30

Denburt
Expert 100+
P: 1,356
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
May 14 '07 #31

NeoPa
Expert Mod 15k+
P: 31,347
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.
May 14 '07 #32

P: 31
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.
May 15 '07 #33

Denburt
Expert 100+
P: 1,356
Cool, hope everything goes well let us know.
May 15 '07 #34

NeoPa
Expert Mod 15k+
P: 31,347
Yeah, especially if you need any help understanding or implementing.
May 15 '07 #35

Post your reply

Sign in to post your reply or Sign up for a free account.