Solved

Access 2007 - Subform datasheet

Posted on 2011-02-14
3
388 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now