Filtering Combo-Boxes -Problem 3

[LakotaMan]

As clearly as I can explain it, here’s the problem:

Version: Access 2002

I have a form based on a query. I have several, unbound combo-boxes used to navigate to certain records in the query. Each builds it’s drop-down list from the choice of the one before, using a QBE on the Row Source property of the cbo.

For each query used to build the drop-down, I hide the numerical key field and display the text value. Using the hidden key fields selected as parameters for the combos, I am able to filter down to a subset of records without problems.

When I get to the last one, and then click a Close button I have put on the form, I get the following message:

“Enter Parameter Value” and then the name of the 2nd to last cbo of the sequence.

In an attempt to solve this, I have stripped out all code (except for the docmd.Close on the button) and isolated the problem to the actual controls. The message does not occur if I use the X button to close the form.

I recognize (I think) that the query behind the last combo box is looking for the value of the previous combo box, but am unable to figure out why it doesn’t “see” the choice made there. Each query produces the desired results in their drop-downs. I also recognize there are probably things that happen, code-wise, behind the X-button that I am not providing for with my simple: doCmd.Close, “Name of Form”

I apologize in advance for the “newbiness” of this questions. I am so brain-dead from trying to figure this out that if you told me I’d spelled my own name wrong, I’d be grateful.

I hope I have sufficiently explained, and I will be glad to elucidate if I have been unclear.

Much thanks to all in advance.

Tru

 

[TheAceMan1]

How are ya trucap . . . .

Every time you change the Row Source for a combobox (espcially if its based on a query), you have to ReQuery the combobox to see the new results. Such as:

Me!YourComboBoxName.Requery

How are you updating the Row Source?

TheAceMan

 

[LakotaMan]

AceMan,

You're so right, I forgot to mention that I am doing a requery.

Here's what I've put behind the change event of the first cbo (I'm using generic names here):

me.cbo2ndbox.requery
me.cbo3rdbox.requery
me.cbo4thbox.requery

Then behind the same event of the 2nd cbo:

me.cbo3rdbox.requery
me.cbo4thbox.requery

When I get to the last one, I don't requery anything. . . could that be it? Seems like I might have tried it and botched it up somehow.

Anyway. . . these are all, as I said, behind the change events. I have also got a requery of all of them on the Form's Current event, but I commented that out when I started having problems and have left it inactive.

Thanks again. . .

 

[TheAceMan1]

trucap . . . . Hmmmmmmmm . . . .

You said:I am able to filter down to a subset of records without problems.

Then you said:When I get to the last one, and then click a Close button . . . . is when your prompted for the parameter. I assume you mean; after you make a selection from the last combobox and your main form goes through a final query of records with no problem, thereafter closing via DoCmd[/blue] raises the parameter prompt? Is this correct?

As you have explained it, I see no reason for it not to work!

By any chance do you have any code in the On UnLoad or On Close events?



TheAceMan

 

[LakotaMan]

AceMan,

Your rundown of my user sequence is correct. I have nothing coded into the Form's Unload or Close events.

I am very glad you are still there. Am losing sleep over this.

Tru

 

[BSman]

Make sure your 2nd to last combo box is bound to a column in the source table/query for the form.

 

[TheAceMan1]

OK trucap . . . .

First, the Change Event is certainly not the best event for your operations here.

Move the combobox routines to their After Update events and let me know if results are the same!



TheAceMan

 

[LakotaMan]

AceMan,

I have some news. I tried your suggestion, but same results. While I was trying to write a fuller explanation of the problem I got some info from a coworker that what we are seeing is actually a BUG in Access 2002.

The work around, as listed in a Microsoft KB article is to turn echo off right before your doCmd.Close and then turn it back on again after. . .

DoCmd.Echo False
DoCmd.Close
DoCmd.Echo True

I just tried this, and it worked.

Here's the link to the MS KB explaining in more detail:

http://support.microsoft.com/?id=811860

I don't pretend to understand the logic of it, but am glad to have a fix.

Thank you SO MUCH for all your help, I'm giving you a star in gratitude.

BSman, I also tried your solution, still had the message appear, but I appreciate your input.

Once again, thanks to all!
Tru

 

[TheAceMan1]

ok trucap . . . Good to go . . .

As it was, my insight was that of Inproper Operation supported by the closing of your form.

Happy to hear your back on track. This is also one to log for the books . . . .

You take care . . . . Ya Here! ;-)

TheAceMan

 

[spongie1]

I am giving you a star trucap good job.