Creating a global constant at runtime

  • Thread starter Thread starter tech.rawsteak
  • Start date Start date
T

tech.rawsteak

I have a function that retrieves a user's login name from their
workstation and looks it up on an employee table to return their full
name (ie: jsmith -> John Smith). Their full name is then displayed on
each form as a greeting, while their login name is used to record
their activities, as well as compared to a list of positions that
allows different users to access different functions on the database.
I have noticed two things however:

a) on menu forms with no discernible data source, the name display
function works fine, but on pages that required users to move from one
record to another, the name display function is executed every time
another record is accessed, even though it has nothing to do with that
record. additionally, if a record does have a username that needs to
be displayed, it will take take longer for access to display the name
on the record and on the user's form.

b) if a user does not match the table with an elevated position, they
are not given certain options. however, if the program hits a glitch
or jumps out of the program flow, it will allow some options to be
shown, even though the user name does not match any names on the
elevated position tables. catching and recovering from the errors are
not impossible, but menu options appear regardless.

i know i have not provided any code, but i was wondering if anyone
knew where the problem might be originating from so i can zero in on
the code and inspect it further. currently, the display name function
is in a module that all the other pages can access, and the menu
options are on the forms that need to selectively display options,
usually in the manner of

if(gbl_empID = gbl_admin) then 'show appropriate options

any ides or comments?
 
I have a function that retrieves a user's login name from their
workstation and looks it up on an employee table to return their full
name (ie: jsmith -> John Smith). Their full name is then displayed on
each form as a greeting, while their login name is used to record
their activities, as well as compared to a list of positions that
allows different users to access different functions on the database.
I have noticed two things however:

a) on menu forms with no discernible data source, the name display
function works fine, but on pages that required users to move from one
record to another, the name display function is executed every time
another record is accessed, even though it has nothing to do with that
record. additionally, if a record does have a username that needs to
be displayed, it will take take longer for access to display the name
on the record and on the user's form.

b) if a user does not match the table with an elevated position, they
are not given certain options. however, if the program hits a glitch
or jumps out of the program flow, it will allow some options to be
shown, even though the user name does not match any names on the
elevated position tables. catching and recovering from the errors are
not impossible, but menu options appear regardless.

i know i have not provided any code, but i was wondering if anyone
knew where the problem might be originating from so i can zero in on
the code and inspect it further. currently, the display name function
is in a module that all the other pages can access, and the menu
options are on the forms that need to selectively display options,
usually in the manner of

if(gbl_empID = gbl_admin) then 'show appropriate options

any ides or comments?
Click to expand...
Look at http://www.mvps.org/access/api/api0008.htm
And http://www.mvps.org/access/api/api0066.htm

You could create a table. Call it WhoAmI (assumes split database).
When you enter the system store the info in WhoAmI. It would always be
the first (and only record) in the table. Then call Dlookup to get the
name and login when necessary. Should be quick in a 1 record table.
 
Look athttp://www.mvps.org/access/api/api0008.htm
Andhttp://www.mvps.org/access/api/api0066.htm

You could create a table. Call it WhoAmI (assumes split database).
When you enter the system store the info in WhoAmI. It would always be
the first (and only record) in the table. Then call Dlookup to get the
name and login when necessary. Should be quick in a 1 record table.
Click to expand...

I think i used the first one, if not a variation of it. the second
one is a bit too intricate thought. Still, I think your "WhoAmI" idea
is simple enough to try. also, while trying out the other getname
function, i noticed that the display user name function (either mine
or the one you linked to) was executed each time a record was accessed
that needed to look up extraneous data, such as a form that has a
datasource defined, but also has to retrieve data from another table.
I've tried making queries to centralize all the data i need for a
form, but certain forms need different external data according to the
selections made on the form.
 
the name display function is executed every time
another record is accessed, even though it has nothing to do with that
record.
Click to expand...

