View Single Post
Old 07-11-2019, 09:27 PM   #4
Newly Registered User
Join Date: Jul 2019
Posts: 104
Thanks: 66
Thanked 1 Time in 1 Post
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

'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


'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

Last edited by NearImpossible; 07-11-2019 at 11:28 PM.
NearImpossible is offline   Reply With Quote