"syntax error in where clause" - please help

[Outstrip]

Hi, please help me on the below code, i am new at VB.

Private Sub cmdOK_Click()
Dim sqlPassword As String
Dim rstPassword As ADODB.Recordset
Dim intDaysLeft As Integer
Dim dateHolder As Date
Dim strChngPassAnsw As String
Dim varBK As Variant

Set cnn = New ADODB.Connection
Set rstPassword = New ADODB.Recordset
cnnOpen
'Open up the database and search for username and password
sqlPassword = "Select * from tblEmployee where "
rstPassword.Open sqlPassword, cnn, adOpenKeyset, adLockPessimistic

With rstPassword
varBK = rstPassword.Bookmark
.Find "empusername = '" & txtUserName.Text & "'", , adSearchForward, 1
    If Not .EOF Then
        If .Fields("EmpPassword") = txtPassword.Text Then
                If .Fields("EmpPassword") = "123456" Then 'If the default pswd then force them to change it
                changepass = True
                End If
            dateHolder = FormatDateTime(.Fields("pswddate"), vbShortDate)
            Me.Hide
        Else
            'Wrong Password
            MsgBox "Invalid Password, try again!", , "Login"
            txtPassword.Text = ""
            txtPassword.SetFocus
            Exit Sub
        End If
     Else
        'Wrong User Name
        MsgBox "Invalid User Name, try again!", , "Login"
        txtPassword.Text = ""
        txtUserName = ""
        txtUserName.SetFocus
        Exit Sub
     End If
    ' Global variables to hold User's data for permissions
    EmpCurrent.emUserName = .Fields("EmpUserName")
    EmpCurrent.emDept = .Fields("EmpDeptID")
    EmpCurrent.emFirstName = .Fields("EmpFirst")
    EmpCurrent.emLastName = .Fields("EmpLast")
    EmpCurrent.emGrade = .Fields("EmpGradeID")
    EmpCurrent.emID = .Fields("EmployeeID")
    EmpCurrent.empswd = .Fields("EmpPassword")
    ' Check the date and force a change every 90 days
    intDaysLeft = Date - dateHolder
    If intDaysLeft >= 85 Then
    intDaysLeft = 90 - intDaysLeft
        If intDaysLeft <= 0 Then
        MsgBox "Your Password is expired, please change it now.", , "Password Expired"
        changepass = True
        Else
        strChngPassAnsw = MsgBox("Your password expires in " & intDaysLeft & "days." & vbNewLine & _
        "Would you like to change it now?", vbYesNo, "Password Expiration")
            If strChngPassAnsw = vbYes Then
            changepass = True
            End If
        End If
    End If
End With
rstPassword.Close
Set rstPassword = Nothing
cnnClose
End Sub

[jmcilhinney]

The problem is that you have no WHERE clause. You have the WHERE keyword but no condition following it. What condition do you want the records to match to be included in the result set? If you want every record then get rid of the WHERE keyword altogether.

[Outstrip]

Thanks, but i didn't understand (my ignorance). Would you please help me to rewrite it?

i am having error in

rstPassword.Open sqlPassword, cnn, adOpenKeyset, adLockPessimistic

[Zvoni]

If you want every record then get rid of the WHERE keyword altogether.

Correct, since 5 lines later he uses the Find-Method of the Recordset.

But for what he wants to do, a proper SELECT with a correct WHERE-Clause would be better

[jmcilhinney]

Thanks, but i didn't understand (my ignorance). Would you please help me to rewrite it?

i am having error in

rstPassword.Open sqlPassword, cnn, adOpenKeyset, adLockPessimistic

No, you don't have an error there. That's where it shows up but there's no issue with that code. The issue is here:

sqlPassword = "Select * from tblEmployee where "

This is your SQL code:

SELECT *
FROM tblEmployee
WHERE

Where what? If you want a record where the user name and password are specific values then say that:

SELECT *
FROM tblEmployee
WHERE EmpUserName = @EmpUserName
AND EmpPassword = @EmpPassword

You should also follow the Database FAQ link in my signature and check out the link on how to use parameters in VB6.

[Outstrip]

Solved

sqlPassword = "SELECT EmployeeID, EmpGradeID, EmpLast, EmpFirst, EmpDeptID, pswddate, empusername, EmpPassword FROM tblEmployee"

Thanks very much to "jmcilhinney" & "Zvoni"

[jmcilhinney]

Solved

sqlPassword = "SELECT EmployeeID, EmpGradeID, EmpLast, EmpFirst, EmpDeptID, pswddate, empusername, EmpPassword FROM tblEmployee"

Thanks very much to "jmcilhinney" & "Zvoni"

I'm not sure that that is the best solution. As I said, if you want all the data then get rid of the WHERE altogether, which is what you've done. Do you really want all the data though? If you're going to retrieve all the data and then look through it in VB code for one record you should be looking for and retrieving just that one record in the first place.

[TysonLPrice]

Solved

sqlPassword = "SELECT EmployeeID, EmpGradeID, EmpLast, EmpFirst, EmpDeptID, pswddate, empusername, EmpPassword FROM tblEmployee"

Thanks very much to "jmcilhinney" & "Zvoni"

You do realize you are going to get all the IDs in that table right? That's great for a report on people's passwords but shouldn't be necessary just to log someone on.