Currentuser and splitting a database

[Guest]

Hi All. Thank you, in advance, for all the help everyone has given me. I am
brand new to building Access databases and have been rushed into action to
build one for my company. These message boards have been an excellent source
of info.

The History: I had built a database to track my company's sales and to allow
the salespeople to input forecasts (the only input form in the database).
Initially, I was told that concurrency was an issue. Later, I found out that
it isn't so much with under 25 users - my company's requirements. To get
around concurrency issues, I was going to copy the main database and save it
as a new database SPECIFIC to each salesperson. I would make it specific by
deleting the customers that weren't theirs. I built in a way for them to
export their most current forecasts.

The enlightenment: With new information from these postings, I realize that
we shouldn't have concurrency issues. I also read somewhere that you can
link the user's logon ID to get to that SPECIFIC customer information. I
want to convert to this - aka, one database.

The questions: I read that you can link the query behind the form, via this
Currentuser() command. How do I do this? Also, the SQL for the query behind
the form would need to be changed as a "WHERE (salesperson
field)=Currentuser(). Again, how do I do this? From there, I read that
splitting the database would be necessary. Anything specific I need to know
or be aware of? Finally, are there anymore steps or things I am missing to
convert everything to run off of one database? Thanks much!

 

[Albert D. Kallal]

The questions: I read that you can link the query behind the form, via this
Currentuser() command. How do I do this? Also, the SQL for the query
behind
the form would need to be changed as a "WHERE (salesperson
field)=Currentuser().

CurrentUser is a function that returns the logged on ms-access user (not the
windows user name..but the logged on ms-access user name). If you fire up
ms-access, hit ctrl-g to get to the command prompt window, and type:

? currentuser()

You will see that the function retuns

Admin

So, when you run ms-access, and you do NOT use ms-access security, then you
are automatically logged in to the database as user Admin. So, in effect,
Currentuser() is of no use to you unless working with a secured ms-access
database. Hence, CurrentUser() will always return admin, as that is the user
who is logged into ms-access. However, you can grab the windows logon name.
The code to do that is here, and I often use it:

You can get the current windows network logon name with:

http://www.mvps.org/access/api/api0008.htm

And, the current computer name with:

http://www.mvps.org/access/api/api0009.htm

And, of course, if you do use ms-access security, then currentuser() will
return the ms-access logon. I often log all 3 of the above values in some
applications to keep track of not only who did something..but at what
workstation.

I read that you can link the query behind the form

I am not sure why you used the term "link" here? It seems to me that you
need to offer the operation some means to select, or set the current
salesrep, and then continue. I don't think this should be a automatic, or
behind the scenes type feature??? You need some flexibility here, as some
salesrep might need to lookup some info that was entered/done by another
sales rep..

Anyway, the approach I usually use is to allow the user to "select" their
name from a combo box, and then launch the form, or "list" of customers that
they can work on. And, I use the above function to grab the windows logon
name...and set it as the default.

I have to assume you also have some field in the table that identifies who
the record belongs to. Some ideas for presenting the data as a grid can be
seen here:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

And, for an screen shot that uses the combo box, and the sales rep (that
defaults to the correct user via the above functions), take a look at the
2nd screen here:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

In the above screen, the salesrep combo box is actually automatically
set..and then if the user hits "edit list", then ONLY those
customers/contacts that belong to that salesrep are shown. The code to
"restrict", or filter the list based on sales rep is:

dim strWhere as string

strWhere = "SalesRep = '" & me.cboSalesRep & "'"

docmd.OpenForm "frmCustomers",,,strWhere

And, if you were using the first function, then you could go:

strWhere = "SalesRep = '" & fOSUserName() & "'"

docmd.OpenForm "frmCustomers",,,strWhere

I read that
splitting the database would be necessary.

Splitting the database has nothing to do with my previous comments, and of
course is separate question. You need to split if you are planning
multi-user operation of your application. You can read up on splitting here:

http://www.granite.ab.ca/access/splitapp.htm