multiple name selection in VB

[tps131]

I have a simple question about VB coding for an access application...I had it working with one user but cant get it with multiple users...I want to be able to check a table and if the users name is in this table I want to unlock a button on the application..here is the code that works with one user...

Private Sub Form_Load()
If UserNem = "Tim" Then
[Command12].Enabled = True
Else
[Command12].Enabled = False
End If

thanks

 

[jebenson]

This solution assumes you already know how to connect to your database and retrieve a recordset. To check if a username is in a table, do this:

Dim rsUsers as New Recordset

rsUsers.Open "SELECT UserName FROM tblUsers WHERE username='" & UserNem & "'", Connection

If rs.RecordCount=1 Then 'username found
[Command12].Enabled = True
Else 'no match found, or more than one match found
[Command12].Enabled = False
End If

Notes: UserName is the name of the field in the table that holds the usernames, tblUsers is the name of the users table, and Connection is the name of the connection object used to access the database. Substitute the appropriate names/values in place of the examples I provided. UserNem is the variable listed in your code from above.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[zemp]

Becareful that you have the correct cursor type when using .recordcount. Not all cursor types will return a recordcount and you could end up with a false condition even when the record exists. I would use this,

rsUsers.Open "SELECT UserName FROM tblUsers WHERE username='" & UserNem & "'", Connection, adOpenStatic, adLockOptimistic, adCmdText

adOpenStatic is the cursor type.


zemp

 

[BarryBolin]

An other option for this is to set up a "Security Check" process. Link this to the "On Open" event for each form.
It involves a table that can be as big or as small as you want. the code is as follows.
WARNING THIS IS ROUGH. I only had about 1 hour to scratch this up.

this tbl_security has 5 fields in it.
User_ID : Text..
Security_Level : byte (Unles more than 256 levels required)
Last_use : date/Time
File_Key : Number (Not important used for multiple mailmerges from multiple users) faster.
Current_Status : Text (whether user is in database now or not. Handy to know.)

Function Check_security() As Integer
Dim RS_User_ID As String
Dim Rs_Usr As DAO.Recordset
Dim Security_Level As Byte

RS_User_ID = CurrentUser
Set Rs_Usr = CurrentDb.OpenRecordset("SELECT * FROM Tbl_Security where User_ID = '" & RS_User_ID & "' ;")
'MsgBox Rs_Usr!User_ID & " :: " & Rs_Usr!Current_Status
If Rs_Usr.RecordCount < 1 Then
Security_Level = Add_User(Rs_Usr)
Else
Security_Level = Rs_Usr!Security_Level
With Rs_Usr
.Edit
!Current_Status = "Active"
.Update
End With
End If
Check_security = Security_Level
Rs_Usr.Close
Set Rs_Usr = Nothing
End Function

This is a simple function to allow all users access to database with readonly level security.
I do not have to explicitly give each user an access level. I just update it after they can not do something.

Function Add_User(Rs_Usr As DAO.Recordset)

' MsgBox "In ADD USER"
With Rs_Usr
.AddNew
!User_ID = CurrentUser
!Security_Level = 6
!Last_use = Date
!Current_Status = "Active"
.Update
End With
Add_User = 6
End Function

This following is used when the database main menu is closed.

Sub Log_Access()
Dim RS_User_ID As String
Dim Rs_Usr As DAO.Recordset
Dim Rs_Usr_Times As DAO.Recordset

RS_User_ID = CurrentUser

Set Rs_Usr = CurrentDb.OpenRecordset("SELECT * FROM Tbl_Security where User_ID = '" & RS_User_ID & "' ;")
Set Rs_Usr_Times = CurrentDb.OpenRecordset("SELECT * FROM Tbl_User_History where User_ID = '" & RS_User_ID & "' ;")

If Rs_Usr.RecordCount <> 1 Then
MsgBox "Audit log failed to work", , "failed"
Else
With Rs_Usr_Times
.AddNew
!User_ID = Rs_Usr!User_ID
!Security_Level = Rs_Usr!Security_Level
!start = Rs_Usr!Last_use
!End = Now()
.Update
End With
With Rs_Usr
.Edit
!Current_Status = "Not Active"
.Update
End With
End If
Rs_Usr.Close
Rs_Usr_Times.Close
Set Rs_Usr = Nothing
Set Rs_Usr_Times = Nothing

End Sub
This just keeps a log in another table (Tbl_User_History) of user acces times, updates a uuser in the security table to in-active and kicks off clean-ups.

can be bypassed but is more of a Stop accidents than bullet proof.

 

[BarryBolin]

Have to change from Tech user to Programmer.

 

[BarryBolin]

forgot to mention "CurrentUser" is the users "system/login id" so that it can be linked back to each person. without worrying about different users fudging access.