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

Requery of a combo box not working

100+
P: 418
I am new at understanding SQL - so please be patient with me.

On frmEchoEnter the user is supposed to select a number from cboEchoSelect before the sub forms are enabled to receive data entry.

frmEcho is where all the Echo number is listed. In the event there are no new number is available to select, frmEcho needs to be updated before frmEchoEnter can be worked on.

I have these SQL for the cboEchoSelect I wanted the new number to be listed once I update frmEcho. But the requery does not work. I must close frmEchoEnter and open it to see the new number that is added.

I probably need some modification to the On Double_Click or On After_Update

Can someone please help? Thanks.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboEchoSelect_AfterUpdate()
  2.  
  3.   Me.Filter = "[ECHOID]=" & Me![cboEchoSelect]
  4.   Me.FilterOn = True
  5.   EnableControls Me, acDetail, True
  6.   sfrmAP.Enabled = True
  7.   sfrmPayroll.Enabled = True
  8.  
  9. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboEchoSelect_DblClick(Cancel As Integer)
  2. On Error GoTo Err_cboEchoSelect_DblClick
  3.  
  4.     If IsNull(Me![cboEchoSelect]) Then
  5.         Me![cboEchoSelect].Text = ""
  6.     Else
  7.         Me![cboEchoSelect] = Null
  8.     End If
  9.     DoCmd.OpenForm "frmECHO", , , , , acDialog, "GotoNew"
  10.     Me![cboEchoSelect].Requery
  11.  
  12. Exit_cboEchoSelect_DblClick:
  13.     Exit Sub
  14.  
  15. Err_cboEchoSelect_DblClick:
  16.     MsgBox Err.Description
  17.     Resume Exit_cboEchoSelect_DblClick
  18. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboEchoSelect_NotInList(NewData As String, Response As Integer)
  2.     MsgBox "Double-click this field to add a New ECHO number to the list."
  3.     Response = acDataErrContinue
  4. End Sub
Jun 24 '09 #1
Share this Question
Share on Google+
20 Replies


NeoPa
Expert Mod 15k+
P: 31,707
Is frmEcho a sub-form?
Jun 25 '09 #2

100+
P: 418
NeoPa:

No. frmEcho is based on tblEcho which has the following fields

ECHOID
EchoNo
DtSubmitted
DtReceived

frmEchoEnter is the main form which has 3 sub forms: sfrmApDetails, sfrmPayDetails, sfrmAllocDetails.

cboEchoSelect is a combo box on the main form frmEchoEnter.

Hope this answers your questions. Thanks for your time.
Jun 25 '09 #3

NeoPa
Expert Mod 15k+
P: 31,707
Forget that. After reading through all the code I can see the design you must be using, as well as your problem. This is entirely unrelated to any SQL though.

