Solved

How do I create an EventProcedure with a dynamic form

Posted on 2008-10-07
11
384 Views
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?

Thanks.
0
Comment
Question by:vlvawter
  • 5
  • 4
  • 2
11 Comments
 
LVL 57
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.

JimD.
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_ClickEventProc:

	Exit Function
 

Error_ClickEventProc:

	MsgBox Err & " :" & Err.Description

	ClickEventProc = False

	Resume Exit_ClickEventProc

End Function

Open in new window

0
 

Author Comment

by:vlvawter
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?
0
 
LVL 84
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.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 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.

JimD.
0
 

Author Comment

by:vlvawter
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 84
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.
0
 

Author Closing Comment

by:vlvawter
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.
0
 

Author Comment

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

Author Comment

by:vlvawter
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?
0
 
LVL 84
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).
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now