Solved

Dynamic list of items in a table to be used on a form or subform

Posted on 2013-06-13
17
290 Views
Last Modified: 2013-06-14
I'm trying to figure out how to have a master list of items for the user to use in a subform.  The list of items would be in tblMasterList.  This list of items would be managed via a form since items could be added at any time.  No problem there so far.

I would use the master list of items in a "Budget" form.  The budget I imagine will need a tblBudgetHeader and a tblBudgetDetails.  The header table would have the budgetID and the budgetName.  The details table would have the items from tblBudgetDetails and the link would be BudgetID.

So the budget form, (Header and Details), will consist of the header with tblBudgetHeader as its record source. The detail section will be a subform with tblBudgetDetails as its record source.

Now here's the issue... If I have the table tblMasterList and the user adds an item, how does that item dynamically get added to tblBudgetDetails so it can appear and be used on the subform?

Has anyone ever done something like this before or do I have everyone as confused as I am?
0
Comment
Question by:SteveL13
  • 8
  • 8
17 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39245105
<<<<<Now here's the issue... If I have the table tblMasterList and the user adds an item, how does that item dynamically get added to tblBudgetDetails so it can appear and be used on the subform?>>>>>

How would you normally add and existing item to tblBudgetDetails???  Your statement above is assuming ALL items  in tblMasterList will always exist in tblBudgetDetails ...  Is that correct???


ET
0
 

Author Comment

by:SteveL13
ID: 39245260
Items would be added to the tblMasterList via an input form.  And yes, ALL items  in tblMasterList will always exist in tblBudgetDetails.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39245276
Hi SteveL13, maybe you misunderstood my original question.  I am asking ...

How would you normally add an existing item to --------> tblBudgetDetails???  Not tblMasterList.

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39245481
Without a sample I can only speculate ... Can you load up a sample db that includes your tables and forms???

Is this correct ...

1.) You have a tblMasterList which has a user data entry edit form to maintain your items.
2.) You have a tblBudgetHeader and a tblBudgetDetails with a BudgetID field in both which can be accessed using a from/sub-form setup.

Now, the question is ... how do you enter the tblBudgetDetails for a specific Budget on your form??  Are you using a Combo Box in the detail section of the form???


ET
0
 

Author Comment

by:SteveL13
ID: 39245619
Ok, Please see the attached.

If you open frmProjectBudget this is how I want the input form to work.  But as it stands right now the fields are all static and I don't like the way I have it working.  The problem is, if you open tblMasterBudgetItems (which has no connect to frmProjectBudget right now), this is how I would like to work.  The user can add (insert) an item with the use of frmMasterBudgetItemListing and the new item will be there in tblMasterBudgetItems and also be sorted by the sort # they enter when they run the report rptMasterBudgetItems.

But I do not know how to take all the (original and any added) items available on the frmProjectBudget when a new budget is entered.

I sure hope you can follow this.  I'm lost.
Budget.accdb
0
 

Author Comment

by:SteveL13
ID: 39245644
Note:  The calculations on frmProjectBudget aren't working because I know this is not the right way to go.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39245956
Ok, I will look at it tonight and respond once I get home.

ET
0
 

Author Comment

by:SteveL13
ID: 39245962
Thank you very much.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39246192
Unless I am missing something, all you need to do is make tblMasterList the row source of a combo box for selecting an item on the subform.  When an item is selected, its ID (or other appropriate field) is added to the subform's record source table.  If you are worried about the list getting out of date, you could requery the combo box from some appropriate event.
0
 

Author Comment

by:SteveL13
ID: 39246374
But I don't want the user to have to add items on the subform.  All of them need to already be there and come from tblMasterList.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39247748
Hi SteveL13,

I had a chance to download your sample db and played around with it.  Please review the attached sample file that I am uploading.  

Below are some of the specifics that I changed.

1.) The dynamic list of tblMasterBudgetItems needed to be added to tblProjectBudgetDetails as rows based on the SortKey not one record with a field for each SortKey as you had it.  

2.) Each row in tblProjectBudgetDetails includes unique ID, BudgetID, SortKey and ItemPrice fields that are joined back to tblProjectBudgetHeader and tblMasterBudgetItems when needed on the relative Form objects.

3.) You needed a separate Command Button to Create/Edit/Delete a Budget Header.  Adding a new BudgetID will add all tblMasterBudgetItems to tblProjectBudgetDetails using the new BudgetID.  Deleting a BudgetID will delete all tblProjectBudgetDetails for the specific budget.

4.) Adding a new item to tblMasterBudgetItems will add this item to all budgets in tblProjectBudgetDetails.  Deleting an item in tblMasterBudgetItems will delete this item from all budgets in tblProjectBudgetDetails.

Let me know if you have any questions.

ET
Budget-New.accdb
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39247828
Also, in tblMasterBudgetItems and on frmMasterBudgetItemListing, I added a field called IndentSpaces.  That is used to determine how many spaces to indent your HeaderName and Sub-HeaderName fields.

ET
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
ID: 39247980
Steve,

Use this version of your modified sample db.  Since the Header rows are included in the Budget tables ... this ensures the ItemPrice field for Headers will always be blank.

ET
Budget-New2.accdb
0
 

Author Comment

by:SteveL13
ID: 39248490
All I can say is, WOW!  Thank you so much.  I really do appreciate the time you put into this.
0
 

Author Closing Comment

by:SteveL13
ID: 39248491
Fantastic. Thank you.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39248523
Thanks for the points Steve.  I build Front-End GUI interfaces for several clients using Access/VBA connected to high end database servers like SQL Server, MySQL, Oracle, etc.  Access is a very powerful development environment when used properly.  

Once I looked at your project it was determined you were in the right church just the wrong pew  :-)  You had the right idea but just wasn't sure how to structure it in Access.  I just put a little time in showing you what could be done with your idea.  With Access, VBA and SQL the possibilities are limitless.

If you ever need some additional or advanced projects developed, just contact me at the email address in my profile.  Good reasonable rates and fast turnaround.  

ET
0
 

Author Comment

by:SteveL13
ID: 39248567
I will definitely keep you in mind.  Thanks again.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

13 Experts available now in Live!

Get 1:1 Help Now