[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access 2007 - Subform datasheet

Posted on 2011-02-14
3
Medium Priority
?
410 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 85
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

656 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