Bill-
That code goes in a Standard module - one you can see in the Database
Window or Nav Pane. If you have the login form bound to your
ztblCurrentUser and the combo box bound to the single field, then you
don't need the UPDATE command. The form will take care of updating the
table when the user select the ID. Here's a corrected version of the code:
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
I had the wrong table name in the original example.
With this code in place, simply call GetUser() from anywhere in a query or
code. For example, in a query you can do:
SELECT *
FROM SomeTable
WHERE UserID = GetUser()
On the query grid, you would put the UserID from the table in the Fields
list and put GetUser() in the Criteria.
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: agent1of6 <Bill.Singer@at-group.net>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Tuesday, October 23, 2012 11:40 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Login form
John V (forgot this the first time)
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
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...>
wrote:
>
> 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@...>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Monday, October 22, 2012 10:46 PM
> To: <MS_Access_Professionals@yahoogroups.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@yahoogroups.com] On Behalf Of John
>Viescas
> Sent: Thursday, October 18, 2012 9:54 AM
> To: MS_Access_Professionals@yahoogroups.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 ©
>
> 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@...
> <mailto:Bill.Singer%40at-group.net> >
> Reply-To: <MS_Access_Professionals@yahoogroups.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> >
> 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@yahoogroups.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>
> 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@...
> <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: 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> >
> 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@yahoogroups.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>
> 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@...
> <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: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> >
> 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@yahoogroups.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>
> 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@...
> <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>
> 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
>
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (167) |
Tidak ada komentar:
Posting Komentar