CODEWITHSUNDEEP
ms-accessvba
Rod
Rod

Reputation: 151

How to close a spawned VBA Access user form via a close button w/o using DoCmd

I have a main form named Calling. It has a button named Travel. That Travel button does:

Private Sub btnTravel_Click()
On Error GoTo btnTravel_Click_Err

    DoCmd.OpenForm "Travel", acNormal, "", "", acFormEdit
    'If you can find a cleaner way to open a form I will be thankful.

btnTravel_Click_Exit:
    Exit Sub

btnTravel_Click_Err:
    MsgBox Error$
    Resume btnTravel_Click_Exit

End Sub

The Travel info form performs correctly. That Travel form has a Close button with the code:

Private Sub bntClose_Click()
    Unload Me
End Sub

When pressed, the Close code generates "Run-time error '361': Can't load or unload this object.

Your help is much appreciated.

Upvotes: 1

Views: 419

Answers (2)

Not just for fun
Not just for fun

Reputation: 39

"Unolad me" just doesn't work in Access Form objects. Access wants you to use DoCmd.Open/DoCmd.Close but you are clever than that and can use Access Form Object as an object. Access names the Form Classes prefixed the name you give them with "Form_". You create a Form named "YourForm", Access create the Class "Form_YourForm". Use this class as a real object:

   'Declare a typed variable
   Dim f As Form_YourForm

   'Create the object
   Set f = New Form_YourForm  'This triggers the Open event

   'Use the object
   f.SetFocus
   f.Resize

   '... And eventually, dispose the object
   Set f = Nothing   
   'Remember <<Unload f>> won't work, neither you can use DoCmd.Close

Also, you can use Form_YourForm directly as an object variable because VBA Access just create "implicitly" this object out of the class Form_YourForm when you first use it or when you use DoCmd.Open. (Yes, it's a little bit confusing, but Access was create for users that didn't have necessarily programmer skills). However, you'll get a different instance of the Form_YourForm Class each time you use a variable object typed as any of the Form Class that exist in your project. This means you can open as many instances of a form as you want ...or while it fits your computer's memory. You can't acomplish it using DoCmd.Open.

The main "disadvantages" are that you have to handle the form object from another module. You can still use the "Forms" collection but since you don't know the given key you can't reach your form instance easily. Also, you can't close the form from its own code, but only disposing the typed variable (Set f=nothing).

Upvotes: 0

LiamH
LiamH

Reputation: 1502

You do not need the commas with the empty strings, nor do you need the acFormEdit as when you open the form you will be able to edit and add new records anyway.

If you leave this argument blank the form will open in the data mode set by the forms AllowEdits, AllowDeletions, AllowAdditions, and DataEntry permissions (in the form properties).

DoCmd.OpenForm "Travel", acNormal

As for the next sub routine, I would use docmd.close instead of unload.

Private Sub bntClose_Click()
  Me.Undo
  DoCmd.Close acForm, "Travel", acSaveNo
End Sub

The me.undo is optional, if you don't want to save, and if you want to save the form change the acSaveNo to acSaveYes.

EDIT:

I have just re-read your question and noticed in the title you want to do this without docmd.

I have had a think about this and docmd is the standard way of closing forms in access using VBA. I am not sure if you have inherited the unload from using VB, but I would stick to docmd.close when using access.

Upvotes: 2

Related Questions