Using a Username as the parameter to query 2

[tinmar]

Hi,

I have created a basic login form (below) with the person needing to enter a username and password

So when the user enters the correct username and password, i need to know what i can do to make a [parameter] in one of the querys' to use the username that was entered

Private Sub Command6_Click()
username.SetFocus
If username = "user1" And password = "user1" Then
DoCmd.Close
DoCmd.OpenForm "test"
ElseIf username = "user2" And password = "user2" Then
DoCmd.Close
DoCmd.OpenForm "test2"
Else
MsgBox "wrong password"
End If
End Sub

 

[MajP]

I bind the login to a table that holds the username. Then you can use a dlookup or likely just do a join to the username table.

 

[Andrzejek]

Or you can have a (Public?) variable and keep in it your user's username.
And use this variable in your queries.

Have fun.

---- Andy

 

[MajP]

If you do decided to store it in a public variable, then you will have to build a function to return that variable in order to use in in a query.

public SomePublicVariable as SomeDatatype

public Function GetSomeVariable() as SomeDataType
GetSomeVariable = SomePublicVariable
end function

 

[tinmar]

Hmmm, thanks for the above, but do any of you have any samples or sites i can look up , so that if the username and password are correct, then bind the user name to query in the form that will open if the username and password are correct.

thanks

 

[cneill]

Hi,

this should do the trick, replace my field names with your fields names and create a name and password table

'Check to see if data is entered into the UserName combo box

Dim StrUser As String
StrUser = MemberID

Dim StrUserAccess As Integer
StrUserAccess = DatabaseAccess


If IsNull(Me.MemberID) Or Me.MemberID = "" Then
MsgBox "Please Select Your Member Name.", vbOKOnly, "Required Data"
Me.MemberID.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "Please enter Your Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in TblAccessToDatabase to see if this matches value chosen in combo box

DoCmd.Hourglass True
If Me.txtPassword.value = DLookup("MemberPassword", "TblDatabaseMembers", "MemberID=" & StrUser) Then
'Close logon form and open Option screen

Select Case StrUserAccess
Case 1
Forms![FrmLoginToDatabase].Visible = False
DoCmd.OpenForm "frmSchemes", acNormal

End Select
DoCmd.Hourglass False

Else
MsgBox "Your Password is Invalid. Please Try Again", vbOKOnly, "Invalid Password!"
Me.txtPassword.SetFocus
DoCmd.Hourglass False
Exit Sub

End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You have have tried three time to Access the KAM Database. Please contact .....your details....", vbCritical, "Restricted Access!"
Application.Quit
End If
DoCmd.Hourglass False

End Sub

 

[cneill]

sorry
you need to add this if you want to limit the number of login attempts

Option Compare Database
Option Explicit
Private intLogonAttempts As Integer

 

[hmlhml]

Remember that if people will expect to log off, and another person log on, without closing the database between, then you will need to reinitialise the attempts counter.

I personally dislike storing passwords because I feel (1) that none of the applications I use is really secure enough to trust with someone's password (Access can be difficult to set up in such a way that no one can ever get their sticky paws on the tables); and (2) people teld to reuse their passwords, and I don't think they should be entrusting me with the password they use for logging on to the local domain at work/their bank/hotmail etc.. (yes, I know it's their stupidity if they reuse a vital password, but we should work round other people's habits). My solution is to take the text in the password box and mangle it into a hash-key that contains a lot less information, so that if someone looks at the value in the table, they still cannot reconstruct the original password. The downside is that you lose some security, so it's a trade-off according to what's most important to you.