Access 2007 - Subform datasheet
Posted on 2011-02-14
I've got an interesting situation I've got to create and am not sure exactly how to do it.
I have an unbound form that I've created to capture data to insert into a 'Contracts' table. The form has insert, update and delete functionality. So if you are in add mode and you input data into the form and close it, the data won't be saved unless you clicked the save button. Likewise, if you are in edit mode and make changes to the fields in the form none of those changes will be updated in the data unless you click the save button.
I've got another table called FundingSource. Now a contract can have many funding sources and a funding source can supply funding to many contracts. So I've got an intermediary table between Contract and FundingSource called ContractFundingSource. This table has the contractID and a FundingSourceID to bind the two tables together and show relationships, and it also has an amount field to show the amount of funding coming from the relationship.
Previously on the Contract form I have had a listbox showing all the available funding sources and multi select property turned on. So a user could select multiple funding sources and on save I had functionality coded so to run through each item in the listbox and if selected I would create a relationship in the ContractFundingSource table if one did not already exist and if a funding source was not selected and a relationship did exist I would delete it. This was all working well and good until I had to add an amount to the relationship. So I now need a way to specify the amount that is coming from each funding source selected in the listbox.
I need a solution that is going to be intuitive to the user and not be clunky. I am ok throwing out the listbox in favor of a different solution.
One idea that I've been toying with was to have a subform that would be a datasheet. On that datasheet you could select a funding source and enter an amount. The problem with this idea is that on datasheets when you enter data into records, that data is saved immediately when you leave the record. I would still need this to perform on the basis of only saving changes when the save button is clicked.