ACCESS FEED: RE: [MS_AccessPros] Login form

John,

Last night I did make the change to the table name. Your directions were
perfect (for a rookie) and I was able to get a query to sort by the
variable, which got the information from the one row in my userID table. I
am very happy.

Here was my confusion (I believe). I am so used to making thing happen with
"click" events that I could not figure out how to get the code to run to
assign the variable. I was under the illusion that the variable would be
assigned and then just sit there and wait to be used. It appears that I was
wrong and the variable is not assigned until the function is called. So in
my case the function to assign the value to the variable just sits there and
does nothing until the query calls for the variable, which was done by
putting GetUser() in the criteria.

It my thinking straight now?

Thanks so much for stepping me through this.

Bill Singer

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, October 24, 2012 1:51 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Login form

Bill-

The code must go in a Standard module, not behind your form. What is the
name of the table in which you're storing the selected user ID? If it's
not t_userID, then you need to change this line:

strUserID = DLookup("UserID", "t_UserID")

The first parameter in the DLookup is the name of the field. The second
is the name of the table.

As I replied earlier, use a call to GetUser() anywhere in your code or
queries to find the stored ID.

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> >
Reply-To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> >
Date: Tuesday, October 23, 2012 9:04 PM
To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> >
Subject: RE: [MS_AccessPros] Login form

John,

I now have one table with one row.

I can get the user ID into the table. I initially set it up so that the
Combo box on the Login form drops the ID in the table. Is that going to
work? It seems to work.

The user selects their name on the Login form from the list of names in the
combo box and the table gets updated.

The problem is that I do not know how to get the value out of the table.
If
I want to run a query and sort the results of the query by the UserID I do
not know how to grab that UserID out of the local table. I can't add it to
the criteria filed in the Query grid. When I run the query I get a message
box asking for the UserID.

As you can tell my Rookie-ness is showing. I am guessing this is why I
need to assign a variable rather than pull the number from a table.

Do I put this code on the Login form on the click event of the button that
opens the database? That will assign the public variable and then open
the
database.

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", "t_UserID")
End If
' Return the user ID
GetUser = strUserID
End Function

I am not even sure what questions to ask.

...and am not sure where to put this code.. CurrentDb.Execute "UPDATE
t_UserID Set UserID = " & Me.UserID,
dbFailOnError

Then I will have to figure out how to call a variable in code, which I have
not done before.

Thanks,

The learning continues.

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: Tuesday, October 23, 2012 2:09 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] Login form

Bill-

Your local table should have only one row and one field - and that field
can be the Primary Key. The Execute is simply running an SQL Update query
to stuff the selected login ID in that local table for later use. If the
ID is a number, then the command needs to be modified to drop the quotes:

CurrentDb.Execute "UPDATE ztblCurrentUser Set UserID = " & Me.UserID,
dbFailOnError

My original example was wrong - the command should update the local "ztbl"
table, not your master user table.

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: Monday, October 22, 2012 10:46 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

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%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 9:54 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 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>
<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: 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>
<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%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: 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>
<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>
<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: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>
<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%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 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>
<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>
<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>
<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>
<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%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>
<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>
<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>
<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>
<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]

------------------------------------

Yahoo! Groups Links

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