How do I create an EventProcedure with a dynamic form

Posted on 2008-10-07
Medium Priority
Last Modified: 2013-11-28
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?

Question by:vlvawter
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
LVL 58
ID: 22661644
<<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


Author Comment

ID: 22661853
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?
LVL 85
ID: 22661918
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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 22662485
<<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",frm.name)

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.


Author Comment

ID: 22662666
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.
LVL 85
ID: 22663297
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.

Author Closing Comment

ID: 31503924
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.

Author Comment

ID: 22663636
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.
LVL 85
ID: 22663858
I still don't understand why you'd need to build this dynamically ... but it's your data ...

Author Comment

ID: 22664160
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?
LVL 85
ID: 22664309
<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).

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question