If you are new to Access, setting up workgroup security can be a little
intimidating. It would be the best solution, however.
Another way would be to create a table of users and the deparment codes they
belong to. If only certain users are able to look at the data, then only put
those users and their department codes in the table.
Then you will need to know who the logged on user is. Put this code in a
standard module:
Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
Public Function GetUserID()
' Gets the userid (5+2) of the current user.
' Put the following in the declarations section:
'Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
' modified 4/11/02 dbk set default userid for those not on network
'set default to xxx, changed 10 to 20 for length on 2 lines
Dim buffer As String * 20
Dim Length As Long
Dim lngresult As Long, userid As String
Length = 20
lngresult = GetUserNameA(buffer, Length)
If lngresult <> 0 Then
userid = Left(buffer, Length - 1)
Else
userid = "xxxxxxx"
End If
'MsgBox "USERID = " & userid, , "GetUserID()"
GetUserID = UCase(userid)
End Function
So now we know who is logged on and we can lookup their department.
Dim strUserID As String
Dim strDept As String
strUserID = GetUserID()
strDept = Nz(DLookup("[Department]", "tblUserDepts", "[UserID] = '" &
strUserId & "'"),"")
If Len(strDept) = 0 Then
MsgBox "You Do Not Have Permissions to this form"
Else
DoCmd.OpenForm "MyDepartmentForm", , , "[Department] = '" & strDept
& "'"
End If
Tasmania said:
What is the best practice in this situation? Should I use Workgroup security
or should I use some other approach? What would be any other approach? I
hope I am not asking too much? Since I am a novice Access user, please don't
be annoyed. Thanks.
--
thadi
:
The code I posted will show only the selected department when the form is
opened.
If you plan to use workgroup security, you could assign users to groups by
department, then set the filter based on the group of the user that is the
current user.
:
Thanks.
My combo is a text field and it works fine with the filtering. Thanks fir
the code.
Now how would I set up the security? If a time keeper logs in how would I
restrict them to see their specific dept data?
Thanks in advance for the response.
--
thadi
:
You would need to have the combo with all the departments on the form from
which you open the form you want to filter. Then when you open the form
Docmd.OpenForm "MyForm", , ,"[Department] = '" & Me.cboDepartment & "'"
The above is if the Department field in your table is a text field. If it
is numeric:
Docmd.OpenForm "MyForm", , ,"[Department] = " & Me.cboDepartment
:
Thanks alot for the response.
For the first part, I am using a combobox on the mainform where the user
will select the dept and it will take them to the data entry form for that
department only.
For the second part, I would like use Access Security where each timekeeper
will have a username and password with the WorkGroupInformationFile.
When database opens up, the user need to put in the userid/password, and
then it will take them to the mainform. In the mainform they will select
their department, and so on. How do I do that? Thanks again
--
thadi
:
First, the easy part.
Filter the form by department number so only that department's employees
will be included.
Now the hard part. That is, to know which department should be included.
Since I don't know enought about your applicatioin, I don't know if you are
using security or if you have any way of knowing who the user is and what
that user's deparment is. This is the part you will have to determine. The
question is "How do I know which department to filter on?"
If you can define how you will know that, I will be happy to help with the
coding of it.
:
I have a timekeeper database where one timekeeper from each department will
keep their own employee time. I need to restrict the view of data for each
department so each deprtment can see only their own peoples data. How can I
do that? At this point I am clueless since I am a novice Access User.
I appreciate all the replies.
