Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Assigning Global Values to variables (97/2k/XP)

    My challenge this week is to find a better more efficient method of implementing lines of code that work. We have built in the ability for a user to "rename" captions and/or labels in reports and forms. I am not sure this is the best method of design, so I am open to suggestions. The following works, but seems inefficient and is cumbersome when adding new forms or reports to the database.

    I have a table (tblLabels) with three fields
    pkey (Primary Key)
    strName (not editable by user)
    strValue (user defined)

    There are three records,
    "1","label1","Region"
    "2","label2","Building"
    "3","label3","Room"
    where Region, Building and Room are how one user has his set up, but other users use names other than the three mentioned.

    The idea is to use tblLabels table to define the labels on forms and reports, giving each user the opportunity to assign a name to each of the three labels that fit her/his needs. In other words on a given form or report, the control caption would be Region, Building, Room for label1, label2, label3, respectively, for a person using the above table records. However, other users use copies of this database and change the names of the strValue in the tblLabels table to match their needs.

    Right now, we have code on the "On Load" event that dimensions the variables, creates a recordset, reads the values into the recordset and assigns them to the controls. It works fine, but to me that seems like an inefficient method as every form and every report that use the labels, has to have the same code in the "OnLoad" event. Is it possible to create a function, class or module that could be run on start up that would load these variables one time and not every time a form or report is run?

    Is there a better way to implement?

    Thanks in advance for your ideas and suggestions.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Assigning Global Values to variables (97/2k/XP)

    It's possible to do this with classes, but it is non-trivial code design. However, I would be more concerned about the fact that users are changing "labels" to fit their own needs, because what do the underlying tables look like? The implication is that you are using some kind of generic table. That may sound like a nice idea, and it is essentially what databases like Act! and even Outlook do, but it is very bad database design for long term use and maintenance because it is inconsistent.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Assigning Global Values to variables (97/2k/XP)

    Charlotte,

    Thanks for feedback.

    There are two Other tables involved:
    tblBiped
    pkey
    strSSN
    strcountry
    strH1
    strH2
    strH3
    dtmDOH
    dtmTerm


    tblOFI
    pkey
    fkey
    strOFINumber
    strSSN
    strH1
    strH2
    strH3
    dtmOFI

    The user(s) do(es) not see the underlying tables, just the data, presented in forms and reports. They can change the value of any of the three labels, and then run reports or open forms and instead of "strH1", "strH2", or "strH3" printing, it prints the corresponding value from the tblLabels table. The names "label1", "label2", "label3" from tblLabels.strName should more appropriately have been named H1, H2, and H3, but this is an inherited project. There is probably a better way, but for now I am stuck with what I have. There about 20 folks using the existing version, and they want additional reports and forms. Ultimately it will be rewritten from scratch, with better practices and conventions, but initially I need to meet their immediate needs with minimal programming on our part (that is create reports and forms for the users using existing structures).

    Does this make sense, or have I muddied the water?

    Thanks for all your help. In addition to being brilliant, you are a kind person!

    Ken

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Assigning Global Values to variables (97/2k/XP)

    In other words, the users can put any value they like in those three fields and customize the "label" for them in the application? I have a much larger example of the same kind of approach to sort out under a contract right now, so I sympathize. Your best bet might be static functions, which will look up the value for the particular "label" specified for that user in the table and return it. With a static function, it only executes the first time and after that it just returns the value, but it doesn't break on unhandled errors the way global variables do. That would allow you to use locked textboxes on your forms and reports instead of labels and have the objects use the static funtions are their control source. That won't require any code beyond building the static functions.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •