Page 1 of 4 123 ... LastLast
Results 1 to 15 of 47

Thread: User Login Name

  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: User Login Name

    The db, see attached, has security set up throughout it. I have the users log in with a user name and password. I need to have this user name stored so that if the user clicks the command button "New Task" on the frmMainMenu, their user name will be populated in the User_Name text box field on that form.

    The issue is that, when the form is then opened by someone else, THE ORIGINAL USER NAME, THE PERSON WHO ORIGINALLY STARTED THE TASK REMAINS THE SAME. The original name CANNOT CHANGE, this is for 'accountability' or 'blaming' purposes.

    Thanks,

    Bryan
    Attached Files Attached Files

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I've heard of people doing similar methods to yours, who store the user's name on a "hidden" form on start up and then reference that.

    Typically, I do something a little different as far as user verification goes. I call the username to get the user login. Then ask for their password at the startup of the database to verify it's the user. Then I can use the same call throughout the database to get the username (which I can then reference on a table to get the user's name,email, whatever).

    Others may suggest better methods.
    Me.Geek = True

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by nckdryr
    I've heard of people doing similar methods to yours, who store the user's name on a "hidden" form on start up and then reference that.

    Typically, I do something a little different as far as user verification goes. I call the username to get the user login. Then ask for their password at the startup of the database to verify it's the user. Then I can use the same call throughout the database to get the username (which I can then reference on a table to get the user's name,email, whatever).

    Others may suggest better methods.

    nckdryr,

    Yeah, the db is programmed to 'call' the user name that is logged into the machine, since it is government and done through CAC cards, it is easy.

    What I'm trying to do is make that user name a "stored value" in the User_Name txtbox field on the form, frmTask. I also have to create an audit log for the entire db that will log all actions taken by the users.

    All I'm trying to do right now is to get the user who is currently logged in, to have their name stored in that txtbox field, User_Name, in the table, tblTask when they click on "New Task" from the main menu.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    User Login

    Try this. It works pretty good. Just add the users login to the admin table (for permissions if you want to do that). Import the admin table, modules into your db. Look at the routines to get the username and permissions in the form. Also, you can use the Getuser() function as a column in a query or SQL string as well as in the forms and it will populate that column with the users login (this works very well).
    Attached Files Attached Files
    Last edited by pkstormy; 05-18-07 at 10:30.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Oct 2003
    Posts
    103
    Quote Originally Posted by nckdryr
    I've heard of people doing similar methods to yours, who store the user's name on a "hidden" form on start up and then reference that.

    Typically, I do something a little different as far as user verification goes. I call the username to get the user login. Then ask for their password at the startup of the database to verify it's the user. Then I can use the same call throughout the database to get the username (which I can then reference on a table to get the user's name,email, whatever).

    Others may suggest better methods.
    I won't get into how to login, I've used both of these approaches depending on the network structure.

    However two things to consider:
    1. Look to see if the field has already been completed.
    If isnull(fieldname) then
    'enter the username here
    else
    'do nothing
    end if

    2. If you want to keep track of what has been done/changed, then I recommend setting up a transaction history table. Have a unique recordid field for each transaction, a field to cross-reference the transaction to the table you are working with, and record any data in a text/memo field - I have "holder fields" on my form, but I think you can use a tag.

    One such case is if the user changes the queue that a document is assigned to I do the following:
    strsql = "INSERT INTO [tbldocumenthistory] ([masterdocumentno],[user], [fieldchanged],[oldfieldvalue],[newfieldvalue],[note], [date&time]) values (" & Me.MasterDocumentNumber & ",'" & UserName & "','" & FieldChanged & "','" & Me.MPOldFieldValue & "','" & Me.CurrentQueueName & "','" & Note & "','" & dateval & "');"
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL strsql
    DoCmd.SetWarnings (True)

    A true programmer could do something more efficient I'm sure, but this get's the trick done.

    This way, I can look at the document history (I have a form for that), where I can see what was done, by whom, and when. In this case, I also use a do while loop and an inputbox (I'll spare you the code) to force the user to make a notation about why the queue was changed,

    Good luck.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Two techniques I have used for creating an audit trail:

    1) If I want to track changes to one table or a set of simply related tables, I create an audit table that has the same fields as the original plus change ID. When the user changes an existing record, I prompt the user for a reason. Then I write the entire record in it's original state to the audit table before updating the new data to the live table. Since my original tables have fields to track username, date/time and reason for change to show the audit history, I simply pull all the records from the audit table plus the current record and sort by date - this shows the progression of the data from original record to current record.

    2) If the data has complicated relationships and/or is simply too large to represent in one table, I have an audit table with username, date/time, changeID, reason and a memo field. On each form, when a change is committed, I have a routine that creates a block of text that describes the change. For example (an actual live example!):

    From 'MPCA Database Application'
    For Tool 104-36:
    OQ Status changed from 'OQ Required' to 'OQ Completed',
    OQ VPN changed from '' to '03-019-VA',
    OQ Date changed from '' to '03/17/2003',
    PQ Status changed from 'PQ Required' to 'PQ Completed',
    PQ VPN changed from '' to '104-36-PQ1',
    PQ Date changed from '' to '02/09/2004',
    Number of required PQs changed from '0' to '3',
    Number of completed PQs changed from '0' to '3',
    Tool marked as Approved

    The second option looks nicer, but from a practical standpoint, is more labor intensive if you need to actually construct the data history. FYI, each field had the user friendly name in the tag; a simple routine loops through the dirty fields and assembles the text. It can be done with a bound form, but is easier with an unbound form.


    As for user identification, I grab the username of the user that is logged in - this is pretty secure, especially is password protected screen savers are required. This at least guarentees the correct user was there to log in. When the user attempts a change, I promt for an electronic signature (fancy term for a password) to continue, that must match the logged in user. Add in an application timeout and this is sufficiently secure even for US Gov CRF Part 11.

    A while back, I took the various methods of obtaining the username from the registry and combined them into one fairly clean set of procedures so that you could call fnUserName in any Windows environment from 95 to XP, even a Novell login, and get the correct value. That, along with other API snippits, can be found HERE.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    A technique i've used in the poast is to write the SQL to an audit log, then if a table is crashed you run the SQL again

    it does take a bit of processing in background, but there is no dicking about each line in the audit log is a fully formed bit of SQL. Ive written it to text file on another drive, another table in the same db, another table in another db.

    when the system crashed and we had to re do a backup is was a piece of proverbial........ find the date/time the backup was taken, and run the SQL from that time on.

    It was originally done so that a JET db could be kept synched with other JET DB's, then MySQL, then something remote.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Aug 2006
    Posts
    559
    Everyone,

    Great ideas for obtaining the user name. However, I'm trying to get the user who is currently logged in, to have their name be populated in the form, frmTask, in the User_Name text field when they click on "New Task" from the main menu. I already have the user name being called from the computer upon them accessing the shortcut for the db.

    So the issue is to get that user name to populate the "User_Name" field in the form, frmTask, and NOT be changed if someone opens it. That, the not changing, I believe can be done with some thing like an

    1. Look to see if the field has already been completed.
    If isnull(fieldname) then
    'enter the username here
    else
    'do nothing
    end if

    **courtesy of Jdostie

    User_Name is Null
    Then populate with =f

  9. #9
    Join Date
    Aug 2006
    Posts
    559
    Double-posted, darned government networks!
    Last edited by Grafixx01; 05-18-07 at 10:02. Reason: Double-posted

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    UserName

    If you are really interested in a good method to get the username who logged in and populate it to a form, download the sample I previously supplied above. It's worth a thousand words. Try it. You won't be dissappointed! It's the best method I've ever found and I've been using it for years now.
    Last edited by pkstormy; 05-18-07 at 10:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    If you are really interested in a good method to get the username who logged in and populate it to a form, download the sample I previously supplied above. It's worth a thousand words. Try it. You won't be dissappointed! It's the best method I've ever found and I've been using it for years now.

    Ok, I'm going to try that and see what happens.

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Sample

    There are many things the sample demonstrates:

    1. How to get rid of the caption bar on a form.
    2. How to set up permissions of a user login table (dbo_AdminTable) so users don't have to enter a password (ie. isAdmin function).
    3. How to set up a MainFormBackGround and MainMenu form to essentially lock the menu bar at the top so users are forced to click an "exit" button on the form and don't have access to the top menus.
    4. How to utilize the getuser() function to retrieve the user login name.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Aug 2006
    Posts
    559
    Ok, I tried to user the module that was in that db, inserted it into mine on that text box field, that didn't work.

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You need to import ALL the modules into your db! Also import the GetUser form and then simply open it to try it out. You'll notice the login is returned (again, providing you've imported ALL the modules). Import the dbo_AdminTable if you want to try out the permissions. If you don't import ALL the modules, it won't work! Make sure you also debug/compile!

    Try just opening the GetUserSample mdb file itself without importing into your db. I've never had this not work for anyone.
    Last edited by pkstormy; 05-18-07 at 11:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Aug 2006
    Posts
    559
    Paul,

    The db that I posted, which is the one I am doing, already calls the network user name upon login to the db. I'm just now trying to figure out how to put that name into the "User_Name" text box on the form, frmTask, when the user clicks on "new task" from the main menu. Then make a statement in the code of that box that says like, 'if populated' then leave alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •