dynamic rowsource on continuous subform

[Funger]

I have a form with a continuous subform on it. This subform contains the subject areas of a subject. On this continuous subform, is a dropdown which holds the grade values that are available for the current subject area. Each subject are can have its own set of grade values.

The problem is though that when I change the rowsource of the dropdown, the values that are not in the current rowsource disappear.

I read somewhere to change the rowsource of this dropdown on the Enter event to the limited set, and then change it back to the complete set on the Exit event.

This doesn't seem to work, and all kinds of chaos ensues.

Can anyone please tell me how to get this to work? Of course time is something that I am out of, as report cards are due at the end of next week, and this software is being used by multiple clients. Sigh.

Thanks for any help in advance.

-Funger

 

[Pat Hartman]

I have never found a way to make this work (and I have looked since I have had the problem) so don't beat yourself too much. What I eventually did was leave the combo's rowsource complete but add a code to it so you could see two fields when you opened it. I then added code in the BeforeUpdate event of the combo to restrict the values as necessary.

 

[Funger]

Sigh. Thanks for replying at least. I can't believe that this functionality was completley overlooked in Access... Then again, knowing about some of the other shortcomings in Access, maybe it isn't so hard after all.

-Funger

 

[Pat Hartman]

It isn't a shortcoming of Access per se, it is a problem of knowing how many sets of properties need to be maintained. The number of rows represented in a subform is infinite. How many copies of properties should Access allow for?

A possible solution that just occured to me is to use a regular textbox on the subform but lock it so the user can't type into it. Then in the mainform have an unbound combo. In the current event of the subform requery the mainform combo to get its rowsource filtered properly and copy the value from the subform record to the mainform combo. Then let the user use that combo to select the correct value. In the BeforeUpdate event of the subform, look to see if the locked subform control is different from what is showing in the combo and if it is, change the subform record. Since I haven't tried this, I'm not sure what the pitfalls may be or even if it is doable.