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
  • 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.
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

850 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