Can it be done?? (1 Viewer)

NearImpossible

Registered User.
Local time
Today, 15:44
Joined
Jul 12, 2019
Messages
225
As I like to push my knowledge of Access and VBA, I'm working on a database to track some stuff for a game that involves multiple pieces of information.

Here's what I've gotten so far:

- Select data from multiple tables and create a new table via query
- Using VBA, I then change the table name, based off of user input, and use that same name to create a new page on a tab control to match it.

Here's what I'm looking to do:

I have blank sub form titled "BlankForm", with no table assigned to it, that I would like to add to every newly created page and then be able to assign the newly created table as the data source for that sub form on the corresponding tab.

Is it possible to do or am I just dreaming?
 

plog

Banishment Pending
Local time
Today, 15:44
Joined
May 11, 2011
Messages
11,611
Yes it's possible and you're doing it all wrong.

There's no need to dynamically have a database create tables. There's no need to dynamically name them. There's no need to dynamically build forms upon those tables. If you want a database to track this data, then you should read up on database. I suggest starting with normalization (https://en.wikipedia.org/wiki/Database_normalization), that's the process of properly setting up your tables.

Set up properly tables can accomodate all your data--you don't need to build new tables, or add fields or alter them. Set them up properly initially and they will work for all the data you need.

Perhaps you can better explain your data, perhaps upload a copy of your database. Be sure to complete the Relationship Tool before you do.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:44
Joined
May 7, 2009
Messages
19,169
how did you create the new tab.
access only allow creation of controls (though vba) in design view of the form.

for the "BlankForm" set its SourceObject property to the name of the table:

Me.BankForm.SourceObject="table." & Me.txtTableName
 

NearImpossible

Registered User.
Local time
Today, 15:44
Joined
Jul 12, 2019
Messages
225
I open the form in design mode, but hidden, make the changes, close and save and then reopen it.

Here is the code I have so far

Private Sub Build_Era_Click()

'Hides Warnings
DoCmd.SetWarnings False


'Selects Era Goods and Builds Era table
DoCmd.OpenQuery "BuildEraTable Query"


'Asks for Era Name to rename Era table
Dim tdf As TableDef
Dim strPrompt As String

strPrompt = InputBox("Enter the Era Name")
CurrentDb.TableDefs("Era").Name = strPrompt


'Opens AllErs form, Inserts new Tab and renames it as the Era Name
DoCmd.OpenForm "AllEras", acDesign, WindowMode:=acHidden
Forms!AllEras.Eras.Pages.Add


'Sets current Tab to Era Name
Forms!AllEras.Eras.Pages(Forms!AllEras.Eras.Value).Caption = strPrompt


'Copies the BlankEraGoods form and renames it to the Era Name
DoCmd.CopyObject , strPrompt, acForm, "BlankEraGoods"


'Sets Era Table as the record source for the Era form
DoCmd.OpenForm strPrompt, acDesign, WindowMode:=acHidden
Forms(strPrompt).RecordSource = strPrompt
DoCmd.Close acForm, strPrompt, acSaveYes


<INSERT FORM INTO TAB CONTROL>


'Closes, Saves and Reopens the AllEras form
DoCmd.Close acForm, "AllEras", acSaveYes
DoCmd.OpenForm "AllEras", acNormal

End Sub


I modified your code a bit and its all working other than the last piece. How can I add a Form to the newly created tab using VBA as the BlankForm is only a template?

The process i'm trying to accomplish is as follows:
1. Create a new table and rename it based off of user input (Done)
2. Create a new tab and rename it using the same input (Done)
3. Rename the BlankForm using the same input (Done)
4. Assign the newly renamed table to the newly renamed "BlankForm" (Done)
5. Insert the newly renamed "BlankForm" on the newly created tab

So I am just missing step 5 to make this work

Any help is greatly appreciated.

thank you
Kevin
 
Last edited:

NearImpossible

Registered User.
Local time
Today, 15:44
Joined
Jul 12, 2019
Messages
225
plog,

Thanks for the information, however I did fail to mention that this will be an ever changing database where each Tab will be created on the fly based on different sets of criteria.

There are only 2 static tables, an Age Table (AgeID(PK), Age, Selected) and a Goods SubTable (AgeID, GoodsID(PK), Goods) that are joined on the AgeID

From those 2 tables, a dynamic table will be created based on required criteria, hence the query to make a new table base on the "Selected" checkbox

The "BlankForm" is a template to add to the new tab and the new table will then be the Source Object for the BlankForm on the new tab.

The next time a new Tab is needed, the process is repeated based on the required criteria for that age.
 

isladogs

MVP / VIP
Local time
Today, 20:44
Joined
Jan 14, 2017
Messages
18,186
I've approved the post that went for moderation.
Posting this to trigger email notifications
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:44
Joined
May 7, 2009
Messages
19,169
the problem with your approach (code), is that you will find it hard to insert to the proper Page the subform. I've googled it an found none so far. the best approach you have is to design your form and create as many Pages with that many subform in it. then make those pages Visible property to No.

you also need a global counter variable as to how many Pages are visible.
you may also need a Table to save gintPages value.
on a module you may create:
Code:
Global gintPages As Integer

Public Function initPages()
gintPages = dlookup("Value", "theTable")
End Function

now create a macro (autoexec) that will RunCode the initPages() function.
so that everytime your app starts, ginitPages will be initialized as to how many
pages you have so far.

next on the Load event of your form, check each Page caption against the strInput.
if not found, increment gintPages and make the corresponding Page visible, and set its caption.

you can now set the SourceObject of the subform.
Code:
Me.subformName.SourceObject="table." & strInput

on the Unload event of your form, save gintPages to the table again.

