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

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?
SteveL13Asked:
Who is Participating?
 
Eric ShermanAccountant/DeveloperCommented:
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
 
Eric ShermanAccountant/DeveloperCommented:
<<<<<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
 
SteveL13Author Commented:
Items would be added to the tblMasterList via an input form.  And yes, ALL items  in tblMasterList will always exist in tblBudgetDetails.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
Eric ShermanAccountant/DeveloperCommented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
SteveL13Author Commented:
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
 
SteveL13Author Commented:
Note:  The calculations on frmProjectBudget aren't working because I know this is not the right way to go.
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, I will look at it tonight and respond once I get home.

ET
0
 
SteveL13Author Commented:
Thank you very much.
0
 
Helen FeddemaCommented:
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
 
SteveL13Author Commented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
SteveL13Author Commented:
All I can say is, WOW!  Thank you so much.  I really do appreciate the time you put into this.
0
 
SteveL13Author Commented:
Fantastic. Thank you.
0
 
Eric ShermanAccountant/DeveloperCommented:
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
 
SteveL13Author Commented:
I will definitely keep you in mind.  Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.