I have a database that tracks expenditures by a number of programs run by a smaller number of social service agencies (one-to-many relationship). From time to time agencies and programs are either added or dropped or change their names, I need to give the user a way to edit the two (related) lists. I can see two ways of doing this: with dependent combo boxes or with a form and a subform. I’ve tried both ways, and, for different reasons, I can’t make either one work.
The form with dependent combo boxes is bound to a query uniting the table with agency names (tblAgencies) with the one with program names (tblPrograms). The combo boxes work, but when I make a change in the box (each box has its respective table for a control source), instead of changing the record in the table, Access adds a new record. I tried adding a delete button, but that doesn’t do the job. I also added a button to save changes, but that doesn’t make a difference, either.
In the form/subform option, the main form is bound to tblAgencies, and the subform is bound to tblPrograms. They are united on the agencies foreign key in tblPrograms. There is a combo box on the main form that displays the agencies. The subform is in datasheet view and displays all of the programs that belong to a particular agency. The problem is that when I change agencies in the combo box, the subform doesn’t update. I tried putting a requery statement in the After Update event for the Agencies combo box, but that doesn’t help.
Which is the better option, and what do I need to change to make it work?
Thanks,
Henry