Procedures (1 Viewer)

SkyCraw · Oct 10, 2013

Is it possible to create a form and vb procedure within that particular form (ie. an OnClose event)?

Code:

Function CreateForm()

Dim Form as Form

set Form  = CreateForm
frm.Caption = "MyNewForm"
frm.Recordsource = "Stuff"
'Do other stuff to form, save and close, open form again
[Forms]![MyNewForm].OnClose = 'insert sub and/or call to procedure (if possible, haven't been successful yet)

End Function

Any help would be greatly appreciated

CJ_London · Oct 10, 2013

Don't think you can. I think I have seen somewhere if you write your closeform code in a class module you can set it there

Cronk · Oct 10, 2013

A form can be exported as a text file and then re-imported and multiple instances of a form can be created.

I've never had need to use the former but search ms access export form as text file to get syntax.

I have used the latter in cases to enable a user half way through data entry could open another instance of the form in order to answer a phone query about another record. Search ms access create multiple instances of a form

JHB · Oct 11, 2013

SkyCraw said:
Is it possible to create a form and vb procedure within that particular form (ie. an OnClose event)?
Any help would be greatly appreciated

Create a new form, place a button on it and paste the below code into the click event for the button.

Code:

    Dim frm As Form, ctl As Control, mdl As Module
    Dim lngReturn As Long

    ' Create new form.
    Set frm = Application.CreateForm
    frm.Caption = "MyNewForm"
    'Be sure the table exist before you set it in
    'frm.RecordSource = "Stuff"
    'Return reference to form module.
    
    Set mdl = frm.Module
    ' Add event procedure.
    lngReturn = mdl.CreateEventProc("Click", "Form")
    ' Insert text into body of procedure.
    mdl.InsertLines lngReturn + 1, vbTab & "MsgBox ""Way cool, isn't?"""
    DoCmd.Save acForm, frm.Name
    DoCmd.OpenForm frm.Name

Click on the new form's record selector to show the message.

Cronk · Oct 11, 2013

It is indeed remarkable what can be achieved within Access. Thank you for the code. I doubt I'd ever need it because I have not in many years of using Access had any need to create a form in code, nor do I ever expect to (but I've been proven wrong before and will be again).

Roku · Oct 11, 2013

You can add controls to a form using the CreateControl method. The Access Help explains it reasonably well.
http://msdn.microsoft.com/en-us/library/office/aa221167(v=office.11).aspx
Once the form is set up, use the procedure explained by Cronk to add its module.

SkyCraw · Oct 11, 2013

JHB said:

Create a new form, place a button on it and paste the below code into the click event for the button.

Code:

    Dim frm As Form, ctl As Control, mdl As Module
    Dim lngReturn As Long

    ' Create new form.
    Set frm = Application.CreateForm
    frm.Caption = "MyNewForm"
    'Be sure the table exist before you set it in
    'frm.RecordSource = "Stuff"
    'Return reference to form module.
    
    Set mdl = frm.Module
    ' Add event procedure.
    lngReturn = mdl.CreateEventProc("Click", "Form")
    ' Insert text into body of procedure.
    mdl.InsertLines lngReturn + 1, vbTab & "MsgBox ""Way cool, isn't?"""
    DoCmd.Save acForm, frm.Name
    DoCmd.OpenForm frm.Name

Click on the new form's record selector to show the message.

This is just what I need, but I would like to take it further...

Is there any way to create a form and add an "OnClose" event to this newly-created form automatically?

I'm doing the following with a procedure (based on a button):

Create temp table (based upon make-table query)
Create form with the temp table as a record source
Add controls with control sources from record source
Save, close and reopen form in design view
Rename controls to same name as control source
Save, close and reopen form one last time (currently in datasheet view)
Here is where I would like to implement "OnClose" event (switching to design and adding it then switching back to DS perhaps?)
Once user finishes adding/editing values, closes the form (thus triggering the new OnClose event, which will add new values to another table)

Reason why I'm doing it this why is I'm trying to mimic an Excel sheet where the column names and # of columns are customizable (based upon earlier form where they add to/edit a query recordset on a continuous form, where they click the initial button I mentioned above)

Sorry for being a buzz-kill

SkyCraw · Oct 11, 2013

Forget the buzzkill... was able to tweak it for my application.

Code:

DoCmd.OpenForm "cttblDesignHours", acDesign
Set frm = Forms!cttbldesignhours
Set mdl = frm.Module
lReturn = mdl.CreateEventProc("Close", "Form")
mdl.InsertLines lReturn + 1, vbTab & "docmd.setwarnings false"
mdl.InsertLines lReturn + 2, vbTab & "'code to be entered here"
mdl.InsertLines lReturn + 3, vbTab & "'more possible code"
DoCmd.Save acForm, frm.Name
DoCmd.RunCommand acCmdDatasheetView

Thanks JHB!

ChrisO · Oct 11, 2013

If you need to go into design mode then it will not work in a compiled version of your database.

Chris.

Procedures (1 Viewer)

SkyCraw

Registered User.

CJ_London

Super Moderator

Cronk

Registered User.

JHB

Have been here a while

Cronk

Registered User.

Roku

MBCS CITP

SkyCraw

Registered User.

SkyCraw

Registered User.

ChrisO

Registered User.

Similar threads

Users who are viewing this thread