?
Solved

How track who is logged in and save specific settings for a user?

Posted on 2011-10-25
6
Medium Priority
?
290 Views
Last Modified: 2012-05-12
When my application opens the user logs in. The values username and password are stored in a table called tblusersettings. If there is a match it allows opening the main form. How do I know who logged in so while in different forms I can load specific data or settings?
0
Comment
Question by:seanlhall
6 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37026340
If what you are trying to do is limit users access to certain forms or controls on a form, then you need to create UserRoles, and assign each user to one or more roles.

Then when a form opens, you can enable/disable buttons based on their roles, which would prevent them from accessing some forms.  Once you open a form, you might use the Form_Load or Form_Current event to test to determine whether the user should be able to see (visible = yes/no) or edit (locked = yes/no) a particular control based upon their permissions.

For example, the people in operations don't need to see peoples salaries or SSNs, but those in human resources do (or at least some of them do).
0
 
LVL 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 37026398

 One of the common ways to do this is through a hidden form, storing the username in a control on it.  You can then refer to it at any time.

 Another is to simply fetch the username every time you need it; the Windows API call is quite quick.

 As for user preferences, I have a pair of routines to set/get (code below) the preferences based on the object name.

  The preference is nothing more then a string, which I've formatted in a specific way using delimiters.  I then have routines to build/parse this string and react accordingly.  For example, here's some code from a Query By Form, which saves the values of the filter controls and the state of the filter on the form:

    lngUserID = Forms![frmGlobal]![UserId]
    Set frmQBFForm = Forms![frmTractorSummarySF_QBF]
    strFieldValues = ""

    If Not IsNull(frmQBFForm![txtStopDateTime]) Then strFieldValues = strFieldValues & ":txtStopDateTime:" & frmQBFForm![txtStopDateTime]
    If Not IsNull(frmQBFForm![cboDriver]) Then strFieldValues = strFieldValues & ":cboDriver:" & frmQBFForm![cboDriver]
    If Not IsNull(frmQBFForm![cboTractor]) Then strFieldValues = strFieldValues & ":cboTractor:" & frmQBFForm![cboTractor]
    If Not IsNull(frmQBFForm![cboTrailer]) Then strFieldValues = strFieldValues & ":cboTrailer:" & frmQBFForm![cboTrailer]
    If Not IsNull(frmQBFForm![txtLastPostalCode]) Then strFieldValues = strFieldValues & ":txtLastPostalCode:" & frmQBFForm![txtLastPostalCode]
    If Not IsNull(frmQBFForm![cboLastState]) Then strFieldValues = strFieldValues & ":cboLastState:" & frmQBFForm![cboLastState]
    If Not IsNull(frmQBFForm![txtLastCity]) Then strFieldValues = strFieldValues & ":txtLastCity:" & frmQBFForm![txtLastCity]
    If Not IsNull(frmQBFForm![txtNextPostalCode]) Then strFieldValues = strFieldValues & ":txtNextPostalCode:" & frmQBFForm![txtNextPostalCode]
    If Not IsNull(frmQBFForm![cboNextState]) Then strFieldValues = strFieldValues & ":cboNextState:" & frmQBFForm![cboNextState]
    If Not IsNull(frmQBFForm![txtNextCity]) Then strFieldValues = strFieldValues & ":txtNextCity:" & frmQBFForm![txtNextCity]
    If Not IsNull(frmQBFForm![cboLastStopType]) Then strFieldValues = strFieldValues & ":cboLastStopType:" & frmQBFForm![cboLastStopType]
    If Not IsNull(frmQBFForm![cboNextStopType]) Then strFieldValues = strFieldValues & ":cboNextStopType:" & frmQBFForm![cboNextStopType]
    If Not IsNull(frmQBFForm![txtOrderID]) Then strFieldValues = strFieldValues & ":txtOrderID:" & frmQBFForm![txtOrderID]
   
    strFieldValues = Mid$(strFieldValues, 2)
    intRet = SetUserPreferences(lngUserID, "frmTractorSummarySF_QBF:FieldValuesA", strFieldValues)
   
    If Me![btnRemoveFilter].Enabled = True Then
      strFieldValues = "ON"
    Else
      strFieldValues = "OFF"
    End If
    intRet = SetUserPreferences(lngUserID, "frmTractorSummarySF_QBF:Filter", strFieldValues)


