Lookup Table For Logon Form

[ThunderBolt]

I am developing a simple framework for a logon script.

I have a simple table with two fields: Username and Password. I have a form based on the table with the two fields and a button entitled 'logon'. Can anyone tell me how to go about using an OnClick event for the button to reference values entered in the form with those in the table. My feeling is that I could could create a query when the button is clicked that refernces the values???

Some advice/feedback much appreciated.

 

[ThunderBolt]

progress

In this example

=DLookUp("[Title]", "Employees", "[LastName] = 'Callahan'")

the statement shows how to look up a value from a table with criteria 'Callahan'

Can the criteria be a value in a text box, on a form. If it can, how would you go about doing this in code.

 

[jfgambit]

Here is a really simple example...

Username: Password
gambit:cards
wolverine:claws
cyclops:glasses

This sample uses the Dlookup Function you are asking about.

HTH

 

[ThunderBolt]

Thanks a lot, that was exactly what I hoped to achieve!

 

[Oldsoftboss]

You need to add error handling if the password is incorrect...

Private Sub cmdLogon_Click()
On Error GoTo Err_LogOn

'Look-Up password from tblUsers based on Username and open new form on validation

Dim rsPw As String

rsPw = DLookup("[Password]", "tblUsers", "[Username] = '" & Me.txtUser & "'")

If rsPw = Me.txtPass Then
DoCmd.OpenForm "frmYouIn", , , , , acDialog
Else
MsgBox "No way am I letting you in!", vbOKOnly, "Incorrect Password"
Me.txtUser = ""
Me.txtPass = ""
Me.txtUser.SetFocus
End If

Err_LogOn:
Select Case Err.Number
Case 94 'Invalid use of Null
MsgBox "No way am I letting you in!", vbOKOnly, "Incorrect Password"
Me.txtUser = ""
Me.txtPass = ""
Me.txtUser.SetFocus
Case Else
MsgBox Err.Description
End Select

End Sub

 

[Oldsoftboss]

And then you can could have some fun..

Option Compare Database
Dim Counter As Integer

Private Sub cmdLogon_Click()
On Error GoTo Err_LogOn

'Look-Up password from tblUsers based on Username and open new form on validation

Dim rsPw As String

rsPw = DLookup("[Password]", "tblUsers", "[Username] = '" & Me.txtUser & "'")

If rsPw = Me.txtPass Then
Counter = 0
DoCmd.OpenForm "frmYouIn", , , , , acDialog
Else
Call WrongPassword
End If

Err_LogOn:
Select Case Err.Number
Case 94 'Invalid use of Null
Call WrongPassword
Case Else
MsgBox Err.Description
End Select

End Sub

Function WrongPassword()

Counter = Counter + 1

If Counter < 3 Then
MsgBox "No way am I letting you in!. You only get 3 goes and this is number " & Counter & " ! ", vbExclamation, "Incorrect Password"
Me.txtUser = ""
Me.txtPass = ""
Me.txtUser.SetFocus
Else
MsgBox "Your Stuffed. Stop trying to hack in here!!", vbCritical, "Get Some Help"
DoCmd.Close acForm, Me.Name
End If

End Function

 

[jfgambit]

As I said:

Here is a really simple example...

 

[Oldsoftboss]

Sooorrry. Nothing a bit of cut and paste wont fix.
Hopefully it will lead to a better understanding of VBA

Dave

 

[ThunderBolt]

Thankyou both for your help. If I have time I may add the extra code to make the logon more advanced. I had thought about a counter and your code shows me how to implement this so that is helpful.

Regards,
Thunderbolt

 

[ThunderBolt]

I just remembered. I will be making a counter, using a recursive procedure. By demonstrating a recursive procedure I hope to gain extra credit for my system (A-Level project).

 

[Oldsoftboss]

Can you tell me more about your schooling, what qualification you will gain, how long the course is etc.....
I have never been taught anything about Access, all learnt through trial and error and hanging around this forum.

My latest "effort" at a logon demo is here http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=60084

Cheers, interested in your thoughts.

Dave

 

[ThunderBolt]

Im doing an A-Level in Comuputing. I am using access to do my project, but i am pushing it, as accesss is not renound for its use of programming languages/ techniques. I have to use large amounts of visual basic code for my project to be acceptable. I advise you to take a course in computing rather that I.T as its the code that has the power. Borland Delphi is a powerful programme, and we are encouraged to use that. I am in full time education at the momet but im sure you could do an AS Leve or A level (if you are from england). If not night classes may be useful.