(Yet another) Subform/Combo box issue ..

[raor]

An Access newbie, and I have searched the forum and found hundreds of related posts but none of those helped.

Here's the issue -
1 . I have a form with a text box and database navigation button to pick the "Frequency" from tblFrequency.
2. The subform(datasheet view) displays the records from another table related to the "Frequency" shown in the textbox.

The table relationships are set up and the above set up works perfectly fine. Just that I would like a Combo Box to replace the text box and database navigation button in the main form.

I can create a combo box that successfully retrieves frequencies from tblFrequency and I enter the field names in the Link child/master fields. But then the selection in the combo box has absolutely no affect on the records in the subform.

I am using Access 2000. Any ideas?

Thanks in advance,
rr

 

[boblarson]

If the criteria of the query underlying the subform was referencing the text box, you should change the criteria to refer to the combo box instead and then in the after update event of the combo box put:

Forms!YourMainFormNameHere.Form.YourSubFormContainerNameHere.Requery

 

[raor]

I really do appreciate your help. (I wish I could see the scale like icon to click and add to your reputation !! )

What I did -
1. added the code you provided (yes I did have the subform reference the combo box instead of the text box).
2. deleted the original text box
3. hid the Navigation buttons from showing up on the form.

Your solution did work but 2 other problems have risen -

1. The combo box neither "selects" nor collapses the drop down after I pick a value unless I hit "Enter" on the keyboard (this never happened to me before). But it definetely changes what is displayed in the subform after I hit "Enter".
2. Even with the above limitation, it only scrolls thro the list sequentially. Meaning I cannot pick items in the combo box list at random. It resets back to the next record and doesn't cycle back to the first one after the last record.

Any help appreciated.

Thanks,
R

 

[boblarson]

Is there any way you can post a stripped down version of your db here so I can take a look?

 

[raor]

Thanks Bob.
Here's the stripped down mdb.

I appreciate the help.

r

 

[boblarson]

Okay, got it sorted (see - I'm way more visual and when I can play around with it I can do it a whole lot faster).

First -

1. You have your main form set to NO EDITS (which means that the combo box can not be changed - that's why it hangs there).

2. You have the combo box bound to a field, when it shouldn't be.

3. The main form doesn't need to have a recordsource set as the main form isn't storing any data but using the combo box to select the data.

4. Get rid of the Master / Child links

5. Set the AfterUpdate event of the Combo box to
Forms!tbleFrequency.Form.[tblqueryList SubForm].Requery
because you didn't have it named properly (that's why you had the error).

Check out the attached revisions.

 

[raor]

You have completely solved my issue here...
You have been most helpful, Bob !!
Learned my mistakes.

Thank you very much !
-r

 

[boblarson]

Great to hear! Believe me, there is a lot to learn with Access and I keep learning new stuff all the time (especially hanging out here in these forums).