Solved

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

Posted on 2013-06-13
17
301 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
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Link Excel Spreadsheet to Access, not using Transferspreadsheet 5 29
Password on a button in Access 2013 7 35
Explanation of Access VBA code 2 36
DBF to ... Converter 5 43
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

920 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

12 Experts available now in Live!

Get 1:1 Help Now