Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-11-2019, 08:28 PM   #1
NearImpossible
Newly Registered User
 
Join Date: Jul 2019
Posts: 56
Thanks: 44
Thanked 0 Times in 0 Posts
NearImpossible is on a distinguished road
Can it be done??

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?

NearImpossible is offline   Reply With Quote
Old 07-11-2019, 08:36 PM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,326
Thanks: 10
Thanked 2,263 Times in 2,215 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Can it be done??

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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
NearImpossible (07-11-2019)
Old 07-11-2019, 08:54 PM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,993
Thanks: 64
Thanked 2,546 Times in 2,445 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Can it be done??

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
NearImpossible (07-11-2019)
Old 07-11-2019, 09:27 PM   #4
NearImpossible
Newly Registered User
 
Join Date: Jul 2019
Posts: 56
Thanks: 44
Thanked 0 Times in 0 Posts
NearImpossible is on a distinguished road
Re: Can it be done??

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 by NearImpossible; 07-11-2019 at 11:28 PM.
NearImpossible is offline   Reply With Quote
Old 07-11-2019, 09:51 PM   #5
NearImpossible
Newly Registered User
 
Join Date: Jul 2019
Posts: 56
Thanks: 44
Thanked 0 Times in 0 Posts
NearImpossible is on a distinguished road
Re: Can it be done??

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.
NearImpossible is offline   Reply With Quote
Old 07-11-2019, 10:01 PM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,085
Thanks: 110
Thanked 2,739 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Can it be done??

I've approved the post that went for moderation.
Posting this to trigger email notifications
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
NearImpossible (07-11-2019)
Old 07-11-2019, 11:03 PM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,993
Thanks: 64
Thanked 2,546 Times in 2,445 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Can it be done??

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.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
NearImpossible (07-11-2019)
Old 07-11-2019, 11:16 PM   #8
NearImpossible
Newly Registered User
 
Join Date: Jul 2019
Posts: 56
Thanks: 44
Thanked 0 Times in 0 Posts
NearImpossible is on a distinguished road
Re: Can it be done??

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
NearImpossible is offline   Reply With Quote
Old 07-12-2019, 05:41 AM   #9
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,326
Thanks: 10
Thanked 2,263 Times in 2,215 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Can it be done??

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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
MajP (07-12-2019)
Old 07-12-2019, 02:33 PM   #10
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,759
Thanks: 28
Thanked 526 Times in 499 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Can it be done??

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.
MajP is offline   Reply With Quote
Old 07-12-2019, 07:16 PM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,248
Thanks: 86
Thanked 1,624 Times in 1,507 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Can it be done??

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
NearImpossible (07-13-2019)
Old 07-12-2019, 07:24 PM   #12
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,248
Thanks: 86
Thanked 1,624 Times in 1,507 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Can it be done??

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
NearImpossible (07-13-2019)
Old 07-13-2019, 12:18 AM   #13
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,138
Thanks: 40
Thanked 3,613 Times in 3,488 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Can it be done??

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
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
NearImpossible (07-13-2019)
Old 07-13-2019, 12:24 AM   #14
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,138
Thanks: 40
Thanked 3,613 Times in 3,488 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Can it be done??

Quote:
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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following 2 Users Say Thank You to CJ_London For This Useful Post:
NearImpossible (07-13-2019), The_Doc_Man (07-13-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 11:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World