none
Whats best way to manage forms/controls/properties from backend? RRS feed

  • Question

  • My app has 30+ users and we are going to double soon.
    I'm looking for a way to manage all Controls on forms from a remote table. My Access application has a SQL Server backend. My application security is not part of Access or the SQL Server. It is just a table with department numbers linked the users allowed access to that departments Forms/Controls. I use the Tag property right now and have to set values in the Tag property for each control I make which is insane.

    Depending on a user's security levels, I want to determine how the properties of each control is set(Mostly Enabled/Locked but potentially others) on a Form when it loads. Having it search a table instead of how I am doing it now.

    I'm thinking about creating some tables to handle this logic. Control names may change and Controls/Forms may be deleted or added often. I want to create a form to manage Forms/Controls/Properties but am having trouble coming up with the best approach as I've never done such a thing before.

    I expect I will select a Form Name from my management form, it will analyse the form and get a list of all the controls on the form. Then compare those controls to a list of controls in a table to see if any are new or missing since last time and flag them for "addition" or "deletion" into the audit table.

    Then I will need to have another table(or I can take a denormalized approach) to list all properties for each control on each form and correlate the security levels with the property values.

    Are there any good examples of what I am trying to accomplish out there already? Is there a better way to do this? What seems the most daunting is that controls/forms may be added/deleted or renamed, and I am trying to avoid adding/deleting records everytime this happens. Is it overkill to have the system check for new/deleted controls/forms/properties? Should I just add/delete records eachtime I rename/add/delete?

    Thanks for the help!
    Wednesday, February 3, 2016 6:03 PM

Answers

  • Are there any good examples of what I am trying to accomplish out there already? Is there a better way to do this? What seems the most daunting is that controls/forms may be added/deleted or renamed, and I am trying to avoid adding/deleting records everytime this happens. Is it overkill to have the system check for new/deleted controls/forms/properties? Should I just add/delete records eachtime I rename/add/delete?

    Hi HTHP,

    Interesting question!

    There are many ways to do this, and the best way is the way that suits the best with your house style and used systematics. But in my opinion all the different ways share that they use (meta)data tables to store meta data: data that describes how the code will run.

    That this kind of approach works follows from the fact that on this moment I have 92 different applications running, all with the same systematics.

    But I am a little worried about who does the add/delete/rename of forms and controls. Is that only you as developper, or are that the end users. In the latter case I am afraid you cannot have enough controlability over the system. In my applications users NEVER need to modify any form or control. The end users have enough flexibilty to define what they want. Depending on definitions in the meta data tables forms and/or controls are visible or hidden.

    If the above is about in line with what you want, then let me know, and we can continue.

    Imb.

    • Marked as answer by HTHP Thursday, March 3, 2016 8:40 PM
    Wednesday, February 3, 2016 8:20 PM

