OK the reason I asked for the context of this is because you mentioned referring to a form using a variable. I see that you are relatively new to Access so I assumed you might not know the differences technically. A variable has different meanings in different situations.

 

Now looking at your code I can see that yes....you are using a variable...but only in the sense that it is a string variable referring to the 'name' of the form rather than the form itself as an object reference. There is a subtle difference.

 

I have no idea what your overall design strategy is in doing what you are doing by copying existing objects into more objects I am curious as to the reasoning behind it.

 

Going with your strategy as it is:

 

When you do create extra objects you have to consider how you control that scenario. A simple command of 'Docmd Copyobject' does what it says on the tin so to speak...but..what happens if it doesnt, what happens if the object is already in place or if the user types invalid character into the textbox or too many perhaps. The program flow errors and so on.

 

You can try to perceive the obvious problems that might be encountered during a program flow trap for those and for those that you cannot ? ...well it errors out and you debug to track any causes etc.

 

The following code is a reshape of that which you have seen thus far. Now that I have seen your code context I can give you something back to chew over and maybe you might see things in there that are relevant to you. I have included in it error handling to sit within the on click event procedure of a control (command button) called command1

 

 



Private Sub Command1_Click()

    On Error GoTo Err_Command1_Click

    Dim dbsCurrent As DAO.Database

    Dim docLoop As DAO.Document

    Dim NewName As String, MyTemplate As String

    Dim Concatnewfrm As String

    Dim frm As Access.Form

    Dim i As Integer    'Counter variable

    Dim C As String     'current character being verified

    Dim Result As String

    Set dbsCurrent = CurrentDb()

    'simple validation check for a value entered

    If IsNull(Me!NewEquipList) Then

        DoCmd.Beep

        MsgBox "You need to enter then name for the new equipment list", vbExclamation, "System Message"

        Me!NewEquipList.SetFocus

        Exit Sub

    End If

    'lets have a look at that textbox. We wouldn't want any invalid unwanted characters in there!!

    'if the user has a free reign on typing into it so set it all to upper case for at least some

    'consistency and REMOVE ANY chars like square brackets typos and other rubbish!.

    'The acceptable chars which you can expand if you like are contained in the call to the

    'INSTR function below)

    s = UCase$(Me!NewEquipList)

    Result = ""

    'loop through and examine the contents of each character in the textbox

    ' and throw the result into the RESULT variable

    For i = 1 To Len(s)

        C = Mid$(s, i, 1)

        If InStr(1, "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ", C) > 0 Then

            Result = Result & C

        End If

    Next i

    'make sure any entry is within acceptable boundaries

    If Len(Result) = 0 Then

        DoCmd.Beep

        msg = "Please try again because the name entered" & vbCrLf

        msg = msg & "does not pass the validation test!"

        MsgBox msg, vbExclamation, "System Message"

        Exit Sub

    ElseIf Len(Result) > 64 Then    '<<< the limit for naming of objects

        DoCmd.Beep

        msg = "You have entered too many characters for naming purposes" & vbCrLf

        msg = msg & "Reduce the amount typed in and try again"

        MsgBox msg, vbExclamation, "System Message"

        Exit Sub

    End If

    MyTemplate = "frm_Standard2"

    NewName = Result

    Concatnewfrm = "frm_" & NewName

    'delete any pre-existing table of the same name

    With dbsCurrent

        With .Containers

            With !Tables

                For Each docLoop In .Documents

                    If docLoop.Name = NewName Then

                        DoCmd.DeleteObject acTable, NewName

                    End If

                Next docLoop

            End With

            'delete any pre-existing form of the same name

            With !Forms

                For Each docLoop In .Documents

                    If docLoop.Name = Concatnewfrm Then

                        DoCmd.DeleteObject acForm, Concatnewfrm

                    End If

                Next docLoop

            End With

        End With

    End With

    'now copy the objects turn off system warning (turns back on at the end)

    DoCmd.SetWarnings False

    DoCmd.CopyObject , NewName, acTable, "Standard"

    DoCmd.CopyObject , Concatnewfrm, acForm, MyTemplate

    DoCmd.OpenForm Concatnewfrm, acDesign, , , , acHidden

    'set a form object variable so that we can refer to

    'and change its properties

    Set frm = Forms(Concatnewfrm)

    With frm

        .RecordSource = "SELECT * FROM Standard"

    End With

    DoCmd.Close acForm, Concatnewfrm, acSaveYes

 

Exit_Command1_Click:

    'turn warnings back on and release memory

    DoCmd.SetWarnings True

    Set dbsCurrent = Nothing

    Set frm = Nothing

    Exit Sub

Err_Command1_Click:

    'if we errored out then bring back the essential stuff

    DoCmd.SetWarnings True

    DoCmd.Hourglass False

    DoCmd.Echo True

    MsgBox "Error " & Err.Number & " Err.Description", vbInformation, "System Message"

    Err.Clear

    Resume Exit_Command1_Click

End Sub