btw I haven't tested it yet, only on my imagination.
 

NearImpossible

Registered User.
Local time
Today, 15:44
Joined
Jul 12, 2019
Messages
225
Thanks again for the reply, i've updated my code above and modified your code a bit.

I'm copying the BlankForm, renaming it to the user input and then setting the record source as the corresponding table

I'll just have to figure out to insert a form into a tab control as i'm already working with the tab name, I should be able to figure out the page number.

I'll give your theory a shot as well, this was just something I dreamed up about an hour before my first post as I like to make access do things it wasn't necessarily designed to do :)
 

plog

Banishment Pending
Local time
Today, 15:44
Joined
May 11, 2011
Messages
11,611
Even though you are determined to do this the wrong way, I'll try and explain myself better and then leave this thread (no promises on the many other threads you will be opening to maintain and get this thing to work).

I guarantee your "dynamic" table is going to be storing values that should be in fields in field and table names. For example:

ElfTraits
CharacterID, ElfMagic, ElfPower, ElfHealth
1, 72, 93, 18
2, 66, 41, 38

The correct way to store that data is in a table like so:

Traits
CharacterID, TraitType, TraitScore
1, Magic, 72
1, Power, 93
1, Health, 18
2, Magic, 66
...

That generic table can now hold all values you will ever put it in and doesn't need to be "dynamic" to do so. Again, that's a demonstration of what I am sure you are doing wrong--storing values which should be in the table in table and field names.

Again, what you want to do is possible with a lot of hacks, but its going to be a pain to maintain and extend.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:44
Joined
May 21, 2018
Messages
8,463
I am with Plog. What is being suggested makes ZERO sense. I am sure I could code this, but that does not mean it is not a dumb approach. The problem with these hack jobs is that you might get them to work for that specific task, but to expand from that gets more and more complicated. Do not re-invent the wheel.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:44
Joined
Feb 28, 2001
Messages
27,001
You are essentially trying to build a "faceless" database and there is a serious flaw in the entire concept. A database that has no inherent structure will have no inherent data coherence. You will have trouble building relational integrity, which would make your DB difficult to manage and susceptible to many inconsistencies. You would be unable to assure proper normalization, which isn't always fatal - but it can be crippling in terms of DB efficiency.

The WORST part of this is that you won't be able to keep records of what you built, because you didn't build anything except scaffolding and frameworks. The meat of a DB is how you interact with it. So you've got these tables? So what? Can you get to them easily? You need focused forms, reports, and queries. Without that focus, you have inaccessible data. Inaccessible data is useless data. If the structure is so complex and unstructured that you can't get to it and have no forms, reports, or queries to moderate it, then (pardon if this sounds harsh) there is no practical difference in utility between what you originally described and an empty database.

Databases that we typically see here have focus on building a business model of some company, department, or specific task. Your original structure is totally unfocused with regard to any model. That lack of focus will kill your project.

I am not trying to be rude about this. But if I didn't give you an honest evaluation, I would not be doing you a favor. It might sound like Plog, MajP, and I are being negative, but it is not without reason. If you came here for advice, my advice is to rethink what you are trying to do with a tool that was designed for SIMPLE business applications. The fact that it has extra capability doesn't mean that it will be efficient when given a really complex design.

And just to be clear, this CAN be done. But my forte when I was an active program designer was in making project schedule estimates. This project will have a relatively fast initial implementation time but a truly horrendous, potentially never-ending debugging phrase. That is the part of this idea that will eat your socks.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:44
Joined
Feb 28, 2001
Messages
27,001
I am adding a second note because there are products that purport to do what you describe - but they have a poor track record. If you look a PeopleSoft databases, for example, they claim that they are pretty much going to "do the right thing" with regard to whatever data you define. But they don't tell you that if your data goes outside of their model, they will charge you thousands of dollars per hour to customize the "outlier" data. They work on having a "library" of common things that businesses do. If what you want to do isn't in their library, it is <OH, BOY, lick your chops> expensive customization time. (Been there, saw it done, saw it fail.)

Therefore, if you got this idea from some company that claims to be faceless, it is because they are NOT faceless. It is because they have a lot of little hidden faces to join together based on what data you pick for your tables. They have presets for the most common data elements. What you describe as your intended design does not have that depth of code from which to extract support routines.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:44
Joined
Feb 19, 2013
Messages
16,553
ignoring the issues around normalisation which has already been covered and looking at the code provided, I really don't see the benefit - it looks like same form, different tab, same basic recordsource, different criteria - I presume we are talking about a form with tabs, not a FE set up with tabbed documents.

If this is correct, there are other, easier ways to achieve the effect.

Instead of tabs, use a row of buttons across the top of a form, formatted to look like tabs. You can create enough to go right across the top of the form and include code to hide the ones not in use, adjust button widths if required and include a dropdown at the end for tabs not displayed - you can make them perform much like a web browser does. You can even use code to enable users to 'drag' buttons left/right to change the order.

In the control tag property (or perhaps an associated array, dictionary or recordset) you include the criteria which is applied to the form recordsource when the button is clicked.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:44
Joined
Feb 19, 2013
Messages
16,553
But they don't tell you that if your data goes outside of their model, they will charge you thousands of dollars per hour to customize the "outlier" data.
I agree. Many of these apps are designed to meet 80% of client requirements. The other 20% requires customisation. Which is where I often make my money. Typically the 20% is required by only few users, and most clients are reluctant to spend a large sum for only a few users, even if the requirement is just below business critical. When this happens I can generally offer a solution based around Access/Excel for a fraction of the cost.
 

NearImpossible

Registered User.
Local time
Today, 15:44
Joined
Jul 12, 2019
Messages
225
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
 

Users who are viewing this thread

Top Bottom