PDA

View Full Version : HELP WITH VBA AND SQL MESS



walawelo
11-14-2008, 03:00 AM
i have got a table called users with two text fields user_name and password and then i have a form that has 2 buttons 1 is login and the other is comand12 which isnt visible i want the user to enter his user name and password and if they do match then command12.visible=true
so i tried this
Private Sub Command4_Click()
Dim User_Name As String
Dim Password As String
Dim strSQL As String
Dim pass As String
Dim Password1 As String
Dim rsTemp As Recordset
Dim db As DAO.Database
User_Name = Text0.Value
Password1 = Text2.Value

Set rsTemp = db.OpenRecordset("SELECT users.[Password]FROM users Where users.[User_Name]='" & User_Name & "';")
Password = rsTemp![Password]
rsTemp.Close
If Password = Password1 Then
Command12.Visible = true
End If

End Sub
after i tried what some one (medialint) else has offered this
Dim rsTemp As New ADODB.Recordset
rsTemp.Open "SELECT [somefield] FROM [sometable];", _
CurrentProject.Connection, adOpenStatic, adLockReadOnly
txtSomeTextBox = rsTemp![somefield]
non of them worked please help
rsTemp.Close

OBP
11-14-2008, 04:16 AM
I devloped this for another poster, the part that you are interested in is the SQL and Set Statements.

On Error GoTo Err_OK_Button_Click
' Here if user has clicked on OK button.

Dim rs As Object, rstable As Object, count As Integer, counter As Integer
Dim sql As String, recount As Integer


Me.UserLoginName.BackColor = 10092543 ' Set UserLoginName BackColor to yellow
Me.UserLoginPassword.BackColor = 10092543 ' Set UserLoginPassword BackColor to yellow



If IsNull(Me.UserLoginName) And IsNull(Me.UserLoginPassword) Then
' here if both Username and password are blank
Me.UserLoginName.BackColor = 6599167 ' set backcolor orange
Me.UserLoginPassword.BackColor = 6599167 ' set backcolor orange
MsgBox "You must enter a User Name and Password", vbExclamation
Me.UserLoginName.SetFocus
Exit Sub
End If
' here if not both Username and Password are blank

If IsNull(Me.UserLoginName) Then
' here if Username is null but Password is not null
Me.UserLoginName.BackColor = 6599167 ' set backcolor orange
Me.UserLoginPassword.BackColor = 10092543 ' set backcolor yellow
MsgBox "You must enter a Username", vbExclamation
Me.UserLoginName.SetFocus
Exit Sub
End If
' Here if a Username has been entered, but it has not yet been validated


If IsNull(Me.UserLoginPassword) Then
' here if Password is null but Username is not null
Me.UserLoginName.BackColor = 10092543 ' set backcolor yellow
Me.UserLoginPassword.BackColor = 6599167 ' set backcolor orange
MsgBox "You must enter a Password", vbExclamation
Me.UserLoginPassword.SetFocus
Exit Sub
End If
' Here if both a Username and Password have been entered, but neither has been validated
'
' At this point it is considered a login attempt and all further such attempts
' are counted until the MaxAllowedLoginAttempts value has been met. Once
' the MaxAllowedLoginAttempts has been met, the user is dumped and the database
' and Access are closed.

' This starts the processing and counting of login attempts



sql = "SELECT [tbl(Zn)UserNames].* " & _
"FROM [tbl(Zn)UserNames] " & _
"WHERE Username = '" & Me.UserLoginName & "' "
Set rs = CurrentDb.OpenRecordset(sql)
recount = rs.RecordCount



If recount = 0 Then
' here if no records are found with this Username in them
' this counts as a failed login attempt
LoginAttemptCounter = LoginAttemptCounter + 1 ' Increment LoginAttemptCounter
rs.Close
Set rs = Nothing

' continue to see how many login attempts have been made so far
If LoginAttemptCounter < MaxAllowedLoginAttempts Then
' here if no such Username and user has not exceeded
' MaxAllowedLoginAttempts; allow another login attempt
Me.UserLoginName.BackColor = 6599167 ' set backcolor orange
MsgBox "That is not a recognised Username. Try Again.", vbExclamation
Me.UserLoginName.SetFocus ' move curser back to Username field
Exit Sub ' return control to User on the form to allow another try
Else
' here if this Username not found and MaxAllowedLoginAttempts reached
' force an Exit from the database
With rs

.Edit

