Apparently I have already offended plog as he has already made up his mind about me based off of 2 posts, and based off of his responses, I have a good idea of the type of person he is as well. Anyway, I digress, so let me step back for a second and let you know where i'm coming from.
I will never be offended by a response that someone is willing to take their time to give me, as long as they can explain their reasoning behind it, especially on a subject i'm not 100% familiar with, its how I learn.
With that being said, everything I know about Excel, Access and VBA has been self taught and furthered along by asking questions on these types of forums, so I apologize if i'm giving the impression that i'm ignoring your responses as I keep asking questions as I love challenges and more often than not, don't give up until I can make it work, whether conventional or not.
I already have a similar working solution in Excel that I have been using for the past couple years, but as I said, I love challenges so I figured I would try to make it work in Access. If I can't, then I will move on, no harm, no foul with just a little more knowledge of VBA and Access.
Based off of the responses i'm getting, I probably should have been a little more clear so let me explain a little further on the end product i'm trying to achieve as this went from a VBA question to database 101. Note that few things have been added since posting my code above so above posts will not match 100% what i'm describing below.
The game is Forge of Empires, which has multiple ages and each age has 5 goods in it. In each age there are several researches that need to be completed before you can progress to the new age. Based on the research, it can require anywhere from 1 to all 5 of the goods from that age, and a different number of each good per research. As you progress through new ages, the requirements grow and you may need goods from multiple ages.
I have the following tables setup and joined on the EraID: (Examples are data for 2 ages)
AGE TABLE (EraID(PK), Age Name, Selected)
-----------------------------------------
1, Bronze Age, Checkbox
2, Iron Age,Checkbox
AGE GOODS TABLE (EraID, GoodsID(PK), Good, #ofGoods)
------------------------------------------
1, 1, Marble, 100
1, 2, Lumber, 100
1, 3, Dye, 100
1, 4, Stone, 100
1, 5, Wine, 100
2, 6, Cloth, 100
2, 7, Ebony, 100
2, 8, Jewelry, 100
2, 9, Iron, 100
2, 10, Limestone, 100
AGE RESEARCH TABLE (EraID, ResearchID(PK), Research)
----------------------------------------------------
1, 1, Craftwork
1, 2, Statue of Zeus
1, 3, Tower of Babel
2, 4, Architecture
2, 5, Militia
2, 6, Processions
2, 7, Agriculture
2, 8, Archery
2, 9, Sewerage
2, 10, Fortification
2, 11, Thermae
2, 12, Plowing
2, 13, Chain of Command
2, 14, Colosseum
2, 15, Lighthouse of Alexandria
I have an "Age" form containing the AGE TABLE with the AGE GOODS TABLE and AGE RESEARCH TABLE as Sub Forms. As new Ages are added to the game, This form will be used to add the new Age Name, Goods and Researches.
I then have a "Build New Age" form containing the AGE TABLE where you can select the applicable Age(s) to pull goods from, based on the goods required for the researches in the new age. After the Ages are selected, you click the Build button which then asks for the New Age Name.
After entering the name, 2 queries run to build an ERA GOODS TABLE, and ERA RESEARCH TABLE, using the "Selected" column from the AGE TABLE (In this case the Iron Age requires all goods from both the Bronze and Iron Ages for its researches)
ERA GOODS TABLE (EraID, Age, Good, #ofGoods)
----------------------------
2, Bronze Age, Marble, 100
2, Bronze Age, Lumber, 100
2, Bronze Age, Dye, 100
2, Bronze Age, Stone, 100
2, Bronze Age, Wine, 100
2, Iron Age, Cloth, 100
2, Iron Age, Ebony, 100
2, Iron Age, Jewelry, 100
2, Iron Age, Iron, 100
2, Iron Age, Limestone, 100
ERA RESEARCH TABLE (EraID, Age, Research)
-----------------------------------------
2, Iron Age, Architecture
2, Iron Age, Militia
2, Iron Age, Processions
2, Iron Age, Agriculture
2, Iron Age, Archery
2, Iron Age, Sewerage
2, Iron Age, Fortification
2, Iron Age, Thermae
2, Iron Age, Plowing
2, Iron Age, Chain of Command
2, Iron Age, Colosseum
2, Iron Age, Lighthouse of Alexandria
In the background there is a BlankFormTemplate that will house the data from the ERA GOODS TABLE and ERA RESEARCH TABLEs
At this point a new sheet is added to the TabControl on the "World" form and renamed to match the User Input, the ERA GOODS TABLE and ERA RESEARCH TABLE are renamed to match the User Input, the BlankFormTemplate is copied and renamed to match the User Input and then the tables are assigned as the record sources for the applicable subforms on the template.
This all works in the background and when I open the Form, everything is how I need it to be, the only piece I am missing is inserting the Form on a newly created sheet in the TabControl.
Every Sheet on the "World" form is an Age and will contain is own set of data in its newly created tables, based on the processes above
As this is the "Module & VBA" section of the forum, I came seeking advice on how how to add a table as a record source and how to add a form to a TabControl sheet using VBA.
I am now able to set the record source and everything is working as intended, with the exception of being able to add the form to a TabControl Sheet, so I ask once again "Can it be done?".
I know how to identify and sort Tab Controls with VBA, I just don't know how to add a form to a TabControl sheet using VBA.
Once again I appreciate everyone's input and I'm not sure why I immediately gravitated towards using a TabControl, when I can just use VBA to add a button to a form and code it to open the newly created form.
I think I will use a modified version CJ_Londons suggestion of using buttons and be done with it, however if anyone is willing to share, I would still like to know how to add a form to a TabControl Sheet using VBA.
Thank you
Kevin