Create Template from a Form

[access_viper]

Hello, it has been a while since I have posted. Anyway here goes...

Is there a way to save a form as a template and then apply that form to different tables? I run into the issue having multiple tables that I need to interface with the same form.


Any recommendations?

Thank you for reading.

 

[CyberLynx]

Just use the same Form but change the Forms' RecordSource property as required:

Forms!MyFormName.RecordSource = whatever

or

Forms("MyFormName").RecordSource = whatever

Any Access Form can be used as a Template. If what you want to do is temporarily create multiple instances of the same Form but with a different RecordSource, then you can do something like this:

First Declare a Public Variable Array as Form. You can do this within the declarations section of the initiating Form's code module or within a Database Code Module. More about this later.

As a sample test...Decide which Form it is you want to use as a Template. We'll need the name of that Form. For the sake of this example we'll call your Template Form: MyTemplateFormName. As I said earlier, this can be any Access Form you created.

Create a small blank Form and place a Command Button into the middle of it. In the Declarations section of this Form (under the Option Compare Database and or Option Explicit) enter:

Dim MyForms() As Form_MyTemplateFormName

In the OnClick event of that Command Button now in the middle of the Form, enter this code:

   [COLOR="DarkGreen"]'Declare our loop Variable[/COLOR]
   Dim I As Integer
   
   [COLOR="DarkGreen"]'Loop 5 times to create 5 Forms based off the
   'supplied template Form ([I][B]MyTemplateFormName[/B][/I]).[/COLOR]
   For I = 0 To 4
      [COLOR="DarkGreen"]'Dimension our First Form Array element[/COLOR]
      ReDim Preserve MyForm(I)
     [COLOR="DarkGreen"] 'Set this Array element to be new Form(I)[/COLOR]
      Set MyForm(I) = New Form_MyTemplateFormName
      
      [COLOR="DarkGreen"]'Set properties for the new Form instance.[/COLOR]
      With MyForm(I)
         [COLOR="DarkGreen"]'Make the Form invisible until properties
         'setup is complete[/COLOR]
         .Visible = False
         [COLOR="DarkGreen"]'Set the Record Source for each Form Instance.
         'This can be anything you like. In this Select/Case
         'I'm just setting the RecordSource for each of the 5
         'Form instances as they are created.[/COLOR]
         Select Case I
            Case 0
               .RecordSource = "Table1"
            Case 1
               .RecordSource = "Table2"
            Case 2
               .RecordSource = "Query1"
            Case 3
               .RecordSource = "SELECT * FROM Table1;"
            Case 4
               .RecordSource = "SELECT * FROM Table3 WHERE Name='" & whatever & "';"
         End Select
         [COLOR="DarkGreen"]'Set the new Forms' Title Bar Caption.[/COLOR]
         .Caption = "My New Form Number " & I
         [COLOR="DarkGreen"]'Make the new Form visible.[/COLOR]
         .Visible = True
         [COLOR="DarkGreen"]'Set Focus to the new Form Instance.[/COLOR]
         .SetFocus
      End With
   Next I

To clean things up, enter this code into the new Forms' OnUnload event:

   [COLOR="DarkGreen"][B]'Clean up any instances of opened Forms.
   '==============================[/B][/COLOR]
   [COLOR="DarkGreen"]'Trap Errors. An Error will be raised if
   'there are no Array Elements.[/COLOR]
   On Error GoTo Exit_Unload
   
   [COLOR="DarkGreen"]'Declare our loop variable[/COLOR]
   Dim I As Integer
   
   [COLOR="DarkGreen"]'Enumerate through all the opened Forms
   'and Close them. Then we want to destroy
   'the instance of that Form.[/COLOR]
   For I = LBound(MyForm) To UBound(MyForm)
      [COLOR="DarkGreen"]'Close the Form[/COLOR]
      DoCmd.Close acForm, MyForm(I).Name
      [COLOR="DarkGreen"]'Destroy reference to it.[/COLOR]
      Set MyForm(I) = Nothing
   Next I
   
Exit_Unload:
   [COLOR="DarkGreen"]'Free memory - Destroy the array[/COLOR]
   Erase MyForm
   [COLOR="DarkGreen"]'Clear Errors if any.[/COLOR]
   If Err <> 0 Then Err.Clear

If the the Forms Array Variable (MyForm) is declared within a Form Code Module then when that Form is Closed, all created new Form Instances will be destroyed.

If this was a Public Array (set within a Database Code Module) and the Unload event Code above was removed, then all new Form instances would remain opened until they are either closed or the Database is closed.

Some mechanism should be put into place so as to properly destroy the new Form Instances somewhere within the Database. Something very similar to what is shown in the Forms' Unload event code above. However you want to do this is entirely up to you.

.