![NumberFailedLogins] = ![NumberFailedLogins] + 1

.Update
.Bookmark = .LastModified
End With
MsgBox "The maximum allowed login attempts have been" & Chr(13) & _
"reached. Exiting the database and Access.", vbCritical
Quit
End If

Else
' Here if there is a record with this Username in it, which means this
' Username is valid. Also, a Password has been entered but not yet verified.

Me.UserLoginName.BackColor = 10092543 ' Set UserLoginName BackColor to yellow
' Now check for valid password
If Me.UserLoginPassword <> rs.Password Then
' here if incorrect Password for this Username
' LoginAttemptCounter = LoginAttemptCounter + 1 ' Increment LoginAttemptCounter
' rs.Close
'MsgBox "Temp stop 8.3"
' Set rs = Nothing
'MsgBox "Temp stop 8.4"

If LoginAttemptCounter < MaxAllowedLoginAttempts Then
' here to allow another login attempt after bad password
'MsgBox "Temp stop 8.5"
Me.UserLoginPassword.BackColor = 6599167 ' set backcolor orange
MsgBox "That is not the correct Password for" & Chr(13) & _
"that Username. Try Again.", vbExclamation
Me.UserLoginPassword.SetFocus
' start login process over again
Exit Sub
'MsgBox "Temp stop 9"
Else ' here if LoginAttemptCounter > MaxAllowedLoginAttempts
' force a Quit because allowed login attempts has been exceeded
With rs
'MsgBox "Temp stop 9.0"
.Edit
'MsgBox "Temp stop 9.1"
![NumberFailedLogins] = ![NumberFailedLogins] + 1
'MsgBox "Temp stop 9.2"
.Update
'MsgBox "Temp stop 9.3"
.Bookmark = .LastModified
End With
MsgBox "The maximum allowed login attempts have been" & Chr(13) & _
"reached. Exiting the database and Access.", vbCritical
Quit
End If
' here if still more login atempts are permitted
End If
' here if Password is valid for this Username; login is now successful
End If
' Here if login successful...
'MsgBox "Temp stop 10 - Login Successful"

With rs
.Edit
![NumberSuccessfulLogins] = ![NumberSuccessfulLogins] + 1
![LastLogin] = Now()
.Update
'MsgBox "Temp stop 10.3"
.Bookmark = .LastModified
End With

'MsgBox "Temp stop 11"

' Populate UserLog form with data needed to register successful login for this user
'MsgBox "Temp stop 11.1"
Forms![frm(97x) User Log].[UserName] = Me.UserLoginName
Forms![frm(97x) User Log].[DateTimeLogin] = Now()
'MsgBox "Temp stop 11.2"
' Save UserName in global variable for use elsewhere
'MsgBox "Temp stop 12"
g_str_CurrentUserName = Me.UserLoginName
'MsgBox "Temp stop 12.1"
rs.Close
Set rs = Nothing

' close the LogIn form
DoCmd.Close

' open the Main Menu (Access Switchboard
DoCmd.OpenForm "Main Menu"

Exit_OK_Button_Click:
Exit Sub

Err_OK_Button_Click:
MsgBox Err.Description
Resume Exit_OK_Button_Click

End Sub

CreganTur
11-14-2008, 06:31 AM
You could also use the DLookup function to lookup the password for the specified username. If the Dlookup returns a null or empty string value then you know the username was incorrect. If it does return a value, then you can compare it to the password entered by the user.

Access Help has some great documentation on DLookup, but if you need help with it let us know.

DBinPhilly
11-22-2008, 07:00 AM
I took the original code and made minor alterations.

This will work:

Private Sub Command4_Click()


Dim User_Name As String
Dim Password As String
Dim strSQL As String
Dim pass As String
Dim Password1 As String
Dim rsTemp As Recordset
Dim db As DAO.Database
User_Name = Text0.Value
Password1 = Text2.Value

Set db = CurrentDb
strSQL = "SELECT * FROM users "
Set rsTemp = db.OpenRecordset(strSQL)
'Set rsTemp = db.OpenRecordset("SELECT * FROM users Where users![User_Name]='" & User_Name & "'")
' I added these lines: _______________________________
rsTemp.FindFirst ("User_Name = '" & User_Name & "'")
If rsTemp.NoMatch Then GoTo NoFind
' _______________________________________
Password = rsTemp![Password]
rsTemp.Close
If Password = Password1 Then
Command12.Visible = True
End If
NoFind:

End Sub