Gee, you could put your function that displays this infofaton in the fomrs
footer, it might not execute everytime. However, why is exetin this fucitno
goign to be a problem.

ms-access is able to run 10, or 20 millin instricons per seocnd. I can't
imainge that you worred aobut saving 1 millioned of a second? Why would you
waste such developer resouces on this small thiing?

Lets try a some code to see how fast that lookup occures:

dim gblRstUser as dao.recordset


Call setuser()
....this rouitne will setup the user table
dim strSql as string

set strSql = "select * from tblUsers where UserName = '" & strLogOn & "'"
set glRstUser = currentdb.openrecordset("select * from tblUsers where
UserName =

So, now we have a glboal record that is set to the users name.

Our function to retive teh "full name" wold be

Public Function UFullName() as string

UFullName = gblRstuser!FullName

end function.

So, on our rerpots, we go:

=UFullname().

lets test how much time it takes to execute our fucntion to lookup and
retive taht full name.


dim i as long
dim t as double
dim s as string
t = timer
for i = to to 1000000
s = UfullName()
next i

t = timer - t
debug.print t

debug.pritn = 2.7 seconds

So, we can exeute this grabbing of the users full naem 1 million times in
2.7 seconds (that on my weaker notebook). So, the time to exectue thsi
fuction ONCE on your form is:


2.7 / 1000000 = 0.0000027

That is not even 100,000 of a second.

Or, simply put, ms-access can execute that function 370,000 times PER
SECOND.

So, if you used the navagtion arrows to view and move throught 370,000
reocrds, the increase time to view those records would be 1 adtional
seconds. And, if a uers was clciking the next buttion once per second, it
would take 6000 seconds (102 minutes) to view that many rerocrs. 102 minutes
is nearing two hours, and in that period you going to increate tihs time by
ONE SECOND.

Really, is there somting paritlcar in your world that you really need to
save 1 second over seveal hours of time?

I must be compliery and 100% missign your point, but some werid and
unforness deatil is being left out here....
additionally, if a record does have a username that needs to
be displayed, it will take take longer for access to display the name
on the record and on the user's form.
Click to expand...

Are you talking about a user logon name that you get once at startup (when
the user logson), or a user name that is a actual field on the form? (and
you looking up this value?). If you need high speed looikup for a form,
consider using a left join, and include that users table (but, it not 100%
clear here, becuase in the prevous parrage you talking about a users logon,
and not you seem to be talking about data in the form.

however, once again, perfoamcne should be a noticalbe issue here.
b) if a user does not match the table with an elevated position, they
are not given certain options. however, if the program hits a glitch
or jumps out of the program flow, it will allow some options to be
shown, even though the user name does not match any names on the
elevated position tables. catching and recovering from the errors are
not impossible, but menu options appear regardless.
Click to expand...

Huh? you complte lost me in the above. If you don't trap errors in
sma-ccess,
then ALL LOCAL AND GLOBAL varables are re-set, and their values are lost.

Solution:
a) use error hadning code
b) displiate a mde to your users. Unhandeld errors will NOT re-set
vaiblaes
There is zillion reasons to displaite a mde to your end usrs, and any
devloper
worht their salt will immdmdiyy reialzing this.

It not clear what you mean by "glitch", you might want ot expain furhter.
if(gbl_empID = gbl_admin) then 'show appropriate options

any ides or comments?
Click to expand...

