Solved linking text box with after_update procedure using VBA. (1 Viewer)

[Kingz]

Hi,

I've got a form where I dynamically create a text field using
Set ctl= CreateControl(...,acTextbox,...)

Now, in the form, I have written an after update function, which I need to be linked somehow to the created control. How do I do that with VBA.
I know normally, one would go on the text box to the properties and set it manually, but how can I do that with VBA code?

Oh, and if it helps, the text box is set as a date, so if I enter the text box a calendar opens straight away..I would like the event after the date is set.. Isn't that after update of the text box?

By the way, I understand it's not advisable to do such things dynamically, but I need to.

 

[theDBguy]

Try something like:

Me.ControlName.AfterUpdate = "[Event Procedure]"

 

[Pat Hartman]

Are you building a tool that build forms for yourself because I would not recommend this functionality at all for an app intended for a user.

You don't ever "need" to do this. Business applications do not create new tables/columns/objects on the fly. Tools you build for yourself might. Perhaps you might want to explain the problem you are trying to solve and we can offer a different solution.

 

[Kingz]

Try something like: Me.ControlName.OnAfterUpdate = "[Event Procedure]"

Yeah, I tried that, but I got an error..
Run-time error 438 Object doesn't support this property or method

 

[Kingz]

Are you building a tool that build forms for yourself because I would not recommend this functionality at all for an app intended for a user.

You don't ever "need" to do this. Business applications do not create new tables/columns/objects on the fly. Tools you build for yourself might. Perhaps you might want to explain the problem you are trying to solve and we can offer a different solution.

Please! That's why I write my " by the way" at the end. I hear you

 

[MajP]

Please! That's why I write my " by the way" at the end. I hear you

I have never seen a legitimate reason to create a dynamic control in Access. Not saying, but 9 times out of 10 it is due to a poor design. If you need a control then have a group of hidden controls. Then simply move, show, and format.

If you tell me there is a reason you cannot have hidden controls, and need dynamic created controls, I will be very interested in the reason. But, I strongly doubt you have a compelling reason.

 

[Kingz]

I have never seen a legitimate reason to create a dynamic control in Access. Not saying, but 9 times out of 10 it is due to a poor design. If you need a control then have a group of hidden controls. Then simply move, show, and format.

If you tell me there is a reason you cannot have hidden controls, and need dynamic created controls, I will be very interested in the reason. But, I strongly doubt you have a compelling reason.

We did need it.. The application is nearly finished now, it just needs one or two added functionalities like this one.
To briefly explain the reasoning: imagine you have 5 different rooms, say and each room has 20 different components which could be filled by text boxes or combo boxes. Now the only thing that's fixed are the 5 rooms, aside from that the components of the rooms filling up to 5 tabs per room are stored in tables, which can change. So the composition of the forms are dynamically created with the contents of the table. Even the combo boxes are filled via the content of a table. These contents will change, hence the dynamism.

 

[Edgar_]

Read this post for an example to create dynamic controls on the fly:

Solved - Creating combo boxes on the fly

Hi, Been trying to find some code that allows for the creation of a combo box for each value that an SQL tables fields. Lets say 10 fields name in the table would yield 10 combo boxes setup one above each other. It is for mapping fields for import. I have the code to extract the fields names...

www.access-programmers.co.uk

If you have 5 different rooms:
tbRooms: RoomID, RoomNumber (~5 records)
If you have 20 different components for each room, that's very likely two additional tables:
tbComponents: ComponentID, ComponentName (<100 records)
tbRoom_Components: ID, RoomID, ComponentID, Notes (~100 records)

A continuous form would be ideal for that setup. You would avoid creating controls on the fly, which will eventually render your form unusable.

Now the only thing that's fixed are the 5 rooms, aside from that the components of the rooms filling up to 5 tabs per room are stored in tables, which can change. So the composition of the forms are dynamically created with the contents of the table. Even the combo boxes are filled via the content of a table. These contents will change, hence the dynamism.

This sounds like you're using a table for each room. If you can post a sample file or a screenshot of your db schema, that might make things clearer.

 

[Kingz]

Yeah, I tried that, but I got an error..
Run-time error 438 Object doesn't support this property or meth

Try something like:

Me.ControlName.AfterUpdate = "[Event Procedure]"

I got it..

It's ctl.afterupdate = "[Event Procedure]"

Thanks!

 

[xavier.batlle]

If I'm not mistaken, creating dynamic controls doesn't work on MDE/ACCDE databases, so I hope you don't have to convert your database.

 

[arnelgp]

If I'm not mistaken, creating dynamic controls doesn't work on MDE/ACCDE databases, so I hope you don't have to convert your database.

i think that is what he is doing? opening the form in Design view and creating the control on the fly.
of course you cannot go to design view when the db is compiled as accde.

 

[xavier.batlle]

i think that is what he is doing? opening the form in Design view and creating the control on the fly.
of course you cannot go to design view when the db is compiled as accde.

Yes, I agree with you. My post is only a warning.

 

[MajP]

We did need it.. The application is nearly finished now, it just needs one or two added functionalities like this one.
To briefly explain the reasoning: imagine you have 5 different rooms, say and each room has 20 different components which could be filled by text boxes or combo boxes. Now the only thing that's fixed are the 5 rooms, aside from that the components of the rooms filling up to 5 tabs per room are stored in tables, which can change. So the composition of the forms are dynamically created with the contents of the table. Even the combo boxes are filled via the content of a table. These contents will change, hence the dynamism.

Like I said, absolutely no reason for creating dynamic controls when this can be done, if it had to, with a handful of hidden controls.
However, the design itself sounds like a Rube Goldberg machine and could be radically simplified.

 

[GPGeorge]

This discussion reminds me of my very first attempt at creating controls on the fly, early in my career when I knew enough to be dangerous and not enough to realize how dangerous that level of knowledge could be.

I created a rube goldberge menu system that recreated command buttons for the menu options each time the accdb was opened. I published an article on it. And then I gradually realized how clumsy and inefficient the whole process was going to be. The main problem, of course, is the lifetime limit on the number of controls on a form. Each time a new control is added, it counts against that limit. I want to say it's 756 or something in that neighborhood 754 . Perhaps as an exercise in minutia, someone could test it out. The limit may actually have increased in more recent versions of Access. It is, nonetheless, finite.

In the original question, there is only a reference to adding new controls to a form, not to removing any that become superfluous in later processing. Okay, so assuming that obsolete text controls are deleted and new ones added, the lifetime limit is not changed by deleting controls. Even if all but one control is deleted, attempting to add another control that exceeds the limit will fail.

Regardless of any other consideration about the utility of constantly changing an interface, this factor should be recognized as a show-stopper.

 

[MajP]

Here is one of many examples of pseudo creating controls.
This first one allows the user to build a restaurant floor plan with different types of tables and persist the design. There is about 200 "tables" you can position, and choose type of table. None are created dynamically all are just hidden and stacked up. Basically you make the next one in the stack visible and configure the image.

Class to move about 20 pictures

Hello, I'm building a kind of floor plan. For this I placed 50 image objects on a form. I am able to display certain images (for example a table for 4 persons or a table for 8 persons) on a selected image object. What I want is that these image objects can be moved with MouseDown/MouseMove...

www.access-programmers.co.uk

Same idea to locate Tasks onto different ship floor plans.

Solved - User made command button that can be moved with mouse?

haha! We got plenty of liferafts! Thanks MajP, is working good. Attached is idea of the jobs pane. I think when a new dot is moved to the plan/deck then it would ideally open up a new record/job for this new dot that has been added, this would highlight as a new job in the jobs detail pane...

www.access-programmers.co.uk

Although these use simple controls and not data bound controls, clearly you can locate and set the properties once visible. This then works in ACCDE and will not crash due to hitting the lifetime limit of controls. Point is you can do this with hidden controls and not created controls.

 

[Pat Hartman]

We did need it..

You absolutely did not. What you needed was a properly normalized schema. What you have is actually a very common problem that occurs in many different types of applications. For example, in a manufacturing application, parts have attributes but different types of parts have different attributes. So, the list of potential attributes can become quite large and possibly even exceed the 254 column count for a table/query. So, the answer is not to create a table with bunches of columns to accommodate any potential attribute. It is not to split the problem up and create a separate table for each part type so the attributes are more controlled. It is to create a table that defines attributes, a second table that relates attributes to each part type and a third table to store the values for each specific part. This is ALWAYS the answer and it is a common and well known pattern.

@MajP gave you an example you could use when you have a display problem which my problem described above and your problem do not have.

 

[isladogs]

Each time a new control is added, it counts against that limit. I want to say it's 756 or something in that neighborhood 754 . Perhaps as an exercise in minutia, someone could test it out. The limit may actually have increased in more recent versions of Access. It is, nonetheless, finite.

It is certainly now much higher but still finite....
Last time I tested, the limit was 1038 (32-bit Access) & 1167 (64-bit)

Access Specification issues

Microsoft publishes a list of Access specifications and limits. Most of the information is correct. This article discusses some specification items that are misleading, out-of-date or inaccurate

www.isladogs.co.uk

 

[Kingz]

Ok, I'm starting to see understand your points of view. I've just read up on con

Like I said, absolutely no reason for creating dynamic controls when this can be done, if it had to, with a handful of hidden controls.
However, the design itself sounds like a Rube Goldberg machine and could be radically simplified.

Ok, I'm starting to see understand your points of view. I've just read up on continuous forms.. This could indeed be a clean solution to my task. I think I'll make a new thread to discuss a bit more thoroughly. Thanks dude