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?
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?
ASKER
Items would be added to the tblMasterList via an input form. And yes, ALL items in tblMasterList will always exist in tblBudgetDetails.
Hi SteveL13, maybe you misunderstood my original question. I am asking ...
How would you normally add an existing item to --------> tblBudgetDetails??? Not tblMasterList.
ET
How would you normally add an existing item to --------> tblBudgetDetails??? Not tblMasterList.
ET
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
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
ASKER
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
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
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
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
ASKER
Note: The calculations on frmProjectBudget aren't working because I know this is not the right way to go.
Ok, I will look at it tonight and respond once I get home.
ET
ET
ASKER
Thank you very much.
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.
ASKER
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.
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
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
Also, in tblMasterBudgetItems and on frmMasterBudgetItemListing
ET
ET
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All I can say is, WOW! Thank you so much. I really do appreciate the time you put into this.
ASKER
Fantastic. Thank you.
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
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
ASKER
I will definitely keep you in mind. Thanks again.
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