Password on a form

[Andy Roberts]

I have a split DB with slightly different front ends on different PCs. One
of my forms is about the staff and there are only 2 people I want to be able
to seethis form. I can control this to a certain extent in each PCs front
end, but sometimes people swap PCs for logistical reasons.

Can I have a form open and ask for a password or have a password field on a
form which when entered makes all the controls visible?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Klatuu]

There is a way you can do it without even having to ask for a password.

Copy this code into a module by itself:
Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
Public Function GetUserID()

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
GetUserID = UCase(userid)

End Function
****************
I call mine ModGetUserAPI

Create two tables to use to determine who is allowed access to what forms:
tblUserList
UserID - AutoNumber primary key
UserName - Text - The use's network login name (index this field)

tblUserRights (primary key could be UserID and FormName
UserID - Foreign key to tblUserList
FormName - Text, Name of a form you want protected
HasRights - Yes/No

Now, in the Form Open event, check to see if the user has rights or not and
cancel the open if they don't:

Private Sub Form_Open(Cancel As Integer)
Dim lngCurrentUser As Long

lngCurrentUser = Nz(DLookup("[UserID]", "tblUserList", "[UserName] = """
& _
getusername & """"),0)
If lngCurrentUser = 0 Then
MsgBox "Unknown User"
Cancel = True
Else
If Not Nz(DLookup("[HasRights]", "tblUserRights", "[UserId] = " & _
lngCurrentUser),False) Then
MsgBox "You Do Not have permission to use this form"
Cancel = True
End If
End Sub

 

[Andy Roberts]

This sound perfect...Could I implement it on a subform and on a page within
a tab control?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

There is a way you can do it without even having to ask for a password.

Copy this code into a module by itself:
Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As
String,
ByRef intN As Long) As Long
Public Function GetUserID()

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
GetUserID = UCase(userid)

End Function
****************
I call mine ModGetUserAPI

Create two tables to use to determine who is allowed access to what forms:
tblUserList
UserID - AutoNumber primary key
UserName - Text - The use's network login name (index this field)

tblUserRights (primary key could be UserID and FormName
UserID - Foreign key to tblUserList
FormName - Text, Name of a form you want protected
HasRights - Yes/No

Now, in the Form Open event, check to see if the user has rights or not
and
cancel the open if they don't:

Private Sub Form_Open(Cancel As Integer)
Dim lngCurrentUser As Long

lngCurrentUser = Nz(DLookup("[UserID]", "tblUserList", "[UserName] =
"""
& _
getusername & """"),0)
If lngCurrentUser = 0 Then
MsgBox "Unknown User"
Cancel = True
Else
If Not Nz(DLookup("[HasRights]", "tblUserRights", "[UserId] = " & _
lngCurrentUser),False) Then
MsgBox "You Do Not have permission to use this form"
Cancel = True
End If
End Sub
--
Dave Hargis, Microsoft Access MVP

I have a split DB with slightly different front ends on different PCs.
One
of my forms is about the staff and there are only 2 people I want to be
able
to seethis form. I can control this to a certain extent in each PCs
front
end, but sometimes people swap PCs for logistical reasons.

Can I have a form open and ask for a password or have a password field on
a
form which when entered makes all the controls visible?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Klatuu]

Yes you could; however, I would do it a bit differently.
I would still use the Load event of the main form and if the user did not
have permissions, I would make the subform control and/or tab invisible.

The reason to use the Load event in this situation rather than the open
event is that all the form's objects may not be instansiated in the Open
event. In other words, it may be too soon. The reason to use the Open event
if you want to close the form is because the Open event can be canceled
before the form fully loads.

Now, if you do this, the object you make invisible is not the form name of
the form being used as the subform, but the name of the subform control on
the main form.
--
Dave Hargis, Microsoft Access MVP

This sound perfect...Could I implement it on a subform and on a page within
a tab control?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

There is a way you can do it without even having to ask for a password.

Copy this code into a module by itself:
Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As
String,
ByRef intN As Long) As Long
Public Function GetUserID()

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
GetUserID = UCase(userid)

End Function
****************
I call mine ModGetUserAPI

Create two tables to use to determine who is allowed access to what forms:
tblUserList
UserID - AutoNumber primary key
UserName - Text - The use's network login name (index this field)

tblUserRights (primary key could be UserID and FormName
UserID - Foreign key to tblUserList
FormName - Text, Name of a form you want protected
HasRights - Yes/No

Now, in the Form Open event, check to see if the user has rights or not
and
cancel the open if they don't:

Private Sub Form_Open(Cancel As Integer)
Dim lngCurrentUser As Long

lngCurrentUser = Nz(DLookup("[UserID]", "tblUserList", "[UserName] =
"""
& _
getusername & """"),0)
If lngCurrentUser = 0 Then
MsgBox "Unknown User"
Cancel = True
Else
If Not Nz(DLookup("[HasRights]", "tblUserRights", "[UserId] = " & _
lngCurrentUser),False) Then
MsgBox "You Do Not have permission to use this form"
Cancel = True
End If
End Sub
--
Dave Hargis, Microsoft Access MVP

I have a split DB with slightly different front ends on different PCs.
One
of my forms is about the staff and there are only 2 people I want to be
able
to seethis form. I can control this to a certain extent in each PCs
front
end, but sometimes people swap PCs for logistical reasons.

Can I have a form open and ask for a password or have a password field on
a
form which when entered makes all the controls visible?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007