Dynamic/reusable forms

[Imb]

New to this forum, triggered by a thread, but alas I cannot find that thread. So I will sketch my situation.

Since long I work on generalization of code to make programs more powerful and robust. Code must be as structured as normalized data.
This has resulted in automation of many of the processes that play a role in data-handling, condensed in general routines.
For instance, the looping through recordsets. In my case I use a function "Active_set" that includes all what is necessary to run the loop, including all necessary error handling. And, because it is a loop, includes a number of progress-meters to monitor the proces, if you want that.
Another example is the complete automatic generation of an "Overview form", a continuous form for any table/recordset: no Datasheets anymore!

Another goal was that a Control should behave always the same, independant on which form or how it is used. This is, among others, prominent with controls that are filled by hand or by code, but need an Afterupdate treatment. Alas, there is no Afterupdate event after automatic filling the control.
In fact, "independant on which form" means that the form is not a substantial part of data-processing, but just a carrier of controls.

But how works Access? You define Fields with a certain datatype. These fields are used to define controls on a form, but only with the technical description of the control (type, maximum length, ...), not with the conditions of Realworld-controls, including their variability and boundary conditions. All that kind of information is hidden all over the database in Beforeupdate and Afterupdate events, in the BackEnd for Referential Integrity, etc. It makes the form static.
You can revert this process. You can define a Control, a RealWorld control, that completely describes the dynamic behaviour of that control.
That can be collected in a metadata FE table. One of the fields in this metadata table is where the value is technically stored in what type.
The controltype of these RealWorld controls may be different form the fieldtype of the corresponding field. Think of BankAccountNumbers, SSN, or worldwide Telephonenumers for validity-check. Incomplete historical dates that can be sorted. A contiguous numeric list that must stay contiguous.
But also the FK-control: this is the start of a complete dynamic walk through your application.

As I said, the form is just a carrier of controls, but can join controls together, both technically and (cosmetic) optical.
But that also means that you can tune such a form at will, depending of what you want to see. They are completely tunable to make a Singlerecord form, a Selection form with all possible selection to construct a Where-clause, or define any ordening. Dynamic Calenders, Timetables, Colour selection, Internet grabbing, ...
The tuning of such a form is by standard routines in general modules.

On this moment this is not a dream any more. I realised some 100+ applications, from easy to very complex, all with the same form for continuous records, and one for all other displays. Maintenance of all these application is no problem at all, since the definition of the RealWorld control is in FE tables, and all code in (accessable) general modules.

Now finally my question. I am looking for developpers that work in the same area, to share ideas, and to improve the whole process, or articles that cover the same approach.


Imb.

 

[The_Doc_Man]

I used an approach a little bit different from yours but with the idea of improving efficiency.

I built a "template" form that had a lot of controls and events already partly implemented, lacking only the detailed controls to be bound to a recordset. But commands like CLOSE, UPDATE, DELETE, CREATE, SAVE, UNDO, plus a HELP and a FILE REPORT button. Then I had events that, for bound forms, would verify that you actually used the SAVE button, for example, rather than trying to close a form and use an implied SAVE action. I had special form control modules that allowed me to apply "state" rules - like "Clean" "Dirty" "New" "Error" and a few other states. By making one template form and just copying it and then customizing the copy, I estimate I saved anywhere from 40% to 60% of the work that I would have had to do if I did each form individually.

I stayed away from any attempt to automate data handling based on what the data represented. I've watched what happens with products like PEOPLESOFT that use "data driven" programming. (See also project "DIHMRS".) The problem with those data-driven things is that as soon as you decide what data you have, someone says, "Oh, by the way, that particular element requires special handling due to regulations that differ from one nation to another."

We had a system at one place where I worked that used data-driven techniques. But we ran into the problem of "special cases" so many times that our library of classes was huge. We did oil-and-gas pipelines. We had 24 customers that wanted that feature. By the time the 24th system rolled out the door, we had 25 methods... 1 for each of the 24 customers and the 1 we originally envisioned. So data-driven methods CAN be a bit difficult to manage.

Now, let me add a comment of a different type here. I'm sure you put in a lot of work on this. Many of us have done something like you describe, to one degree or another. If you find like-minded folks to join you in your project, well and good. If all you want to do is talk technically about approaches, we're all for it. Just remember, don't try to actually SELL anything here because we don't allow that. Our site owner must approve any sales advertising.

 

[Imb]

I used an approach a little bit different from yours but with the idea of improving efficiency.

I built a "template" form that had a lot of controls and events already partly implemented, lacking only the detailed controls to be bound to a recordset. But commands like CLOSE, UPDATE, DELETE, CREATE, SAVE, UNDO, plus a HELP and a FILE REPORT button. Then I had events that, for bound forms, would verify that you actually used the SAVE button, for example, rather than trying to close a form and use an implied SAVE action. I had special form control modules that allowed me to apply "state" rules - like "Clean" "Dirty" "New" "Error" and a few other states. By making one template form and just copying it and then customizing the copy, I estimate I saved anywhere from 40% to 60% of the work that I would have had to do if I did each form individually.

I am not suprised that it did not work as you would have expected.
What I understand,is that you use a template form, and then "reform" a copy of the form to a new form. And continu to work in the old classical way.

I stayed away from any attempt to automate data handling based on what the data represented. I've watched what happens with products like PEOPLESOFT that use "data driven" programming. (See also project "DIHMRS".) The problem with those data-driven things is that as soon as you decide what data you have, someone says, "Oh, by the way, that particular element requires special handling due to regulations that differ from one nation to another."

Automating data handling is something else than automating processes for data handling.
I have an pretty large application to describe the history of a middlesized town. It contains describes the people (including genealogy) but also all kind of "objects", like buildings, streets, organizations, industry, sporting, you name is. Including all inter-relations.
Most of the data is incomplete, many times originating from different sources. So it can happen that two, or three, persons seem to be the same person. I have a standard routine that can combine the duplicates, after validation to one person again, including all relations to other entities in the database. A standard routine means that is build-in in any application, for every entity. That makes it powerful.

We had a system at one place where I worked that used data-driven techniques. But we ran into the problem of "special cases" so many times that our library of classes was huge. We did oil-and-gas pipelines. We had 24 customers that wanted that feature. By the time the 24th system rolled out the door, we had 25 methods... 1 for each of the 24 customers and the 1 we originally envisioned. So data-driven methods CAN be a bit difficult to manage.

Again, I suppose you took the non-flexible road that is build in standard Access.
Let us look at a field to store a telephonenumber, stored in a Text-field. Everywhere where you have to duplicate the checks for a telephonenumber.
I don't work with fields, I work with RealWorld controls, placed somewhere on any (anonymous) form. In the description of the RealWorld control you even can see in which field the telephonenumber will be stored, but that is a minor thing. More interesting is what you want to do in the Real World with this telephonenumber. So as soon as you enter a control on a form, the program knows the description of the RealWorld control, and knows the module where to find all information on telephonenumbers. This telephone module is general to all applications, and can be easily extended to add functionality.

Now, let me add a comment of a different type here. I'm sure you put in a lot of work on this. Many of us have done something like you describe, to one degree or another. If you find like-minded folks to join you in your project, well and good. If all you want to do is talk technically about approaches, we're all for it. Just remember, don't try to actually SELL anything here because we don't allow that. Our site owner must approve any sales advertising.

I know. My goal is to understand and improve.


Imb.

 

[The_Doc_Man]

I am not suprised that it did not work as you would have expected.

Ah, but for the case I was describing, it DID work EXACTLY as I expected. My comments about data-driven programming were another project. The problem with data driven programs is that to make them work economically, you have to have uniformity. Something I learned a LONG time ago is "One size DOES NOT fit all." The world is full of customers like roving dogs that hike their legs on every tree and fire hydrant so that THEIR smell (and therefore, their territory) is marked. And every one of them has just a little bit of a different stink.

What I understand,is that you use a template form, and then "reform" a copy of the form to a new form. And continu to work in the old classical way.

Yes, but for 40% to 60% less effort, since all of the common procedural infrastructure was partly or completely handled by the template. The difference between 40% and 60% was how many bound controls were needed. Simple forms? Saved 60%. Really busy forms? Saved 40%.

Again, I suppose you took the non-flexible road that is build in standard Access.

Nope, in the specific case I mentioned that prompted your particular response (our pipeline leak detection algorithm), we used PDP-11 Assembly Language. Don't think you can get more flexible than that.

