Referencing an unopened Form With a String reference

[txgeekgirl]

I have a string that contains the name of a form, something like “Update Requests 1”. It has to start as a string because the digit at the end will change based on user interaction. The digit could be up to 99 so a giant case statement is unmanageable.

I need to populate a variable of data type “form” with a reference to this form so that I can feed it to a function that requires a parameter of type “form”. The function call is something like

  Dummy = Fixform(myformname)    
 
    ' Where the function definition is
      Function Fixform(pform as form) as boolean

The best that I can do so far is get the form as an “AccessObject” data type with this:

                Dim obj as AccessObject
                Set obj = CurrentProject.AllForms(mystringvar)

At that point I can display the “name” property of the object, so I know that the string is populated properly. How do I use the string to create a variable of “form” data type?

This works (because the form is open:

[SIZE=3][FONT=Calibri]Dim myform as form[/FONT][/SIZE]
Myform = me.form
Dummy = Fixform(myform)

I need this to work:

Dim myString as string
Dim mynum as integer
 
Mynum = 1
Mystring = “Update Requests “ + TRIM(STR(mynum))
Dummy = Fixform(mystring)

Tried to set the string to the form reference and got an error that it couldn't reference a form that didn't exist - even though the form does exist using this:

[COLOR=#1f497d][COLOR=#1f497d]Set myform = Forms(mystring).form[/COLOR]
[/COLOR]

Thanks for the help.

 

[jdraw]

What exactly are you trying to do?
Can you open the correct form using this construct - using your Mystring variable with the correct name:

DoCmd.OpenForm MyString, acDesign, , , , acHidden ' whatever arguments you want

 

[txgeekgirl]

yes - the Docmd.Open does work.

I need to pass to my function a name of a form in form type. The name is a string because it could be 1 of any 99 at a given time.

 

[txgeekgirl]

Here’s another example of something that almost works:

[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Dim theform as Form[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim frm As AccessObject[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set frm = CurrentProject.AllForms(myForm)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]MsgBox frm.Name[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set theform = frm[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]dummy = FixForm(myform, Me.Flowchart)

I shows the message box with the correct form name in it, but bombs on the 5th line. I think this is because I’m trying to do an implicit type change by assigning from an AccessObject type of variable to a Form type of variable.

 

[jdraw]

I don't think Access knows which database you are talking about. Also, before a Form is Open, I think it is referred to as Document??

I have a proc (from years ago) where I was trying to find info for Queries, Forms, Reports, Tables. I have extracted the code to deal with Forms. In the original I was trying to find the Recordsource for each form and store the info in a table. It might give you some ideas for identifying the database and using Documents.
The code

Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim Cntnr As Container
    Dim doc As Document
    Dim qdf As QueryDef
    Dim frm As Form
    Dim rpt As Report
    Dim tdf As TableDef
    Dim strResp As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblObjDefs")
     With rst
'********************* SECTION NOTE ******************** _
         Collect the source for each form object _

        Set Cntnr = dbs.Containers("Forms")
        For Each doc In Cntnr.Documents
            .AddNew
                !objName = doc.name
                DoCmd.OpenForm doc.name, acDesign, , , , acHidden
                Set frm = Forms(doc.name)
                !objsource = frm.RecordSource
                DoCmd.Close acForm, doc.name
                !ObjType = "Form"
            .Update
        Next
  End With
 '************************ SECTION NOTE ***************** _
     Clean up the variables that were used to release the memory space.
    Set dbs = Nothing
    Set rst = Nothing

 

[txgeekgirl]

I just sent this to my boss... so far - this is the answer that makes the most sense.

 

[ChrisO]

Sub Test()
    Dim strName As String
    Dim intNum  As Integer
     
    intNum = 1
    strName = "Update Requests " & CStr(intNum)
    
    DoCmd.OpenForm strName
    
    MsgBox FixForm(Forms(strName))

End Sub


Function FixForm(frm As Form) As Boolean

    MsgBox frm.Name
    FixForm = True
    
End Function

That would imply that you have Forms named ‘Update Requests 1’ through to ‘Update Requests 99’ and that would imply that there is something wrong with the database.

Also, if you do want to go into design mode of the Form which opened, it will not work in a compiled version of the database.

Chris.

 

[txgeekgirl]

@Chris - The Update Requests 1-99 is intentional.

 

[ChrisO]

Well, why not tell us the intention; there may be other ways to do what you want.

Chris.

 

[txgeekgirl]

I am not sure what part of this he is building - but it's a way to build forms on the fly as part of a master form based off of the end users choices to questions. Based on their answers, the master form builds off of the other forms.

 

[ChrisO]

>>build forms on the fly<<

That sort of thing could be done but he should be aware that going into design mode is not really an option. A lot of time could be spent designing something which will not work in a compiled version simply because a compiled version can not go into design mode.

What is usually done is to have a template Form with all the Controls predefined but invisible. Then, based on OpenArgs as an argument, position and make visible only those Controls which are necessary.

That normally means that the Control Names and positions are held in a Table record. The OpenArgs argument is then used to open a recordset and position the Controls.
The Control Source of the Controls, and anything else which is needed, is also held in the recordset.

There are basically three things to remember.
1. The template Form, singular, is configured out of a Table recordset.
2. A new Form layout can be created at run-time.
3. It works even in a compiled version.

With some imagination, something similar can be found here:-
http://www.access-programmers.co.uk/forums/showthread.php?t=228040


Chris.

 

[txgeekgirl]

We ended up passing the string and then dealing with the form as a routine passing controls to a master temporary form. We found that there is a limit to the number of controls allowed on a form and just because you delete or flush - the behind the scenes control index does not flush.

 

[ChrisO]

If you haven’t already tried, what I think should be done now is to test the current code in an MDE or ACCDE version of the database.

Chris.

 

[txgeekgirl]

We constantly test code as we build. Especially when we have so much back coding.

 

[ChrisO]

Yes, but what I’m suggesting is testing the code in a compiled version of the database.

Some things work in an un-compiled version and fail in a compiled version. In the future you may want to issue the database in a compiled version. Compiled versions can not go into design mode and hence will fail if it is attempted.

You have not said anything about compiled versions so that is why I am mentioning it.

Chris.

 

[ChrisO]

There may be some confusion about my use of the term compiled versions.

All VBA needs to be compiled before it can run, no exceptions.

But, unlike an MDB file, an MDE file has had the VBA source code removed and it can not go into design mode.

So I’m talking about what you have not said…

From post #2
DoCmd.OpenForm MyString, acDesign, , , , acHidden ' whatever arguments you want

In post #3 you did not mention the acDesign mode. That will fail in an MDE or ACCDE file.

Again, the same line of code is in post #5 and in post #6 you say “I just sent this to my boss... so far - this is the answer that makes the most sense.”
Well, it only makes sense in an MDB or ACCDB file. It does not make sense in an MDE or ACCDE file because the Form can’t go into design mode and that code will fail.

So, by definition, if it makes sense then it hasn’t been tested properly because it does not make sense to have a failure.

Chris.