Your code calls the .Requery immediately after opening the form frmEcho (Line #10 of cboEchoSelect_DblClick). This executes immediately. Not after the operator has made any selection from, or done any work in, frmEcho.

To ensure the .Requery works as you intend, you should call it from the frmEcho code module (either when an item has been added or when the form is closed).
Jun 25 '09 #4

100+
P: 418
NeoPa:

So how do I code After_Update on frmEcho to update cboEchoSelect on frmEchoEnter ?

Can you be a little bit more elaborate on your instructions? I am willing to do it on my own but I need help.

Thanks.
Jun 25 '09 #5

NeoPa
Expert Mod 15k+
P: 31,707
The following code should do the trick for you :
Expand|Select|Wrap|Line Numbers
  1. Call Forms("frmEchoEnter").cboEchoSelect.Requery
Are you comfortable finding where to add it in your module?
Jun 25 '09 #6

100+
P: 418
NeoPa:

No I am not comfortable but I can take a guess. There is a macro on frmEcho which has these codes:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEnterEcho_Click()
  2. On Error GoTo Err_cmdEnterEcho_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frmEchoEnter"
  8.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  9.  
  10. Exit_cmdEnterEcho_Click:
  11.     Exit Sub
  12.  
  13. Err_cmdEnterEcho_Click:
  14.     MsgBox Err.Description
  15.     Resume Exit_cmdEnterEcho_Click
  16.  
  17. End Sub
I bet, I can add your code somewhere here before the Exit command. Am I correct?
Jun 25 '09 #7

NeoPa
Expert Mod 15k+
P: 31,707
You can add it after line #8.

This opens up another issue though. If I read this aright you open frmEcho from frmEchoEnter, and then proceed to open frmEchoEnter again from frmEcho. Is that right?

This will probably work ok for you in Access, but don't you find this makes understanding the flow of the process a bit more complicated? Do you never simply close a called form?
Jun 25 '09 #8

100+
P: 418
NeoPa, Before I opened your instructions, I attempted adding it after line #8, it didn't work. Rather my form froze and I was kicked out.

Anyway, after reading your latest comments I have another idea. See if it makes sense. First let me explain the activities here.

1. Open fromEchoEnter: select an Echo number and the sub forms are enabled.

2. In the event a new Echo number is needed, double_click cboEchoEnter. This opens frmEcho

3. Once you add a new number go back to frmEchoEnter and continue...

After reading your comments I see what you are suggesting. If I were to add a command that will close frmEchoEnter once cboEchoSelect is double_clicked that will probably simplify this issue. Can I then add a command to exit frmEchoEnter somewhere here:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboEchoSelect_DblClick(Cancel As Integer)
  2. On Error GoTo Err_cboEchoSelect_DblClick
  3.  
  4.     If IsNull(Me![cboEchoSelect]) Then
  5.         Me![cboEchoSelect].Text = ""
  6.     Else
  7.                 Me![cboEchoSelect] = Null
  8.     End If
  9.     DoCmd.OpenForm "frmECHO", , , , , acDialog, "GotoNew"
  10.     Me![cboEchoSelect].Requery
  11.  
  12. Exit_cboEchoSelect_DblClick:
  13.     Exit Sub
  14.  
  15. Err_cboEchoSelect_DblClick:
  16.     MsgBox Err.Description
  17.     Resume Exit_cboEchoSelect_DblClick
  18. End Sub
What do you say? Am I thinking correctly?
Jun 25 '09 #9

NeoPa
Expert Mod 15k+
P: 31,707
What I do, and it's worth considering generally, is to hide the calling form when any other is opened. When the newly opened form is closed it must re-show the calling form.

This way only one form is ever the current one and the code always knows where the operator is at.

In this case it is sensible to add that line to the closing code of the called form (frmEcho).

I use classes for handling this process of hiding forms etc, but you can do it more simply if you ensure each opening of a form passes the calling form as OpenArgs. I'm happy to share my class code with you if you'd like.
Jun 25 '09 #10

100+
P: 418
NeoPa:

I added this after Line # 7 and before Line 8 (Posting # 9);

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close

and it worked, well sort of. When I got back to frmEchoEnter, I got this message:

"The expression you entered reers to an opject that is closed or doesn't exist".

However, the combo box lists the new number, once I click on the OK button of the error message.

So I added the close command to the macro button on frmEcho


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEnterEcho_Click()
  2. On Error GoTo Err_cmdEnterEcho_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frmEchoEnter"
  8.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  9.  
  10. Exit_cmdEnterEcho_Click:
  11.     Exit Sub
  12. DoCmd.Close
  13. Err_cmdEnterEcho_Click:
  14.     MsgBox Err.Description
  15.     Resume Exit_cmdEnterEcho_Click
  16.  
  17. End Sub
But frmEcho remains open...(LOL)

I can enter data to frmEchoEnter with the new number but seems like I should do better. I mean it's messy out there.

So, please help me clean these codes. Thanks.
Jun 25 '09 #11

NeoPa
Expert Mod 15k+
P: 31,707
I'm trying to help M, but it's difficult as every time I respond to one point you move immediately on to another and approach from a different direction. It makes it very difficult to keep the conversation making sense so that we both understand what each other is saying.

I will look at this again later and try to clarify things and tidy up the current situation as much as possible.

PS. This is due to lack of time currently. I'm just off home from work.
Jun 25 '09 #12

100+
P: 418
NeoPa:

I believe I was following your instruction to the letter and was working on the same issue.

This will probably work ok for you in Access, but don't you find this makes understanding the flow of the process a bit more complicated? Do you never simply close a called form?
My understanding was that I should close one of the form. I am quoting:

After reading your comments I see what you are suggesting. If I were to add a command that will close frmEchoEnter once cboEchoSelect is double_clicked that will probably simplify this issue. Can I then add a command to exit frmEchoEnter somewhere here:
So I made the change on line 12 posting 9 and asked you if I were on the right track. Your response was:

I use classes for handling this process of hiding forms etc, but you can do it more simply if you ensure each opening of a form passes the calling form as OpenArgs. I'm happy to share my class code with you if you'd like.
Since I didn't know how to write OpenArgs and didn't want to take up your time I TRIED to do what I know and see if I succeed. What I did is posted on #11. It's working - I mean when cboEchoSelect is double_clicked, frmEchoEnter is closed and frmEcho is opened. I add a new number and click the macro that re-opens frmEchoEnter. BUT frmEcho still remains open and I wanted it to be closed.

That's why I said, it was messy and if you could help me clean it. Well, I guess to you, I was once again, not on the correct track.

My aplolgies. Thanks for you help and time.
Jun 25 '09 #13

NeoPa
Expert Mod 15k+
P: 31,707
@NeoPa
Can you explain again what happens when this is done. Remembering we're referring to line #8 of the code you posted in post #7 of course.
Jun 26 '09 #14

100+
P: 418
NeoPa:

Here is what happened when I added what you said I should do in posting #6 to after Line #8.

NeoPa, Before I opened your instructions, I attempted adding it after line #8, it didn't work. Rather my form froze and I was kicked out.
Thanks.
Jun 26 '09 #15

mshmyob
Expert 100+
P: 904
Mind if I give my 2 cents?

In the AfterUpdate Event of frmEcho put this

Expand|Select|Wrap|Line Numbers
  1. ' check if frmEchoEnterform is open - if so refresh the combo box
  2. If CurrentProject.AllForms("frmEchoEnter").IsLoaded Then
  3.     Forms!frmEchoEnter.cboEchoSelect.Requery
  4. Else
  5.     'do nothing
  6. End If
  7.  
cheers,
Jun 26 '09 #16

NeoPa
Expert Mod 15k+
P: 31,707
That's probably not going to hurt ever, but I think (it's always hard to be absolutely sure remotely) that this code could never be reached unless the form were already open.

