Requery of a combo box not working

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.

re: Requery of a combo box not working

Is frmEcho a sub-form?

re: Requery of a combo box not working

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.

re: Requery of a combo box not working

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

re: Requery of a combo box not working

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.

re: Requery of a combo box not working

The following code should do the trick for you :
Are you comfortable finding where to add it in your module?

re: Requery of a combo box not working

NeoPa:

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

I bet, I can add your code somewhere here before the Exit command. Am I correct?

re: Requery of a combo box not working

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?

re: Requery of a combo box not working

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:

What do you say? Am I thinking correctly?

re: Requery of a combo box not working

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.

re: Requery of a combo box not working

NeoPa:

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


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


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.

re: Requery of a combo box not working

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.

re: Requery of a combo box not working

NeoPa:

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

My understanding was that I should close one of the form. I am quoting:

So I made the change on line 12 posting 9 and asked you if I were on the right track. Your response was:

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.

re: Requery of a combo box not working

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.

re: Requery of a combo box not working

NeoPa:

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

Thanks.

re: Requery of a combo box not working

Mind if I give my 2 cents?

In the AfterUpdate Event of frmEcho put this

cheers,

re: Requery of a combo box not working

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.

re: Requery of a combo box not working

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.

re: Requery of a combo box not working

NeoPa:

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

I clicked on cmdEnterEcho to open frmEchoEnter and I got this message:
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.

re: Requery of a combo box not working

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

re: Requery of a combo box not working

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,