Combo box requery

[sherylnance]

Hi All,

I would like to have a query behind a combo box executed everytime the combo box is "activated". basically everytime the down arrow is pushed to show the values i would like the query that produces those values executed. Is there a way to do this?

Thanks!
Sheryl Nance


More explanation:

I have a form with information about a child's school, ie name, and zone.
There is a subform that has combo boxes for the user to select school choices for next year. these choices need to be based on the zone information on the main form. (this part works on the very first student)

When the second student is called up and is ready to have choices made the combo boxes are still populated with the previous zone schools. They need to rerun the query that is in the row source property of the combo box every time a student is selected.

I have tried the me.choice1.requery code in every imaginable combination of things I can think of with no luck.

I hope this helps to better explain what I am trying to accomplish.

Thanks again for any assistance and insight....

 

[boblarson]

Put

Me.YourComboName.Requery

in the OnClick event of the combo box

 

[sherylnance]

I tried that with no luck....

any other thoughts?

 

[boblarson]

can you post a stripped down version of your database so I can take a look?

 

[sherylnance]

I stripped out all but about 1300 records.

 

[boblarson]

Check out the changes here. I changed Student# to StudentNum as well as you should not use special characters in field names as it will come back to bite you at some point. Also, don't use reserved words as field or object names either (just an FYI).

I added this to the On Current event of the main form:

    Forms![Choice Form].[Choice subform1].Form.Choice1.Requery
    Forms![Choice Form].[Choice subform1].Form.Choice2.Requery
    Forms![Choice Form].[Choice subform1].Form.Choice3.Requery

 

[sherylnance]

That didn't seem to work either. Darnit!!

 

[boblarson]

You may not have gotten in and looked at everything I did. The main thing was those combo box codes, but there was more. I renamed your student# to studentnum and then put code in to populate the studentnum textbox on the subform instead of trying to let it do it itself.

The sample I posted, which is your own database, works for me.

 

[sherylnance]

I finally see the difference. There was also a line that referenced the student number from the main form. I guess thats the key :-D

You are my hero!!!!

 

[sherylnance]

OK one more question. I also need to save the choices for each student. It appears that it saves one row in the choices table but then over writes the row for the next student. This is different than it was acting before the choice combo boxes were working.

BTW... Is there a class or a book that I can get to learn more about this kind of thing?

 

[boblarson]

I had to go back to work today, so I won't be able to do anything until after work, which will be in about 9 hours or so.

 

[boblarson]

Okay, this is how I fixed the problem. Take out the code from the main form that assigned the student number from the main form to the subform. Then, delete the subform and click on the toolbox to add the subform back in. Be sure to use the wizard and select the appropriate links (AA Student Number on Master and StudentNum on the subform). I don't know why but when trying to do it manually it didn't seem to work, but when I deleted the subform, added it back in using the wizard and selected the links in the wizard, it worked. See attached.