Jim.


Function SetUserPreferences(lngUserID, strObjectName, strValue As String) As Integer

  Dim wrk As Workspace
  Dim dbCurrent As Database
  Dim dbRemote As Database
  Dim tdfAttached As TableDef
  Dim strPath As String
  Dim rst1 As Recordset
  Dim rst1Field As Field

'
' First, get the path to the MDB for the attached table.
'
  Set wrk = DBEngine.Workspaces(0)
  Set dbCurrent = wrk.Databases(0)
  Set tdfAttached = dbCurrent.TableDefs("tblUserPreferences")
 
  strPath = tdfAttached.Connect
  strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
 
  Set dbRemote = wrk.OpenDatabase(strPath, False, False)
 
'
'  First, find the postal code.  Need to know the country.
'
  Set rst1 = dbRemote.OpenRecordset("tblUserPreferences", DB_OPEN_TABLE)
  rst1.index = "PrimaryKey"
  rst1.Seek "=", lngUserID, strObjectName

  If rst1.NoMatch Then
    rst1.AddNew
    rst1![UserId] = lngUserID
    rst1![ObjectName] = strObjectName
  Else
    rst1.Edit
  End If
  rst1![Value] = strValue
  rst1.Update

  rst1.Close
  Set rst1 = Nothing
  dbRemote.Close
  Set dbRemote = Nothing
  Set tdfAttached = Nothing
  Set dbCurrent = Nothing
  Set wrk = Nothing


End Function

Function GetUserPreferences(lngUserID As Long, strObjectName As String) As Variant

  Dim wrk As Workspace
  Dim dbCurrent As Database
  Dim dbRemote As Database
  Dim tdfAttached As TableDef
  Dim strPath As String
  Dim rst1 As Recordset
  Dim rst1Field As Field

'
' First, get the path to the MDB for the attached table.
'
  Set wrk = DBEngine.Workspaces(0)
  Set dbCurrent = wrk.Databases(0)
  Set tdfAttached = dbCurrent.TableDefs("tblUserPreferences")
 
  strPath = tdfAttached.Connect
  strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
 
  Set dbRemote = wrk.OpenDatabase(strPath, False, False)
 
'
'  First, find the postal code.  Need to know the country.
'
  Set rst1 = dbRemote.OpenRecordset("tblUserPreferences", DB_OPEN_TABLE)
  rst1.index = "PrimaryKey"
  rst1.Seek "=", lngUserID, strObjectName

  If rst1.NoMatch Then
    GetUserPreferences = Null
  Else
    GetUserPreferences = rst1![Value]
  End If

  rst1.Close
  Set rst1 = Nothing
  dbRemote.Close
  Set dbRemote = Nothing
  Set tdfAttached = Nothing
  Set dbCurrent = Nothing
  Set wrk = Nothing


End Function
0
 

Author Comment

by:seanlhall
ID: 37026432
For example the user name "sean" logs in. How do I carry the user name "sean" throughout all the forms. Do I store it in a temporary table or is there some other way?

I
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 49

Expert Comment

by:Dale Fye
ID: 37026453
see JD's 1st and 2nd two lines.

You can also save the value to a global variable, but I would recommend against that as they can loose the value if you encounter an unhandled error.  Personally, I prefer the API call.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37026506
^
...In other words, depending on your exact requirements, this may not be what you would consider "easy" if you are new to coding and/or MS Access Database design.
;-)

I've done a few simple samples illustrating very simple ways of doing this.
However, you still have to have a good working knowledge of coding and/or MS Access Database design.

I'll try to post them later if you are interested.

JeffCoachman
0
 

Author Comment

by:seanlhall
ID: 37026618
Jeff, I understand Jim's post, and I am working with it to see what I can do with it.I would still be interested in what you have. Thanks.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question