473,327 Members | 2,074 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Requery of a combo box not working

418 256MB
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
20 3121
NeoPa
32,556 Expert Mod 16PB
Is frmEcho a sub-form?
Jun 25 '09 #2
MNNovice
418 256MB
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
32,556 Expert Mod 16PB
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
MNNovice
418 256MB
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
32,556 Expert Mod 16PB
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
MNNovice
418 256MB
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
32,556 Expert Mod 16PB
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
MNNovice
418 256MB
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
32,556 Expert Mod 16PB
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
MNNovice
418 256MB
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
32,556 Expert Mod 16PB
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
MNNovice
418 256MB
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
32,556 Expert Mod 16PB
@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
MNNovice
418 256MB
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
904 Expert 512MB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
@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
MNNovice
418 256MB
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
MNNovice
418 256MB
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
904 Expert 512MB
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

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

Similar topics

5
by: Scott | last post by:
I have created a form that has a few combo boxes on it. On the form I have ComboBoxA which selects data from a region, and ComboBoxB that selects individual locations within a selected region. I...
0
by: Michelle | last post by:
Hi all I have a main form which has a combo box and a subform. The two forms are linked by ShiftPatternName which is basically the value in the combo. the combo has the following code ...
2
by: Uwe Range | last post by:
Hi to all, I am working on a form with a combi-field on a subform. I want to give the user the opportunity to alter the list-content (which is based on a table) in a separate form. The user...
2
by: jay | last post by:
Hi. I have created an unbound combo box in a form whose purpose is to pass parameters to a query and then requery the requery. The unbound combo box is named "ClosedFilter" The two parameters...
1
by: Bill | last post by:
Problem: Combo box data disappears from view when a requery is done See "Background" below for details on tables, forms & controls On a form, I want to use the setting of bound combo box C1...
14
by: Kurt | last post by:
I have an unbound main form with an unbound subform. frmProjects fsubProjectList Using combo boxes, the user can select several search criteria on frmProjects and then click a command button....
11
blyxx86
by: blyxx86 | last post by:
I can't seem to get this figured out. I am trying to requery another form after adding new information to a table that a cboList is created from. So, I have a combo box that has Sales personnel,...
6
by: MarkoBBC | last post by:
Hi everyone, First a brief description of my form: I have a subform within a main form. In my subform, I have a listbox displaying address information by firm name. A user first has to select a...
12
waynetheengineer
by: waynetheengineer | last post by:
Hi, I have a combo box on a form with a list of cities in it based on a single field city table. I also have a command button on this form that opens up another form that allows the user to enter...
10
by: themightypea | last post by:
Hi, I'm attempting to create a continuos form which allow the user to edit various fields for each item. Unfortunately, the data I need is spread over multiple tables so binding the control to the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.