Declaring and Setting Form Variable in Access VBA

Question

It seems so simple, but I can't seem to find the correct syntax. I am looking to create a form variable for an unopened form in my Access 2013 application so I can pass the form object to a series of methods rather than passing separate properties of the form.

Here's what I've been attempting, but I run into Run-time error '2465' "Microsoft Access can't find the field 'frmSuppliers' referenced in your expression.

Private Sub cmdSuppliers_Click()
    Dim frm As Form
    Set frm = Form("frmSuppliers")

    Debug.Print frm.Name

You have to open the form for it to be initialized and added to the forms collection of the DB — Sorceri
– Sorceri, Commented Jan 11, 2017 at 21:29

MoondogsMaDawg · Accepted Answer · 2017-10-26 23:38:13Z

5

Private Sub cmdSuppliers_Click()
    Dim frm As Form
    Set frm = Forms("frmSuppliers")

    Debug.Print frm.Name

You referenced the Forms collection but made it singular instead of plural.

edited Oct 26, 2017 at 23:38

answered Jan 11, 2017 at 21:27

MoondogsMaDawg

1,71413 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve W · Accepted Answer · 2017-01-11 21:47:48Z

3

Thank you Sorceri and Christopher. I seem to have been missing a compilation of the wrong reference to the forms collection and not opening the form prior to setting the variable. This code seems to do the trick:

Private Sub cmdSuppliers_Click()
    Dim frm As Form

    DoCmd.OpenForm "frmSuppliers", acNormal, , , , acHidden
    Set frm = Forms("frmSuppliers")

    Debug.Print frm.Name

    DoCmd.Close acForm, frm.Name
    Set frm = Nothing

End Sub

answered Jan 11, 2017 at 21:47

Steve W

4761 gold badge5 silver badges19 bronze badges

Collectives™ on Stack Overflow

Declaring and Setting Form Variable in Access VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related