Solved Set frm = Forms(FormName): Error 438

[EL_Gringo]

No matter how, I cannot get Set frm = Forms(FormName) to work. Though used in uncountable solutions, I'm stonewalled by Error 13, Type mismatch or Error 438, Object doesn't support this property or method. What works:

Private Sub Test_Click()
ListControlProps Me
End Sub

Public Sub ListControlProps(ByRef frm As Form)
Set CurrDB = CurrentDb()
For Each ctl In frm.Controls
...

Any other attempt to assign another form (i.e. not the current form using 'me') to a variable throws an error. For open forms (in normal or design view) and using the Forms-Collection:

Dim frm As Form 'Or Access.Form
Dim FormName As String
FormName = "MyExistingForm"
DoCmd.OpenForm FormName, acDesign, , , , acHidden
Set frm = Forms(FormName) 'Error 438

For closed forms and using the AllForms-Collection I tried:

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
Dim FormName As String
Dim frm As Form 'Or Access.Form

For Each obj In dbs.AllForms
If obj.IsLoaded = False Then
With obj
FormName = .Name
Debug.Print "Form Name: " & .Name
Set frm = Forms(FormName) 'Error 438
Set frm = dbs.AllForms(FormName) 'Error 13, Type mismatch
Set frm = obj 'Error 13, Type mismatch
Set frm = .Name 'Type mismatch at Debug
End With
End If
Next obj

What am I missing? Do I need to activate a special Reference for VBA?

I'm working with Access 2013 Professional Plus on Windows 10, all up to date.

 

[Ranman256]

i use this method all the time

dim frm as form
dim sFormName as string

sFormName= "fMyForm"
docmd.openform sFormName
set frm = forms(sFormName)

now,' fMyForm' MUST be open already. You cannot set it if its closed.

 

[Pat Hartman]

The problem may be that the form is not open.

I only do this from within a form. So If I want to use common code that needs to reference the current form, I use "Me" to pass the reference.

Call CommonCode(Me) ''' passes in a reference to the calling form.

Public Procedure CommonCode (frm As Form)

 

[MajP]

The error is you do not have enough commas after the docmd.openform. Window mode is the sixth argument not the fifth. You should be using intellisense. You are providing an acWindowmode argument (adhidden) to the acDataMode.
should look like

DoCmd.OpenForm formname,,,,,acHidden

So the error is on this line and not where you are showing it.

The problem may be that the form is not open

No, this would not give a 438 but a 2450 "cannot find referenced form"


You cannot get an Access.Form reference to a closed form. The allforms collection does not return an Access.Form it returns an Access.AccessObject

This therefore always gives a type mismatch

dim frm as Access.form
Set frm = dbs.AllForms(FormName) 'Error 13, Type mismatch

This works but will not give you what you want

dim frm as Access.AccessObject
Set frm = dbs.AllForms(FormName)

I will agree that the error is not descriptive of the real problem

VBA Object Doesn’t Support this Property or Method Error (Error 438)​

It should say the something like the value of the supplied argument is not supported.

 

[EL_Gringo]

i use this method all the time

dim frm as form
dim sFormName as string

sFormName= "fMyForm"
docmd.openform sFormName
set frm = forms(sFormName)

now,' fMyForm' MUST be open already. You cannot set it if its closed.

Thank you, Ranman. I know that the Forms-Collection only contains open forms. I tried with the form already open or opening by docmd.openform in the code. In both cases Error 438.

 

[EL_Gringo]

acHidden

I'm sorry, a transcription error on my side. The line actually reads
DoCmd.OpenForm strFormName, acDesign, , , , acHidden
Anyhow, I tried the code opening the form in normal mode. Still Error 438

 

[MajP]

Can you post the entire real code, instead if wasting people's time debugging something that is not even correctly transcribed? Copy and paste
Also show the correct location of the error. Use the formatting tool to block your code.

Public Sub blockcode
  Some code
End sub

 

[EL_Gringo]

The allforms collection does not return an Access.Form it returns an Access.AccessObject

Thank you. I was not aware of this.

It should say the something like the value of the supplied argument is not supported.

I came to this conclusion as well. Not that it helps to explain why it doesn't work for me, when it's a commonly used method.
BTW, I tested the code on another machine with a freshly installed version of Access (same version) - no luck. As well, I tested on a machine with Office 2019 Professional Plus - same error.
My last guess is, that there is a VBA-Reference which should be activated, but is not by default. What is activated:
- Visual Basic for Applications
- Microsoft Access 15.0 Object Library
- OLE Automation
- Microsoft Access 15.0 Access database engine Object Library

This area, however, is way over my head.

 

[EL_Gringo]

Can you post the entire real code, instead if wasting people's time debugging something that is not even correctly transcribed? Copy and paste
Also show the correct location of the error. Use the formatting tool to block your code.

Public Sub blockcode
  Some code
End sub

Private Sub LFP_Click()
On Error GoTo Proc_Err

Dim frm As Form
Dim FormName As String

    FormName = "ListPrptys"
    DoCmd.OpenForm FormName, acDesign, , , , acHidden
    Set frm = Forms(FormName)

Exit Sub
Proc_Err:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Call LogError(Err.Number, Err.Description, Me.Name, "LFP_Click")
    Resume Next
End Sub

The offending line is

Set frm = Forms(FormName)

I'm sorry to have annoyed you.

 

[MajP]

There is nothing wrong with that code. Ensure you compile the code to make sure that there is nothing else broken. That code works for me without the logerror since I cannot test that. Possibly the bad code is in the logerror method.
I would completely delete the lfp_Click procedure and retype it. Sometimes a line just will not compile if it has compiled before.

 

[The_Doc_Man]

Along the lines of having a compilation issue, I might suggest a Decompile/Recompile cycle.

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

Once you decompile and manually force a recompile, you eliminate the chance of being caught by a "partial compile" - which is what MajP is describing when he says a line won't compile if it has already been compiled. The Decompile sequence gets rid of partial compiles. Just as a little precaution, make a good backup copy before doing this, just in case something goes awry.

 

[EL_Gringo]

MajP and Doc_Man, thanks for staying with me.
I tried all of your suggestions, no luck (yes, I know about the /decompile - switch).
I then created a new database with two new forms A and B, typed the sub lfp_Click in A and voilá! No error.
Now, these two new forms work in the old database too, so the problem is within the old form with the sub lfp_Click (say, frmC). I deleted every line of code in C, cycled through decompile etc, retyped lfp_Click: Error.

To make it short, frmC had a Tab-Control. Once I deleted that, the problem was gone.

I'm most appreciative for your help, it put me on the right track!

 

[DickyP]

The other palliative measure with these errors, which occur even if your database compiles, is to fix a well documented problem that's been around since Access 97 where your references get confused. To deal with this add any new reference, compile, then delete the new reference and recompile. Always wondered why this problem still exists, and doesn't cure with compact and repair.

 

[gemma-the-husky]

Maybe the problem is having byref in your header. It should be byref by default, but maybe putting byref in the header causes something to go wrong.