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.
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.
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.
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?
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.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com 
