Add VBA code to MS Access userform using VBA

Question

I have a script that automatically generates a form. This form is only temporary and can be deleted after closing. To prevent having to do it manually, I'd like to add a button or a form_close() method in which there should be some code to delete the form (if this is possible at all).

Otherwise, I'd like to be able to prevent te pop-up forcing me to save the form and give it a name once I close it (shown below in Dutch).

I haven't been able to either disable this pop-up, or add VBA code using VBA code. Can anyone tell me wether this possible and maybe give an example?

why not using a simple InputBox() function ?

Patrick Honorez
– Patrick Honorez

2017-06-15 12:07:13 +00:00
Commented Jun 15, 2017 at 12:07 — Patrick Honorez
– Patrick Honorez, Commented Jun 15, 2017 at 12:07

Andre · Accepted Answer · 2017-06-15 10:46:52Z

1

You don't need VBA code in the form to do this, just call a public function from the OnClick event of the button.

' This is called from the "Close" button on the temp form
Public Function CloseWithoutSave(fName As String)
    DoCmd.Close acForm, fName, acSaveNo
End Function

' Demo
Public Sub CreateTestForm()

    Dim F As Form
    Dim btn As CommandButton

    Set F = Application.CreateForm()
    F.Caption = "This is a test form!"

    Set btn = CreateControl(F.Name, acCommandButton, Left:=100, Top:=100)
    btn.Caption = "Close me"
    btn.OnClick = "=CloseWithoutSave(""" & F.Name & """)"

    ' Open test form in form view
    DoCmd.Restore
    DoCmd.OpenForm F.Name, acNormal

End Sub

answered Jun 15, 2017 at 10:46

Andre

27.7k7 gold badges43 silver badges85 bronze badges

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

Comments

Erik A · Accepted Answer · 2017-06-15 10:35:59Z

1

You can't add modules to forms without saving them, as far as I know, and forms need to be in design view to have a module added to them (just like when you add them through the GUI).

To start from your starting point (you have an open unsaved form):

Public Sub AddModuleToOpenUnsavedForm(strModuleText As String)
    'Save the form name to a variable
    Dim strFormName As String
    strFormName = Screen.ActiveForm.Name
    'Save and close the form
    DoCmd.Save acForm, strFormName
    DoCmd.Close acForm, strFormName, acSaveYes
    'Open the form back up in desing view
    DoCmd.OpenForm strFormName, acDesign
    'Add a module to it
    Forms(strFormName).HasModule = True
    'Add code to the module
    Forms(strFormName).module.InsertText strModuleText
    'Close it again
    DoCmd.Save acForm, strFormName
    DoCmd.Close acForm, strFormName, acSaveYes
    'Open the form back up again
    DoCmd.OpenForm strFormName, acNormal
End Sub

Note that this does prompt a database-wide save. If you have any unsaved open objects, you will be prompted to save them.

Obviously, you can delete the form after using it, but you will have to do it actively instead of leaving it unsaved.

edited Jun 15, 2017 at 10:35

answered Jun 15, 2017 at 10:09

Erik A

32.8k13 gold badges50 silver badges75 bronze badges

2 Comments

Muxor Over a year ago

Thank you for your response. The problem is, is that I don't want to save the form and let the user delete it manually. The user doesn't get access to the navigation pane. The answer Andre provided worked though.

Erik A Over a year ago

Noted, my answer is mainly useful because it answers your question, and people searching can use it. You can add an on close -> delete form if you want, but Andre's solution is more simple if you don't want to actually use the close button or any other VBA.

Collectives™ on Stack Overflow

Add VBA code to MS Access userform using VBA

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related