Well, all I can say is once you got the users logon name, then load in the
reocrdset (or as my example shows, set the global reocrdset to the ONE
record...and you can retrieve values from that one record easily in excess
of 250,000 values per second. You not give any details as to if you doing
some type of table scan to retrieve values from "MANY" records to grab your
information used for privileges, or if you use one record with many fields.
If you do actually have multiple records for each logon user that holds the
permissions, then simply load up the reocrdset at logon with users 15, or 20
records....a findfirst on that table will be super fast, and again I can't
see reason to worry about performance issues..
 
Yikes...lets try this will spell checking....
the name display function is executed every time
another record is accessed, even though it has nothing to do with that
record.
Click to expand...

Gee, you could put your function that displays this information in the forms
footer, it might not execute every time. However, why is executing this
function
going to be a problem.

ms-access is able to run 10, or 20 million instructions per second. I can't
imagine that you worried about saving 1 millioned of a second? Why would you
waste such developer resources on this small thing?

Lets try a some code to see how fast that lookup occures:

dim gblRstUser as dao.recordset


Call setuser()
....this rouitne will setup the user table

eg:
dim strSql as string

set strSql = "select * from tblUsers where UserName = '" & strLogOn & "'"
set glRstUser = currentdb.openrecordset(strSql)

So, now we have a global record that is set to the users name.

Our function to retrieve the "full name" would be

Public Function UFullName() as string

UFullName = gblRstuser!FullName

end function.

So, on our rerpots, we go:

=UFullname().

lets test how much time it takes to execute our function to lookup and
retrieve that full name.


dim i as long
dim t as double
dim s as string
t = timer
for i = to to 1000000
s = UfullName()
next i

t = timer - t
debug.print t

debug.pritn = 2.7 seconds

So, we can execute this grabbing of the users full name 1 million times in
2.7 seconds (that on my weaker notebook). So, the time to execute this
function ONCE on your form is:


2.7 / 1000000 = 0.0000027

That is not even 100,000 of a second.

Or, simply put, ms-access can execute that function 370,000 times PER
SECOND.

So, if you used the navigation arrows to view and move through 370,000
records, the increase time to view those records would be 1 additional
seconds. And, if a users was clicking the next button once per second, it
would take 6000 seconds (102 minutes) to view that many records. 102 minutes
is nearing two hours, and in that period you going to increate this time by
ONE SECOND.

Really, is there something particular in your world that you really need to
save 1 second over several hours of time?

I must be complete and 100% missing your point, but some weird and
unforeseen detail is being left out here....
additionally, if a record does have a username that needs to
be displayed, it will take take longer for access to display the name
on the record and on the user's form.
Click to expand...

Are you talking about a user logon name that you get once at start-up (when
the user logon), or a user name that is a actual field on the form? (and
you looking up this value?). If you need high speed lookup for a form,
consider using a left join, and include that users table (but, it not 100%
clear here, because in the previous paragraph you talking about a users
logon,
and not you seem to be talking about data in the form.

however, once again, performance should be a not be noticeable issue here.
b) if a user does not match the table with an elevated position, they
are not given certain options. however, if the program hits a glitch
or jumps out of the program flow, it will allow some options to be
shown, even though the user name does not match any names on the
elevated position tables. catching and recovering from the errors are
not impossible, but menu options appear regardless.
Click to expand...

Huh? you complete lost me in the above. If you don't trap errors in
ms-access,
then ALL LOCAL AND GLOBAL variables are re-set, and their values are lost.

Solution:
a) use error handing code
b) distribute a mde to your users. Unhandled errors will NOT re-set
variables
There is zillion reasons to distribute a mde to your end users, and any
developer
worth their salt will realize this.

It not clear what you mean by "glitch", you might want to explain further.
if(gbl_empID = gbl_admin) then 'show appropriate options

any ides or comments?
Click to expand...

