How do I create an EventProcedure with a dynamic form

I have searched and searched, but have not found a solution.

I have created a dynamic form.  (Please, there is no need to ask why or it is not necessary.  It is needed.)

I have set the property OnClose = "[Event Procedure]".  When I look at the form in design mode when stopping the program, it is there.  Now, how do I access it.  How do I write code for it.

No, it cannot be opened in design view by the user.  It does need to be dynamic.  

How do I write the code for the OnClose event?

Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
<<D: Thanks for what you sent. I've been looking at other links with CreateEventProc. I do have a question. Your example and the ones I am finding onlne have CreateEventProc with controls on the form. I need an EventProc for the form itself, OnClose, not one of its classes.

Do you have any insight on that?>>

There is no difference. You just refer to the form itself in the CreateEventProc call rather then naming a control object. ie.

lngReturn = mdl.CreateEventProc("OnClose",

As I said, take a look at the module object and it's properties and methods.

And as LSM said, this is not a hot idea. Besides the design time issue and being unable to distribute as a MDE, your code will be un-compiled as well. You can call an undocumented switch to compile it and gain some performance back, but the switch being undocumented can be problematic at best.

As an aside, the need for most dynamic forms can be handled by:

1. Creating a form with an array of controls, which you hide/unhide as needed.

2. Using an object orientated design for the database schema. Not the best relationally, but better then creating a form on the fly in Access.

If this was some other language (ie. VFP), I would not hesitate to create a form on the fly, but Access brings too many issues to the table in doing this, so I would really think twice.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I have created a dynamic form.  (Please, there is no need to ask why or it is not necessary.  It is needed.)>>

  Hopefully you are creating the form itself from scratch each time.  If not, you'll run into a problem; Access has a hard limit of approx 750 controls being created over the live of the form.  Once you hit that limit, that's it.  Deleting controls and compacting does nothing to address it.

<<How do I write the code for the OnClose event?>>

  You use the module object.  See the attached code from the on-line help.

The following example creates a new form, adds a command button, and creates a Click event procedure for the command button:
Function ClickEventProc() As Boolean
	Dim frm As Form, ctl As Control, mdl As Module
	Dim lngReturn As Long
	On Error GoTo Error_ClickEventProc
	' Create new form.
	Set frm = CreateForm
	' Create command button on form.
	Set ctl = CreateControl(frm.Name, acCommandButton, , , , 1000, 1000)
	ctl.Caption = "Click here"
	' Return reference to form module.
	Set mdl = frm.Module
	' Add event procedure.
	lngReturn = mdl.CreateEventProc("Click", ctl.Name)
' Insert text into body of procedure.
	mdl.InsertLines lngReturn + 1, vbTab & "MsgBox ""Way cool!"""
	ClickEventProc = True
	Exit Function
	MsgBox Err & " :" & Err.Description
	ClickEventProc = False
	Resume Exit_ClickEventProc
End Function

Open in new window

vlvawterAuthor Commented:
JD:  Thanks for what you sent.  I've been looking at other links with CreateEventProc.  I do have a question.  Your example and the ones I am finding onlne have CreateEventProc with controls on the form.  I need an EventProc for the form itself, OnClose, not one of its classes.

I don't think I want to create or insert into a module.  Somehow I need to get inside the class.

Do you have any insight on that?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Even though you don't want to hear it, I'll post this for the benefit of those who may pull this up on a search:

This is a bad idea ... creating a simple form that displays something, even bound data, at runtime is one thing, but creating a form with code behind is an entirely different matter and can make the db unstable and very, very prone to corruption. The newsgroups are rife with postings regarding this matter, and in virtually every instance the "experts" involved in those postings advised against doing this. I'm not sure why MS ever even included these properties and methods ...

And if you need to deploy this as an .mde file then you're out of luck, as you cannot work with your objects in Design view.
vlvawterAuthor Commented:
I certainly appreciate the comments made.  The dynamic form is created and then deleted when called upon again.  The only event that I'm looking for is OnClose because I have to know when the form is closed.

If there is another way that I can tell a form is closed aside from that procedure, then I'll surely consider it.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What version of Access are you using? There are many ways to determine if a form is open or closed, if that's what you're doing.
vlvawterAuthor Commented:
Thanks, Jim.  By the way, is there a way to compile dynamically?  There is a property, IsCompiled.  I've search and cannot find anything.  Do you know any way to compile dynamically?  Also, a form is not handled like a control.  The syntax is
lngReturn = mdl.CreateEventProc("Close","Form")
Literally, the word Form in double-quotes.
vlvawterAuthor Commented:
I program in Access 2003 and Access 2007.  I know I can loop through Forms! and find open or closed forms.  The key, however, is that I have to know when the form is closed because I need update three other tables from the data entered in this form.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I still don't understand why you'd need to build this dynamically ... but it's your data ...
vlvawterAuthor Commented:
The code is being written for Catepillar.  It is a program than manages training.  I could easily create a crosstab query to compare two tables, but I couldn't edit it (I guess since it's a summary table).  So, I use one table, read the headers, and make the the headers in the new table.  Then I use a second table to create the fields (the headers from the table one is for the naming).  The data in both tables is very volatile.  Literally I can go from 5 headers to 100 headers.
So, I have a dynamic form in a datasheet format that will look like a crosstab query, but the changes actually occur in a make table that was created from the other two tables.  
After my form accepts changes to the make table and the user clicks to close the form, I'll capture that and take the changes from the make table to adjust the original two tables.

If there is a simplier way to create a form that looks like crosstab, accepts the data, and update the data, I simply don't have the experience to know how.
I appreciate the concern you expressed, but the code to write the form was very short since it was a loop through field names.  Only one event procedure needed to be captured, OnClose.  The user does not want an mde.  Can unstability occur with only one event being written and captured?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<Can unstability occur with only one event being written and captured?>

You bet ... anytime you add objects to a database, you run the risk of corruption and instability. The real issue, to me, would be mucking around in the VBA arena (i.e. adding code to a running application) ... VBA container corruption is often catastrophic and extremely difficult to recover from, if it even can be recovered. IMO, as Jim mentioned earlier, you'd be much, much better off adding 100 or 200 controls to your form and manipulating those at runtime rather than trying to create a form on the fly.

Or you could use a 3rd party grid control (the Janus grid works very well in Access, although I'm not sure about 2007), which is much more configurable. Yes it does have some deployment requirements but for the most part it's pretty straight forward (i.e. deploy the control and a couple of support files, register them and you're done).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.