JosephEricDavis
asked on
Access 2007 - Subform datasheet
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.
Any ideas?
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.
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, they would. That's the nature of bound Access forms. You can use unbound forms, or temporary tables, and manage the data save yourself, but if you maintain this in the standard Access bound scenario for your subform, then you're at the mercy of Access in many regards.
<If the user wen to the funding source sub form and start trying to create relationships there would be an issue because the current contract would not yet exist to tie into the contract/funding relationship in the M2M table. >
This would be something you'd need to catch via code BEFORE the user is allowed to begin creating relationships. That's a "business logic" issue, and not a data structure issue (at least to my mind). If the business requires that a Contract exist BEFORE you can fund it, then your project would need to mimic that logic.
<If the user wen to the funding source sub form and start trying to create relationships there would be an issue because the current contract would not yet exist to tie into the contract/funding relationship in the M2M table. >
This would be something you'd need to catch via code BEFORE the user is allowed to begin creating relationships. That's a "business logic" issue, and not a data structure issue (at least to my mind). If the business requires that a Contract exist BEFORE you can fund it, then your project would need to mimic that logic.
ASKER
This would especially be problematic when the user is on the form creating a new contract that hasn't been saved in the database yet. If the user wen to the funding source sub form and start trying to create relationships there would be an issue because the current contract would not yet exist to tie into the contract/funding relationship in the M2M table.