Hi to all!
I've looked over the archives and the FAQ's, but did not see anything that gave a good answer to the dilemma I've been working on.
I'm building an application that tracks employee donations. I use a main form to enter date, employee, electronic signature, and a few other things. A subform captures each employee's contribution profile for a particular year (event). This needs to be either a continuous or datasheet subform, as each employee may shoot their payroll deduction towards a large choice of charities. We have 6 different chapters with their own charities, and this is how I broke it down. I have a table with the chapters listed and an ID key. Next a table with all the charities listed, with the chapter ID (foreign key) associated.
In the subform, I've placed two combo boxes that cascade. Use the chapter combo to choose a chapter, and the charity combo gets filtered to those charities belonging to that chapter. This is necessary, because there are different chapters of the same charity (Red Cross in different counties, for example), so there has to be a way to separate them, and this seemed the best.
The combo boxes work fine, on their own, but when put in a continuous or datasheet subform, they do NOT work so well. The first time it is used for each contribution event, it does a great job of filtering the combo box. But when you have someone making several divisions of their contribution to charities in more than one chapter, it becomes ineffective. It will always filter out the charity combo of the record being written. If it's a different chapter, the previous chapter entries for the event lose their charities on the form...they just go blank. Looking at the table, they DO get saved, it's just that they no longer show as they should in the form.
Is this a quirk of Access, that cascading combo boxes do this in continuous or datasheet subforms? There are a couple of good FAQ's on using combo boxes, but none satisfactorily answered this issue, though one tried to (it was hard to tell how the writer implemented his solution though). I've tried a couple of work-arounds, to no avail.
Has anyone successfully broken through this? If you are interested, I can post some code and the underlying row source stuff, if need be. I was just curious as to whether there is an explanation for Access' behavior here and if anyone had battled it out.
Thanks in advance,
Joe Cruse
I've looked over the archives and the FAQ's, but did not see anything that gave a good answer to the dilemma I've been working on.
I'm building an application that tracks employee donations. I use a main form to enter date, employee, electronic signature, and a few other things. A subform captures each employee's contribution profile for a particular year (event). This needs to be either a continuous or datasheet subform, as each employee may shoot their payroll deduction towards a large choice of charities. We have 6 different chapters with their own charities, and this is how I broke it down. I have a table with the chapters listed and an ID key. Next a table with all the charities listed, with the chapter ID (foreign key) associated.
In the subform, I've placed two combo boxes that cascade. Use the chapter combo to choose a chapter, and the charity combo gets filtered to those charities belonging to that chapter. This is necessary, because there are different chapters of the same charity (Red Cross in different counties, for example), so there has to be a way to separate them, and this seemed the best.
The combo boxes work fine, on their own, but when put in a continuous or datasheet subform, they do NOT work so well. The first time it is used for each contribution event, it does a great job of filtering the combo box. But when you have someone making several divisions of their contribution to charities in more than one chapter, it becomes ineffective. It will always filter out the charity combo of the record being written. If it's a different chapter, the previous chapter entries for the event lose their charities on the form...they just go blank. Looking at the table, they DO get saved, it's just that they no longer show as they should in the form.
Is this a quirk of Access, that cascading combo boxes do this in continuous or datasheet subforms? There are a couple of good FAQ's on using combo boxes, but none satisfactorily answered this issue, though one tried to (it was hard to tell how the writer implemented his solution though). I've tried a couple of work-arounds, to no avail.
Has anyone successfully broken through this? If you are interested, I can post some code and the underlying row source stuff, if need be. I was just curious as to whether there is an explanation for Access' behavior here and if anyone had battled it out.
Thanks in advance,
Joe Cruse