The problem is ALWAYS that the customer is always right (even when he's a flaming idiot.) One reason our PEOPLESOFT project fell apart is because the military folks want things THEIR way no matter what your best business people said was logically correct. (Not the only reason.) What ran up the price tag for the U.S. Navy project was (a) customization / retrofit of the "data driven" stuff for just about EVERY data type in their repertoire, (b) their claim that data location was flexible at run-time, and (c) the dynamic nature of data-driven software often leads to datatype interpretation at run-time (as opposed to hard-compiled code) and that adds a layer of execution overhead - for which the net result of that dynamic data resolution was a machine meant to process 24 hours of business but that never could complete that processing in less than 28 hours. And we ran that stuff on dedicated UNIX mainframe-class equipment. Still was too much because of the dynamic nature of deciding what to do when and where.

 

[Imb]

You are trying to make operating on my payroll table identical to operating on my inventory table. Good luck with that.

Hi Pat,
Thank you for your answer. Quite long, and it will take some time to consume all your comments.

My first reaction was that you needed to get rid of your frustrations from how is was in earlier days. But in a second thought you are right from your point, but it missing my point.

First a question. Have you ever build over 100 Access-applications and manage these applications at the same time?
Your arguments seem valid for me for a few stand-alone applications.

Now let us try to see it from my side.
I quoted you last remark, because that is a nice starting point.

Yes, from a data point of view, I operate your payroll table identical to your inventory table.
They are both tables containing records, build up with an ID, a couple of other fields, and - occasionaly - some FK's.
The FK's refer to other tables containing records, build up with an ID, a couple of other fields, and - occasionaly - some FK's.
So, the underlying processes are just the same. But the behaviour of the fields is not the same!
A couple of the differences of the fields are layed down in the field definitions, but that is only how - technically - the data is stored.
All othere user-differences, that is how the user interprets the properties of a RealWorld control, are defined as events in the forms (and probably on some other places).
My point now is, you can define these user-differences in metadata-table. Then all these user-differences between controls do not need any more be stored in forms. Forms reduce themself to carrier of controls, and can be made anonymous. A form then is just one big variable that can be tuned in countless variations. The definition of any of the variations is standarized code that can be shared over all applications.

Because of this approach - one form and variable code - you can realize almost anything. It is no problem to change the active form to a complete different appearance. Even show a different overview from a complete different application.
In the way I use this concept, many standard Access utilities are not used anymore, such as the Ribbon, listboxes, splitted forms, the main form-subform systematic, and many other.
I agree, it is completely different from what you are used to. But what you gain is that you can make ad hoc a new "form" to make anything to make it easier for the user.


Imb.


Imb.

 

[Imb]

Yes, but for 40% to 60% less effort, since all of the common procedural infrastructure was partly or completely handled by the template. The difference between 40% and 60% was how many bound controls were needed. Simple forms? Saved 60%. Really busy forms? Saved 40%.

Hi The_Doc_Man,

You can also read my answer to Pat Hartman.
The way the different fields (controls) must be handled isd in your way: By the template.

My point is, bring it to a metadata-table, And free the form from any user-interpretation.
In that case a "form" is a "template" tuned by a piece of sharable code, mostly generalized to building blocks.
The form becomes just a carrier of controls, that can result in countless representations for almost any purpose.

As an example an genealogical family. Clicking on any person makes that person the central one.

Or an actual internet grabbing of actual valutas.

Imb.

 

[The_Doc_Man]

In that case a "form" is a "template" tuned by a piece of sharable code, mostly generalized to building blocks.
The form becomes just a carrier of controls, that can result in countless representations for almost any purpose.

The amount of time to be spent in generalizing the infrastructure has to be balanced against the amount of time to be spent in delivering a customer's finished, working product that also meets his specifications. Out of curiosity... in your many delivered systems, how many of them were for a government entity or agency? (And for which governments?) If you try this approach with the U.S. Navy you will find that they actually DO believe that one size fits all. But it won't be the size that you offered.

 

[Uncle Gizmo]

Another goal was that a Control should behave always the same, independent on which form or how it is used.

With the aid of ChatGPT I built some code for managing a standard set of command buttons for navigating through the records on a form or even a subform displayed on a main form... In the video below I demonstrate the use of the buttons on a main form....

It's an interesting demo because there's no code in the form for the buttons there's not even a code stub for the buttons!

Everything is managed through the agency of a factory module & navigation class module.

You can see more in this video:-

Next Previous Quit - Nifty Access​

 

[AHeyne]

You can define a Control, a RealWorld control, that completely describes the dynamic behaviour of that control.
That can be collected in a metadata FE table.

So your real-world controls are virtual controls whose properties and behavior you define in tables and implement in Access controls when loading a form, for example?

Or have I misunderstood you? Can you explain your idea in a little more detail?

 

[Imb]

So your real-world controls are virtual controls whose properties and behavior you define in tables and implement in Access controls when loading a form, for example?

Or have I misunderstood you? Can you explain your idea in a little more detail?

Hi AHeyne,

I am very happy with your question.
To begin with, I am not inventing something new, but look at the things in a different way.

When you use an Access-application, you normally see a Form with Controls, e.g. a Postalcode control.
But what the user/developper expects is far more than just that control. All those expectations together I have named a RealWorld control.
It is not an object, it is the complete description how the user wants to "use" that control.
Yes, all is already defined: stored and retrieved in the right Field, all BeforeUpdates are dedfined. all AfterUpdates are defined, the typography or masks are defined, editing or not, hidden or not. Most of these proporties are defined in the Form, some in the data-definition of the Field, and some (think of Referential Integrity, Keys) even in the BE.

It is important to realise the difference between the RealWorld control, and its use as a Control on a Form. Perhaps there is a far better name to describe the RealWorld control.

As many of the RealWorld control properties are layed down in the Form, the Form becomes "static", not sharable, especially not from application to application.

I skip for the moment how the metadata tables for the RealWorld controls are filled.
Starting point is the Item (or Entity), with a short but self-explaning name. An Item is stored in an Item_tbl, that has always an Item_Id as record-indentifier (Autonumber). With this simple definition you can already automate basic processes to a far extent, with just the Item as parameter.

When a form is opened (I could better say The Form, with a nickname for identifiction) the Open event of the form calls the <nickname>_Open routine. The form itself has a number of hidden controls, systematically named.
In the Open routine controls are placed where wanted and made visible. The controls that will represent a RealWorld-control get the Id of the metadata-table in the Tag-property, and are thus "bound".
Every time a bound control is entered, then the corresponding metadata-record is opened, so all information about the RealWorld control is available. During the editing process of a control you are already warned for key-violations, exceeding limits, syntax errors, ...
Saving is no problem, since the current metadata-record is bound, and "knows" where to store the data. It only takes a one-liner to do the actual storage.

So a Form is "The" form with a couple of definition routines, among others the Open routine. These can be placed in the modules of the application itself, or in a shared library database for use over all applications (ever dreamed of sharable forms?).


In time, other questions of how things work, can follow.
Or more detail, if this "little more detail" seems too little.


Imb.

 

[theDBguy]

@Imb I could be wrong but my understanding of what you're describing is you have a "generic" form that can be used for any situation. If so, I am wondering how you are managing to do that. For example, if the situation requires the use of two cascading comboboxes, can this generic handle that? What about another situation that requires three or four of them?

You shown us some images of your forms in action. Would you mind showing us the Design View of your generic form? Assuming you pre populated it with a lot of controls, how many did you use? How did you decide on that amount?

 

[The_Doc_Man]

First and foremost, if you have the imagination and some time on your hands, you can make a form do almost anything. For example, I once made a form that symbolically simulated the orbits of several planets around a central sun by moving small controls around a big detail area. It's a long story and at that time, I actually DID have too much time on my hands, helping my mother through her final days and going home to take my mind off of things. But the point there is that you have to write a LOT of complex code to be so generic and yet so flexible. Which means that your form full of "RealWorld" controls is carrying a LOT of baggage. Somewhere along the line you will have to modify something because the real world impinged on your creation and showed you where in some obscure way it was incomplete. Maintenance in the presence of all of that implied baggage would seem to be a nightmare. And yet if you claim that such maintenance doesn't happen, THAT is where I begin to doubt, because the real world is not a smooth, blank canvas. It is rough and rocky, with many features over which one could easily trip. (Speaking figuratively, of course.) There is also the limit of lines of code in a module which would force a lot of "divide and conquer" approaches. The number of "decision points" in that kind of code would be tremendous.

I'll say this: I hope that nothing breaks on you, though part of me says that it is inevitable.

 

[Imb]

Hi theDBguy,

I can "proudly" say that I don't use comboboxes nor cascading comboboxes.

The underlying data decribes always the relation between the "cascading elements".
Instead I define an sql-string that joins all these relations.
When I open "The" form I first open an (generic!) Search form where the user can make his selections on any field in the sql-string. The result with the composed WHERE clause is presented in a (generic) Overview form, defined as Selection form.
A Selection form returns one ID of a certain Item, or none.
A Selection form can even be extended, using any FK, to gain more information from other related tables, to make a better choice.

The number of controls is indeed limited. When to many records are selected (not relevant however for continuous records) I ask the user to make a better preselection, because it is a waste of time that he must "manually" work through that bunch of records. Let computers do that. A alternative that I also use, is to split lengthy values in multicolumns.

Don't look at the Design View. It is boring dull.

Imb.

 

[DHookom]

I have read Imb’s posts promoting this “world” for years with very little value as an answer to any OP question. Perhaps this forum is more appropriate. I would be more interested in the data and table structures behind the functionality. I expect every one of us have stored some design/logic in tables of our projects. This allows users to customize interface and functionality without paying more contract hours.

Show me the beef (ERD).

 

[theDBguy]

@Imb Thanks for indulging my curiosity. As I thought, your generic form is pre populated with some controls. I was wondering how you decided how much of each control to put on your generic form. For example, I see a few Checkboxes, is that enough for "all" situations?

 

[Imb]

First and foremost, if you have the imagination and some time on your hands, you can make a form do almost anything. For example, I once made a form that symbolically simulated the orbits of several planets around a central sun by moving small controls around a big detail area. It's a long story and at that time, I actually DID have too much time on my hands, helping my mother through her final days and going home to take my mind off of things. But the point there is that you have to write a LOT of complex code to be so generic and yet so flexible. Which means that your form full of "RealWorld" controls is carrying a LOT of baggage. Somewhere along the line you will have to modify something because the real world impinged on your creation and showed you where in some obscure way it was incomplete. Maintenance in the presence of all of that implied baggage would seem to be a nightmare. And yet if you claim that such maintenance doesn't happen, THAT is where I begin to doubt, because the real world is not a smooth, blank canvas. It is rough and rocky, with many features over which one could easily trip. (Speaking figuratively, of course.) There is also the limit of lines of code in a module which would force a lot of "divide and conquer" approaches. The number of "decision points" in that kind of code would be tremendous.

I'll say this: I hope that nothing breaks on you, though part of me says that it is inevitable.

Hi The_Doc_Man.

Well, the "RealWorld" I am referring to, is the "RealWorld" of that control. And absolutely not any "RealWorld". Therefore you have to join the Disney-world.

That the "RealWorld" of the user-control can change is trivial. Exactly for that, it is a burden to use standard Access form approach.
In the dynamical way: today asked, tomorrow ready (well perhaps the day after tomorrow).

Was you question really a serious question?


Imb.

 

[The_Doc_Man]

Was you question really a serious question?

At least partly serious. It has been my experience that when theory collides with reality, it isn't always pretty. In a development environment that has finite capacity, headaches seem inevitable. Therefore, I'm questioning how many times you have had to modify your magnum opus?

 

[Imb]

Thanks for indulging my curiosity. As I thought, your generic form is pre populated with some controls. I was wondering how you decided how much of each control to put on your generic form. For example, I see a few Checkboxes, is that enough for "all" situations?

Hi theDBguy,

All what I have is the result of continuous result of generalisation code, so it is/was a long evolutionary path, with successes but also with failures. Where I say that I have one "The" form, I have actually two: one for continuous records, and one for all the rest.
In the continuous from I think I can handle 20 fields. The maximum I reached one time was I think 18 fields. More fields are not likely, because you can better think of some redesign of the table.
In the form of the rest I can accomodate 20 subforms, that each contain 66 controls. I needed that number one time, and until now that seems enough.

The controls in the Header are partly used as column header for the subform, the rest to hold status information of the form.

The "user-controls" are in the Detail-part. In first instance I had all kind of controltypes, but practice learned that you have always a short of one of the types. And the counting is rather complex.
Then I decided to use only textboxes, that are tuned to their function. The additional advantage is that you can make a mix of all.
So, a Label can also used as textbox, including backcolor, and in the same time react on click as a command button.
For a Checkbox I use now Chr(97) of the Marlett font, with a toggle-function on Click.

The Buttons in the Footer had their function in the past, but will disappear in future. They are still there for backward compatibility.

The absolute number of Checkboxes is on this moment 1320, enough I think.

But there are also escape possibilities. On this moment I use the same subform for all the positions. It is no problem define Super-subform, or a mini-subform instead of the standard subform.


Imb.

 

[Imb]

I have read Imb’s posts promoting this “world” for years with very little value as an answer to any OP question. Perhaps this forum is more appropriate.

Hi DHookom,

Thank you for this compliment. I think I was in the wrong forum.
Almost all questions were about problems that did not exist in my systematics any more. And all my answers were more a disturbance of the standard Access way.
On the other hand, while it was an open forum, no one ever said: "Oh, interesting, continu". But understandable.

I would be more interested in the data and table structures behind the functionality. I expect every one of us have stored some design/logic in tables of our projects. This allows users to customize interface and functionality without paying more contract hours.

Do you mean the metadata-tables? Or the data-tables?

Show me the beef (ERD).

I have no. The program flow is not different from the program flow in standard Acces, which is problably different for each application.
- Retrieve data from a field to fill a control
- After filling check for conditions as in the BeforeUpdate event
- If OK, store the value in the field in the right way
The only difference is that all this information is "loaded" in the form on opening, and not defined as static events.

The functionality of this is stored in a linked library. This functionality is parametrized for the diffrent Items/Entities.


Imb.

 

[Imb]

I've been building Access applications since version 2.1 in the 90's. I'm pretty sure I've logged more than a hundred by now.

Hi Pat,

Well, I think I have to nuanciate my question a little bit.
100 as an abolute number is just a matter of time.
But manage 100 applications at the same time including new wishes and ever growing functionality is a little bit different.

Things like postal code are truly generic as long as the field doesn't need to handle codes for different countries. If the control needs to handle multiple countries, you need to include code for ALL countries in the world but that's because the rules for validating postal code are specific for each country. They are not different for each application or for each company that needs the application. Other fields aren't so common as I pointed out and may have some common validation but could otherwise be different per application or per client.

Many, many controls can contain country-specific information.
Think of SSN's, Banking Account numbers (IBAN), telephone systematics, coins collections, and more. To check for validity you must know the systematics. For IBAN numbers you must know how to calculate the 3rd and 4th character as some kind of checksum.

Here in my homeland we have a system that postal code + housenumber is unique.
A Street can cover multiple postal codes (long streets), or a postal code can cover multiple streets (short streets).
This means (in my database) that I have 652647 Postal codes in 243288 unique street names in 456 cities.
In an Adress-table you only need to fill the postal code and housenumber and Straat and City are automatically filled.
The systematics in Belgium are different. You specify the Postal code then you choose one of the cities/villages that use this code.

For postal codes I have a separate application, using the same "form"-systematic.
In this Postcode-database I store the knowledge that I have on postcodes in country-specific tables.
When I need to retrieve a postcode, I need to supply the country, else the default country is used.
With this information the right tables are selected, and the retrieved data returned. Depending on the country it is City and Street (the controls are not editable by the user, in BE a "generalized form" to select the town, in GE just the town, etc.
The other way around, if you know (part of) the cityname and/or (part of) the streetname you get a Selection form to pick of of the postal codes that fullfill the search criteria.

In the definition of the RealWorld controls I can define which controls have the meaning of the contributing controls: Country, City, Street, Housenumber, Postalcode, to make it independant of fieldnames.

So are you keeping code in the tables? Show us a table. Show us the code behind a control.

It is just the code that you use to retrieve data form a table/query using the appropriate fieldnames.
Only the way to show the results is far easier thanks thanks to dynamical forms that can be defined at any moment.


Imb.