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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote