ACCESS FEED: RE: [MS_AccessPros] Login form

Graham,

Thanks for sending this information. I like this idea and I am going to
keep this email as I am sure I will have to do this at some time in the
future.

Bill Singer

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Graham Mandeno
Sent: Monday, October 22, 2012 5:33 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Login form

Hello Bill

Sorry I'm a bit late to this party, but I have a couple of suggestions that
might be of use.

First, I fully endorse all the advice you have received about splitting the
database. I consider it essential, particularly in a multi-user
environment, and I think you can thank your lucky stars that you have not
been bitten by corruption problems before now.

As for storing the UserID in a local table, am I right in assuming that all
the users on the network have different Windows login usernames? If this is
the case, then you can store the user's login usernames for ALL your users
in a back-end table, along with their UserID, full name, email address,
phone, and any other details that may be useful to your application.

When the front-end is opened, the UserID and other details of the current
user can be ascertained by looking up the record with the corresponding
Windows username.

You can get the Windows username by calling an API function like this:

Private Declare Function GetUserName _
Lib "advapi32.dll" _
Alias "GetUserNameA" ( _
ByVal lpBuffer As String, _
nSize As Long _
) As Long

Public Function WindowsUsername() As String
Static sUsername As String
Dim nLen As Long
If Len(sUsername) = 0 Then
nLen = 100
sUsername = String(nLen, 0)
GetUserName sUsername, nLen
If nLen > 0 Then sUsername = Left(sUsername, nLen - 1)
End If
WindowsUsername = sUsername
End Function

Finally, instead of using the Linked Table Wizard to manually relink tables
when moving a back-end or installing an updated front-end, I prefer to have
the front-end look after this automatically. My front-ends contain two
related tables: USysDataSources and USysLinkedTables. There is one "data
source" record for each back-end file (often there can be several) and one
related "linked table" record for each table linked from that source. Each
data source has a unique name (a text key), and the location of the file can
be ascertained by looking up that key in a small "INI" file, usually in the
same folder as the front-end. So, every time the front-end is opened, it
runs a small piece of code to ensure that all the linked tables are linked
to the correct back-end tables and, if not, it relinks them correctly.

If you are interested, I can expand a bit on this for you and maybe put
together a sample.

Best wishes,
Graham

> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
Singer
> Sent: Tuesday, 23 October 2012 09:47
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Login form
>
> John
>
>
>
> The database is split, a table is now in the local copy, called
> t_UserID.
> I have the login in form completed with a combo box that pulls the ID from
> the table of possible users. The ID is a number. The value is stored in
> the local table, which has one record. (Do I need a primary key, auto
> index?) The field is UserID.
>
>
>
> Now is where it gets fuzzy for me.
>
>
>
> There are two buttons on the Login form. One says cancel and closes the
> database. The other should lead me to the database but is not programmed
> yet.
>
>
>
> I do not understand this. "CurrentDb.Execute "UPDATE t_User Set = '" &
> Me.UserID & "'", dbFailOnError
>
>
>
> I am not sure where it goes. Maybe I am having brain gridlock but it is
> not coming to me.
>
> I am sure I will have another question but I will wait for now.
>
>
>
> thanks
>
> Bill Singer
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
> Sent: Thursday, October 18, 2012 9:54 AM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] Login form
>
>
>
>
>
> Bill-
>
> I assume you split out all the tables into the back end. You can now
> define a local table in the front end - it won't be a shared link and each
> user will have their own copy. Simply write the logged-in user ID to that
> table. You could also put it in a Public variable and write a function to
> fetch it when needed. Let's say the table is called ztblCurrentUser and
> the one field is UserID. The table should have one row in it. When you get
> a user ID set in the form, do:
>
> CurrentDb.Execute "UPDATE t_User Set = '" & Me.UserID & "'",
> dbFailOnError
>
>
> Your function could look like:
>
> Option Compare Database
> Option Explicit
>
> Public strUserID As String
>
> Function GetUser() As String
> ' If the public variable isn't set,
> If Len(strUserID) = 0 Then
> ' Fetch it from the local table
> strUserID = DLookup("UserID", "ztblCurrentUser") End If ' Return the user
> ID GetUser = strUserID End Function
>
> Whenever you need to test the ID in code, just call the function:
>
> If GetUser() ="Jviescas" Then C
>
> You can also use the function in an expression in a query.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: Bill Singer <Bill.Singer@at-group.net
<mailto:Bill.Singer%40at-group.net> <mailto:Bill.Singer%40at-
> group.net> >
> Reply-To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> >
> Date: Thursday, October 18, 2012 3:32 PM
> To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> >
> Subject: RE: [MS_AccessPros] Login form
>
> John,
>
> Yes, the split went better than I though. I am waiting for my co workers
> to get in so I can test it.
>
> Now I need to get back to the subject that started this conversation.
>
> I am implementing a Login form so that I can sort specific information to
> the user.
>
> The login form is going to have a combo box with the user's name and ID
> (which will not show) No password.
>
> After each user opens their FE database, logs in, what do I do with the
> login form. I need to capture that ID for the person that logs in and if I
> close the form the ID goes away.
>
> You mentioned "If you store the ID in a local table in the "code" database
> linked to the rest of the data on a server, you will maintain local info
> for each user independently."
>
> Sorry, I do not know what this means. "store the ID in a local table in
> the "code"
>
> Bill Singer
>
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> Viescas
> Sent: Thursday, October 18, 2012 2:10 AM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] Login form
>
> Bill-
>
> I see from later posts that others have already convinced you to split it.
> You've just been lucky to this point to have 4 users share simultaneously
> and not get corruption or worse.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: Bill Singer <Bill.Singer@at-group.net
<mailto:Bill.Singer%40at-group.net> <mailto:Bill.Singer%40at-
> group.net>
> <mailto:Bill.Singer%40at-group.net> >
> Reply-To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> >
> Date: Wednesday, October 17, 2012 6:37 PM
> To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> >
> Subject: RE: [MS_AccessPros] Login form
>
> Sorry John, I do not know what you mean when you say "local table in the
> "code" database linked to the rest of the data on a server"
>
> Just in case this matters. I do not have a split database. One database
> with form and queries and tables all together. It is on a network hard
> drive. All 4 users access it from there.
>
> Bill Singer
>
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> Viescas
> Sent: Wednesday, October 17, 2012 11:27 AM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] Login form
>
> Bill-
>
> If you store the ID in a local table in the "code" database linked to the
> rest of the data on a server, you will maintain local info for each user
> independently.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: Bill Singer <Bill.Singer@at-group.net
<mailto:Bill.Singer%40at-group.net> <mailto:Bill.Singer%40at-
> group.net>
> <mailto:Bill.Singer%40at-group.net>
> <mailto:Bill.Singer%40at-group.net> >
> Reply-To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> >
> Date: Wednesday, October 17, 2012 6:05 PM
> To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> >
> Subject: RE: [MS_AccessPros] Login form
>
> Jim,
>
> I am not exactly sure how that would work. I have 4 people accessing the
> database at the same time and I want each user to see their own stuff.
>
> Bill Singer
>
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Jim
> Wagner
> Sent: Wednesday, October 17, 2012 10:15 AM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] Login form
>
> Bill
>
> You could store the ID in a table and use the table as reference
>
> Jim Wagner
> ________________________________
>
> ________________________________
> From: Bill Singer <Bill.Singer@at-group.net
<mailto:Bill.Singer%40at-group.net> <mailto:Bill.Singer%40at-
> group.net>
> <mailto:Bill.Singer%40at-group.net>
> <mailto:Bill.Singer%40at-group.net>
> <mailto:Bill.Singer%40at-group.net> >
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Sent: Wednesday, October 17, 2012 7:58 AM
> Subject: [MS_AccessPros] Login form
>
> I have created a login form. It does not even have a password, it just has
> a combo box so the person can input their name. I want to use the
> individuals ID to sort certain queries as the person works on the
> database.
>
> I believe to do this I will need to keep the login form open. If I close
> it I will lose the reference to that ID number. What is the best way to do
> this? Maybe there is a better way of identifying who is working on the
> database than a login inform. Do I just minimize the login form, can I
> hide it, or is there another place to save the ID so I can reference it
> later?
>
> Thanks so much?
>
> Bill Singer
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

[Non-text portions of this message have been removed]