Log In Form (1 Viewer)

[TheSafetyGuy86]

Good Morning Forums,

I am working on an log in form that has a text box and a password box, both unbound. I am trying to set it up so that when a user types in their name, it searches for their record and compares the entered password against their actual password inside the database. I am having trouble getting the username textbox to update the current record. I am using a macro in the After Update field for the username textbox.

="[User] = " & Nz([Screen].[ActiveControl],0)

It does not return the selected record, but if I change the username to a combo box, it updates the record just fine. I don't want a combo box though because I don't want to list usernames to anyone that enters the database.

Any ideas?

Safety Guy

 

[pr2-eugin]

TheSafestGuy86, you might want to check how to use VBA, Macros can be a real pain at time. Specially in terms of flexibility of what needs to be achieved. If you are interested in learning we would be happy to walk you through. Good Luck.

 

[TheSafetyGuy86]

pr2-eugin,

I know how to use VBA and am not averse to it. I can write code, just never thought of how to do it for this specific type of thing. If you guys have an idea of what I should do, then I am all ears. I can work with something if I have the basics.

Safety Guy

 

[pr2-eugin]

Okay create three controls on an unbound form. Two text boxes, one button. The first text box (userNameTxt) is just to enter user name the second text box (passwordTxt) is to enter password (a password input mask would be ideal). Then the coding part comes on the Click of the button (validateUserBtn). Something like.

Private Sub validateUserBtn_Click()
    Dim tmpPass As String
    
    If Len(Me.userNameTxt & vbNullString) = 0 Or Len(Me.passwordTxt & vbNullString) = 0 Then
        MsgBox "User Name or Password is missing, please make sure you enter the two required fields", vbCritical
        Exit Sub
    End If
    
    tmpPass = Nz(DLookUp("passwordFieldName", "yourUserTableName", "userNameField = '" & Me.userNameTxt & "'"), "SomeGarbage")
    If tmpPass = "SomeGarbage" Then
        MsgBox "You entered an Invalid UserName. Please try again.", vbCritical
    ElseIf tmpPass <> Me.passwordTxt
        MsgBox "You entered an Invalid Password. Please try again.", vbCritical
    Else
        MsgBox "Congragulations, you have access now !", vbInformation
    End If
End Sub

Please change all field names and control names to match your design.

 

[TheSafetyGuy86]

pr2-eugin,

Great! Thanks. That was much simpler than I was going about it. I was about to start using the docmd.findrecord and then go about writing the code above in a different variant. In other words, having it validate against something else on the form and not having it look up something in the table. Not sure why I thought I had to do that having read through the code above, but either way I appreciate your help!

Safety Guy

 

[pr2-eugin]

Not a problem ! Good luck

 

[TheSafetyGuy86]

Alright, so the above worked great and is doing exactly what I want, but now I am trying to work out a minor thing in relations to the msg box. Is it possible to have the final message box, the one that welcomes them to the database, populate the name of the user? if so what code do I need to use? I tried inputting a Dlookup, but that caused the code to stop working. Maybe I input it wrong, but I am not sure.

Safety Guy

 

[pr2-eugin]

Here something like this should work.

Private Sub validateUserBtn_Click()
    Dim tmpPass As String
    
    If Len(Me.userNameTxt & vbNullString) = 0 Or Len(Me.passwordTxt & vbNullString) = 0 Then
        MsgBox "User Name or Password is missing, please make sure you enter the two required fields", vbCritical
        Exit Sub
    End If
    
    tmpPass = Nz(DLookUp("passwordFieldName", "yourUserTableName", "userNameField = '" & Me.userNameTxt & "'"), "SomeGarbage")
    If tmpPass = "SomeGarbage" Then
        MsgBox "You entered an Invalid UserName. Please try again.", vbCritical
    ElseIf tmpPass <> Me.passwordTxt
        MsgBox "You entered an Invalid Password. Please try again.", vbCritical
    Else
        MsgBox [B]"Congragulations [COLOR=Red]" & Me.userNameTxt & "[/COLOR], you have access now !",[/B] vbInformation
    End If
End Sub

 

[TheSafetyGuy86]

This is what I used. In the table that me.usernametxt is referencing, there is also a field that has the users first name. I am trying to reference the users name in order to make it a little more friendly.

Private Sub LogIn_Click()
Dim tmpPass As String

If Len(Me.usernametxt & vbNullString) = 0 Or Len(Me.passwordtxt & vbNullString) = 0 Then
MsgBox "User Name or Password is missing, please make sure you enter the two required fields", vbCritical
Exit Sub
End If

tmpPass = Nz(DLookup("password", "user", "user = '" & Me.usernametxt & "'"), "SomeGarbage")
If tmpPass = "SomeGarbage" Then
MsgBox "You entered an Invalid UserName. Please try again.", vbCritical
Else
If tmpPass <> Me.passwordtxt Then
MsgBox "You entered an Invalid Password. Please try again.", vbCritical
Else
MsgBox "Good day," & DLookup("trainer first name", "user", "user = '" & Me.usernametxt & "'") & " you now have access.", vbInformation, "Welcome"
DoCmd.Close
DoCmd.OpenForm ("selector")
End If
End If
End Sub

 

[pr2-eugin]

Okay, so is this working now? What is the problem? Please use Code Tags when posting VBA Code

 

[TheSafetyGuy86]

It's not working and I am sorry about the code thing. First time I have ever pasted code. Its telling me that there is a missing syntax error and the debug highlights the line with the final msgbox, but I have no clue what is missing from it.

 

[pr2-eugin]

Try,

Private Sub validateUserBtn_Click()
    Dim tmpPass As String
    
    If Len(Me.userNameTxt & vbNullString) = 0 Or Len(Me.passwordTxt & vbNullString) = 0 Then
        MsgBox "User Name or Password is missing, please make sure you enter the two required fields", vbCritical
        Exit Sub
    End If
    
    tmpPass = Nz(DLookUp("passwordFieldName", "yourUserTableName", "userNameField = '" & Me.userNameTxt & "'"), "SomeGarbage")
    If tmpPass = "SomeGarbage" Then
        MsgBox "You entered an Invalid UserName. Please try again.", vbCritical
    ElseIf tmpPass <> Me.passwordTxt Then
        MsgBox "You entered an Invalid Password. Please try again.", vbCritical
    Else
        MsgBox "Congragulations " & DLookup("[COLOR=Red][B][[/B][/COLOR]trainer first name[COLOR=Red][B]][/B][/COLOR]", "[COLOR=Red][B][[/B][/COLOR]user[COLOR=Red][B]][/B][/COLOR]", "[COLOR=Red][B][[/B][/COLOR]user[COLOR=Red][B]][/B][/COLOR] = '" & Me.usernametxt & "'") & ", you have access now !", vbInformation
    End If
End Sub

 

[TheSafetyGuy86]

It worked! Thanks again for your help!

 

[pr2-eugin]

Not a problem ! Good luck.