As I say, I'm pretty confident this doesn't go to the current problem, but as a general concept it is favoured by some, and can't do any harm.
Jun 28 '09 #17

NeoPa
Expert Mod 15k+
P: 31,707
@NeoPa
@MNNovice
M,

Can you confirm this was added after line #8 to the code found in your post #7?

I know this has already been said, but with other posts around with code in them, it should be stated explicitly. There is so much potential for mis-communication, which we cannot afford that at this stage.

An alternative would be to post the code you tried fully, including the surrounding code. That way I can see whether yu are trying what I was suggesting or something a little different.
Jun 28 '09 #18

100+
P: 418
NeoPa:

This is the entire code after I added what you posted in #6 .

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEnterEcho_Click()
  2. On Error GoTo Err_cmdEnterEcho_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frmEchoEnter"
  8.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  9.     Call Forms("frmEchoEnter").cboEchoSelect.Requery
  10.  
  11.  
  12. Exit_cmdEnterEcho_Click:
  13.     Exit Sub
  14. DoCmd.Close
  15. Err_cmdEnterEcho_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_cmdEnterEcho_Click
  18.  
  19. End Sub
I clicked on cmdEnterEcho to open frmEchoEnter and I got this message:
Expand|Select|Wrap|Line Numbers
  1. The expression you entered reers to an oject that is closed or doesn't exist
Once I press OK button of this message I am able to work with frmEchoEnter but the new number I added to frmEcho does not get listed on cboEchoSelect.

Hope I answered your question. Thanks & regards.
Jun 29 '09 #19

100+
P: 418
mshmyob:

Thanks for your help. I changed the After_Update for frmEcho as you suggested. But when I clicked on the cmdEchoEnter, I also got the same message:
Expand|Select|Wrap|Line Numbers
  1. The expression you entered reers to an oject that is closed or doesn't exist
BUT the cboEchoSelect got updated with the new listing. In other words whatever numbers I added to frmEcho, it got listed in cboEchoSelect on frmEchoEnter.

Thanks again.
Jun 29 '09 #20

mshmyob
Expert 100+
P: 904
What form is 'cmdEchoEnter' on? If it is on frmEcho then you do not need your code (don't even need the button). Mine will do it for you or you can put my code behind your button click instead of yours.

I am lost in the threads to figure it out.

cheers,


@MNNovice
Jun 29 '09 #21

Post your reply

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