All replies

  • Are there any good examples of what I am trying to accomplish out there already?

    Not exactly the same but should be close enough. Take a look at UtterAccess' Code Archive.

    Hope that helps...

    • Proposed as answer by André Santo Wednesday, February 3, 2016 7:36 PM
    Wednesday, February 3, 2016 6:18 PM
  • Are there any good examples of what I am trying to accomplish out there already? Is there a better way to do this? What seems the most daunting is that controls/forms may be added/deleted or renamed, and I am trying to avoid adding/deleting records everytime this happens. Is it overkill to have the system check for new/deleted controls/forms/properties? Should I just add/delete records eachtime I rename/add/delete?

    Hi HTHP,

    Interesting question!

    There are many ways to do this, and the best way is the way that suits the best with your house style and used systematics. But in my opinion all the different ways share that they use (meta)data tables to store meta data: data that describes how the code will run.

    That this kind of approach works follows from the fact that on this moment I have 92 different applications running, all with the same systematics.

    But I am a little worried about who does the add/delete/rename of forms and controls. Is that only you as developper, or are that the end users. In the latter case I am afraid you cannot have enough controlability over the system. In my applications users NEVER need to modify any form or control. The end users have enough flexibilty to define what they want. Depending on definitions in the meta data tables forms and/or controls are visible or hidden.

    If the above is about in line with what you want, then let me know, and we can continue.

    Imb.

    • Marked as answer by HTHP Thursday, March 3, 2016 8:40 PM
    Wednesday, February 3, 2016 8:20 PM
  • I am the only developer, and the only one who will add/delete/rename or doing any type of development of the application for that matter. I think this metadata approach sounds like what I'm trying to accomplish. My application is locked down well.

    For a brief description of current application navigation by users: I use a simple but custom ribbon. I use the Tag attribute of each control in the Ribbon XML to store department numbers(comma delimited) which determine which Groups/Tabs/Controls of the ribbon display to a user during the GetVisible event of the ribbon controls. There are also many command buttons in Forms that open other forms. The load event of all Forms create an instance of a class I made which is responsible for looping over all the controls of that form and looks for a string in each control’s Tag property which might look like: “EnableTrue(1,2,6) EnableFalse(3,9) VisibleTrue(1,2,3) “

    Then the class splits the delimited department numbers and sets the property of the control if the user is part of that department. This is madness I know.

    What guidance would you give if I were to proceed to use metadata tables?
    Wednesday, February 3, 2016 9:23 PM
  • What guidance would you give if I were to proceed to use metadata tables?

    Hi HTHP,

    The guidance is just telling how I think about the use of metadata tables, you can pick out what you need. But there are already a few (and probably more) differences in yours and mine applications.

    The systematics in the metadata tables has lead me to generalized forms and controls, that are used in all the applications. It is not a fixed form with controls bound to fields, but fields are bound to controls on opening the form.
    And because for het users all necessary functionality is already build in in the forms, I do not use Ribbons or Commandbars at all. By the way, this makes it very easy.

    I will now try to describe the process. All is arranged around  "items", the entities in the database. All fields in all entities that have a user meaning are descibed in a metadata table "A_Veld_tbl", with fields "Form_item" (the Item) and "Veldnaam" (field name). In this A_veld_tbl I have a number of other fields that describe the visibility, editabilty, required, forbidden, order, position, and many more properties for the control based on the field, depending on authorization.

    In the Open event of the form a selection is made from the A_Veld_tbl according to the Form_item. Basaed on the Form_item a RecortdSource is assigned, all controls get their ControlSources, and there is the form.

    Because of all the information that is in this A_Veld_tbl, this table has "magic  properties" in relation to database management.

    Still interesting to continue?

    Imb.

    Wednesday, February 3, 2016 10:36 PM
  • I would be pleased if you would continue. Are you saying you are able to use only this one "A_Veld_tbl" metadata table to accomplish this? Most my forms and controls are bound, however I've been began binding many of them during Load also, just not from a metadata table. Do you create a form and give it controls and name them, then start creating metadata table records and let them all? This is very interesting to me.
    Wednesday, February 3, 2016 11:23 PM
  • I would be pleased if you would continue. Are you saying you are able to use only this one "A_Veld_tbl" metadata table to accomplish this? Most my forms and controls are bound, however I've been began binding many of them during Load also, just not from a metadata table. Do you create a form and give it controls and name them, then start creating metadata table records and let them all? This is very interesting to me.

    Hi HTHP,

    In total it looks quite complex for the outside world, that is why I will dose the information in parts that can be digested.

    In fact I have two things in parallel: first the definition of how controls should behave, for instance in relation to authorization, and second the use of generalized forms.

    For the first part only a kind of A_Veld_tbl is necessary. I use a lineair kind of authorization, the higher the number, the more functionality. In the A_Veld_tbl there is a field for the edit level and the visibility of the control. To use the latter without producing gaps on the form you need position information of the control. The authorization level is determined at login. This part can be implemented quite simple: select all the records from A_Veld_tbl for that specific Item, Loop through this set while selecting the right control on the form, and tune the properties of the control.

    Far more interesting, but also more complex are the generalized forms. Mainly I have four formtypes: Overview form, New_form for new records, Enkel_form for single records, Select_form for any selection instead of Listboxes and all these things. In the A_veld_tbl I have also fields to indicatie in which form which fields appear.

    Back to your questions. For the generalization part I use a number of additional metadata table, a.o.  A_Form_tbl, that describes the characteristics of the form characteristics of the used Item, such  as edit, form dimensions, default sql-string for the RecordSource, etc.

    For the forms I used pre-defined form with hidden controls, systematically named, That are tuned in the Open event of the form, keeping track of which field is coupled to what control.

    One of the "magic possibilities" is that - just by inspecting these metadata tables, and that goes very fast - I can immediately see how the 92 different applications behave.

    Imb.


    • Edited by Imb-hb Thursday, February 4, 2016 7:56 AM some typos
    Thursday, February 4, 2016 7:53 AM
  • Sorry Imb-hb, I was sick for a while. If you are still willing to follow up, I am still interested. How do you manage new forms/controls being added/deleted/renamed in the metadata tables. Automated, or do you manually deal with that, or some hybrid? Also, I need to be able to create individual security profiles. Meaning, I can't say that security level 3 has properties set as anything > 3. It will have to have it's own unique settings, no matter its relation to Security Level 1, 2, or 4+. How well would you say your metadata schema would handle something like that? What other things can you say about your implementation?
    Monday, February 8, 2016 8:59 PM
  • Sorry Imb-hb, I was sick for a while. If you are still willing to follow up, I am still interested. How do you manage new forms/controls being added/deleted/renamed in the metadata tables. Automated, or do you manually deal with that, or some hybrid? Also, I need to be able to create individual security profiles. Meaning, I can't say that security level 3 has properties set as anything > 3. It will have to have it's own unique settings, no matter its relation to Security Level 1, 2, or 4+. How well would you say your metadata schema would handle something like that? What other things can you say about your implementation?

    Good to see you back. I hope you are recovered completely. For a moment I thought you lost the interest, because in general it is rather complicated, and for most "hardly the effort worth".

    A very important feature in the A_Veld_tbl is the "Veldtype". This is a field type that goes further then the Access field type, you can see it as a kind of subtype. To this subtype I can assign all kind of property- and method-like things, that come into effect for that control on that specific form (Item). These are are related to the BeforeUpdate and Afterupdate events of the control.

    On this moment the security level is one level with different hierarchies. To extent this to individual users would mean a relation table between A_Veld_tbl and the different users.

    Besides A_Veld_tbl that describes all controls on the forms, I have also an A_Form_tbl, that is a collection of all used "Items". This table contains a.o. information on the position of the Item on a kind of navigation form, but also a default SQL-string (without the selected field, the WHERE clause and the ORDER clause). This SQL-string can be generated automatically from A_Veld_tbl, using Veldtype, including all necessary Joins.

    The management of forms and controls is automated, and compared to standard Access, just the other way around.

    To add a form, or in fact a new Item, a new record is added to A_Form_tbl. After save a generalized form is copied, the Item_tbl is generated in the BE, some standard controls are added to A_Veld_tbl together with the fields in the Item_tbl, an entry is made in A_Data_tbl, so that upon installation the user BE is automatically synchronized.

    To rename a form (Item), the generalized form is renamed, the Item records in A_Veld_tbl are renamed, the table in BE is renamed, and an entry is made in A_Data_tbl for synchronization of the user BE.

    To delete a form (Item), just the same. Delete a record form the A_Form_tbl will do all the necessary follow up things.

    In fact, the same things happens when manipulating controls to A_Veld_tbl. Basically these actions of add/rename/delete in A_Veld_tbl are followed by add/rename/delete in the Item table, data definition is changed accordingly, and logged in A_data_tbl for synchronzation of the user BE.

    This all results in a completely automatic synchronization of the user BE, with no necessity to have any kind of release control.

    Especially for maintaining many applications synchronuously, this works fantastic.

    Imb.


    • Edited by Imb-hb Monday, February 8, 2016 11:21 PM typos
    Monday, February 8, 2016 11:16 PM
  • That is quite amazing. Am I understanding correctly, are you able to have the user's FE create a new form just by adding records in the metadata tables and you don't need to release a new copy of the application?
    Tuesday, February 9, 2016 10:01 PM
  • That is quite amazing. Am I understanding correctly, are you able to have the user's FE create a new form just by adding records in the metadata tables and you don't need to release a new copy of the application?

    Hi HTHP,

    Thank you for your continued interest. You are an exception in this forum!

    No, it is a little different. The users have their FE, and that functions as it is. Users do not modify at all anything of the FE, nor data definition in the BE. Nevertheless the have all flexibility they need to manipulate the data.

    Changes are done in the developpers FE, and indeed, adding a new record to the A_Veld_tbl results immediately in an additional control on the form. From time to time a new FE is delivered to the user, and just by opening the new FE, all changes in data definitions are synchronized. Eventually some procedures will run to modify the data itself, if necessary.

    For this purpose I use a few generalized forms for the most important tasks: Overview form, Selection form (as replacement for Listboxes and Comboboxes), a form for New records, and a form for Single records. All these forms are tuned by the records in the A_Veld_tbl.

    This A_Veld_tbl is the central part in the total functionality. It also contains the information how to behave in the before and after update of the control/field. Very important, when a field is is to be changed or is changed, whether it is via a control on any form, or through code in a routine, this A_Veld_tbl record is inspected for the appropriate actions. That makes field modifications robust.

    All this is controlled by generalized routines, so it makes no difference which Item it is, or which field is processed.

    All these generalized routines are placed in a referenced library that is shared by all applications. And that makes it interesting, a new application is build just by filling a new A_Form_tbl and A_Veld_tbl. More then 95% of all functionality of the application is then already available.

    Imb.

    Tuesday, February 9, 2016 10:58 PM
  • In field [A_Veld_Tbl].[Form_Item] you hold the ID or Name of the form? And hold special fields/normal access properties in [A_Veld_Tbl].[Veldnaam]? Also, do you just prefer not to use ListBoxes/ComboBoxes or would it add another layer of complexity that you don't want?

    Does A_Veld_Tbl have table fields in its own definition for every possible field of a control. I mean, lets say you have a CommandButton, CheckBox, TabControl on a form. These controls have different attributes. Even though the ColumnCount property is not needed for a CommandButton, would I be correct in thinking that you may have a field for ColumnCount but leave it Null in records where [Field_Type] is not for example a CommandButton, Or CheckBox.
    Wednesday, February 10, 2016 3:25 PM
  • In field [A_Veld_Tbl].[Form_Item] you hold the ID or Name of the form? And hold special fields/normal access properties in [A_Veld_Tbl].[Veldnaam]? Also, do you just prefer not to use ListBoxes/ComboBoxes or would it add another layer of complexity that you don't want?

    Does A_Veld_Tbl have table fields in its own definition for every possible field of a control. I mean, lets say you have a CommandButton, CheckBox, TabControl on a form. These controls have different attributes. Even though the ColumnCount property is not needed for a CommandButton, would I be correct in thinking that you may have a field for ColumnCount but leave it Null in records where [Field_Type] is not for example a CommandButton, Or CheckBox.

    Hi HTHP,

    All matter in these generalized database is organized around Items. Items are the same as the formal Entities, but the Item form (with the controls) can contain some more control than the Entity offers. The data is stored in an Item_tbl, the PK in the table is always Item_id (autonumber), the related form is Item_formx. For the meta data tables I use this same defined syntax, but all starting with "A_", to see them together, apart from the other tables.

    A_Veld_tbl has also an ID: A_Veld_id. The same with A_Form_tbl and A_Form_id. For ready readability is still use Form_item to describe which Item it refers to (I also could have referred to A_Form_id).

    When an Item_formx is opened, the records are retrieved from A_Veld_tbl with the correspoding Form_item, and sorted according to some ordening number. Depending on the width field in A_Veld_tbl the controls get their width, are positioned accordingly, and made visible. According to another field in A_Veld_tbl the editability of the control is set. The control labels or column headers get assigned the Veldnaam. Finally the sql-string for the RecordSource is constructed and assigned, and the controls get their ControlSource. An other field in A_Veld_tbl contains Help information.

    The relation between the field name and the "control label" is Veldnaam, in general the field name from the table, but any translation is possible.

    Sometime there are controls of a different ControlType, e.g. CheckBox. This difference is handled in the general routines through the field Veldtype in A_Veld_tbl.

    CommandButtons are handles separately, using the meta data table A_Click_tbl.

    Clicking or Rightclicking on the opens a new world on possibilities regarding that field: sorting, counting, histogram, ...

    The fact that I do not use ListBoxes or ComboBoxes is not because any added complexity, but because the functionality is far to limited. You cannot sort on the fifth column to fast find that wanted record, and you cannot zoom in to get other information before you select the right record.

    Imb.

     

    Wednesday, February 10, 2016 7:51 PM
  • Example:

    Wednesday, February 10, 2016 8:01 PM
  • I'm constructing something similar to what you've been describing. I think it is amazing what you've done. I can't imagine that it wouldn't help speed up development in almost any system. I will followup with more questions probably Monday, but for now other projects are calling my attention. You've given me alot to think about.
    Thursday, February 11, 2016 11:02 PM
  • I'm constructing something similar to what you've been describing. I think it is amazing what you've done. I can't imagine that it wouldn't help speed up development in almost any system. I will followup with more questions probably Monday, but for now other projects are calling my attention. You've given me alot to think about.

    Hi HTHP,

    It takes about 3 seconds to define a new application, still without tables, but already with more than 95% of all its future functionaity.

    Imb.

    Friday, February 12, 2016 1:01 PM