Well, all I can say is once you got the users logon name, then load in the
reocrdset (or as my example shows, set the global reocrdset to the ONE
record...and you can retrieve values from that one record easily in excess
of 250,000 values per second. You not give any details as to if you doing
some type of table scan to retrieve values from "MANY" records to grab your
information used for privileges, or if you use one record with many fields.
If you do actually have multiple records for each logon user that holds the
permissions, then simply load up the reocrdset at logon with users 15, or 20
records....a findfirst on that table will be super fast, and again I can't
see reason to worry about performance issues..

Note that you can declare variables as public in a standard module, and they
are available anywhere, and anytime. If you need a variable as a expression,
then you have to sue a function as above.
 
Yikes...lets try this will spell checking....


Gee, you could put your function that displays this information in the forms
footer, it might not execute every time. However, why is executing this
function
going to be a problem.

ms-access is able to run 10, or 20 million instructions per second. I can't
imagine that you worried about saving 1 millioned of a second? Why would you
waste such developer resources on this small thing?

Lets try a some code to see how fast that lookup occures:

dim gblRstUser as dao.recordset

Call setuser()
...this rouitne will setup the user table

eg:
dim strSql as string

set strSql = "select * from tblUsers where UserName = '" & strLogOn & "'"
set glRstUser = currentdb.openrecordset(strSql)

So, now we have a global record that is set to the users name.

Our function to retrieve the "full name" would be

Public Function UFullName() as string

UFullName = gblRstuser!FullName

end function.

So, on our rerpots, we go:

=UFullname().

lets test how much time it takes to execute our function to lookup and
retrieve that full name.

dim i as long
dim t as double
dim s as string
t = timer
for i = to to 1000000
s = UfullName()
next i

t = timer - t
debug.print t

debug.pritn = 2.7 seconds

So, we can execute this grabbing of the users full name 1 million times in
2.7 seconds (that on my weaker notebook). So, the time to execute this
function ONCE on your form is:

2.7 / 1000000 = 0.0000027

That is not even 100,000 of a second.

Or, simply put, ms-access can execute that function 370,000 times PER
SECOND.

So, if you used the navigation arrows to view and move through 370,000
records, the increase time to view those records would be 1 additional
seconds. And, if a users was clicking the next button once per second, it
would take 6000 seconds (102 minutes) to view that many records. 102 minutes
is nearing two hours, and in that period you going to increate this time by
ONE SECOND.

Really, is there something particular in your world that you really need to
save 1 second over several hours of time?

I must be complete and 100% missing your point, but some weird and
unforeseen detail is being left out here....


Are you talking about a user logon name that you get once at start-up (when
the user logon), or a user name that is a actual field on the form? (and
you looking up this value?). If you need high speed lookup for a form,
consider using a left join, and include that users table (but, it not 100%
clear here, because in the previous paragraph you talking about a users
logon,
and not you seem to be talking about data in the form.

however, once again, performance should be a not be noticeable issue here.


Huh? you complete lost me in the above. If you don't trap errors in
ms-access,
then ALL LOCAL AND GLOBAL variables are re-set, and their values are lost.

Solution:
a) use error handing code
b) distribute a mde to your users. Unhandled errors will NOT re-set
variables
There is zillion reasons to distribute a mde to your end users, and any
developer
worth their salt will realize this.

It not clear what you mean by "glitch", you might want to explain further.





Well, all I can say is once you got the users logon name, then load in the
reocrdset (or as my example shows, set the global reocrdset to the ONE
record...and you can retrieve values from that one record easily in excess
of 250,000 values per second. You not give any details as to if you doing
some type of table scan to retrieve values from "MANY" records to grab your
information used for privileges, or if you use one record with many fields.
If you do actually have multiple records for each logon user that holds the
permissions, then simply load up the reocrdset at logon with users 15, or 20
records....a findfirst on that table will be super fast, and again I can't
see reason to worry about performance issues..

Note that you can declare variables as public in a standard module, and they
are available anywhere, and anytime. If you need a variable as a expression,
then you have to sue a function as above.
Click to expand...

as cryptic and condescending as your reply is, i have to admit you
make a point. unfortunately, the person named "salad" explained in a
much more comprehensible and succinct manner. while i do appreciate
your lengthy and descriptive thesis on the amount of functions that
can be executed running off of an albeit slower notebook, i just
wanted some feedback on how I could do something better. for further
reference, it's not how well you can prove your answer to be correct -
it's how well your answer can be understood. remember, quality over
quantity.
 
Back
Top