Solved

Access 2007 - Subform datasheet

Posted on 2011-02-14
3
394 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:JosephEricDavis
  • 2
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34889468
So for a given Contract, you can have multiple funding sources, and you now need to define the amount which will be used from each of those sources?

It would seem a typical Master-Child sort of relationship would work here, since you can use the Join table to manage the Many-to-Many relationship. Build a form that shows your Contract info, another form (datasheet or continuous) to show you Funding sources. The Funding form would become a subform. On the Funding form, you'd include a Combo so the user could chooose the Source, and another textbox so the user could enter the Amount. This information would be stored in the Join table you mentioned earlier, and the Combo would be based on the table hosting your Funds data (i.e. the name of the fund, etc).

Does that make sense? It's a method I've used with M-2-M relationships, and it seems to work pretty well.
0
 
LVL 7

Author Comment

by:JosephEricDavis
ID: 34889537
Yeah, I totally get that.  But the problem I foresee is that in this sub form when the user makes changes, those changes would immediately be saved in the database when the user leaves the data row.  The rest of the form is only saved when the user clicks on the save button.

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.
0
 
LVL 84
ID: 34909324
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.

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Modal form 11 31
Access check if a table is open 4 43
SetFocus doesn't wait for input 14 34
Use .MoveNext in VBA but have it filter criteria 2 12
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question