why does this not work with microsoft access database?

Hi, I am using the following code for a login form. For some reason if i have multiple users in a database it will only work with the first user. So it wont check the second line in the database, only the first one.

Heres the code

Code:

---

Dim db As Database
Dim rs As Recordset
Dim ds As Recordset
Dim WS As Workspace


 Set WS = DBEngine.Workspaces(0)
    dbfile = ("C:\AddressBook.mdb")
    pwdstring = "swordfish"
Set db = DBEngine.OpenDatabase(dbfile, False, False, ";PWD=" & pwdstring)
Set rs = db.OpenRecordset("SELECT * FROM `agents`")






If Text1.Text = rs("username") And Text2.Text = rs("password") Then


MDIForm1.Show
MainForm.BtnSaveToWaveFile.value = "1"


   
        Me.Hide
 
     
    Else
        MsgBox "Incorrect Username or Password", vbCritical
        Text1.SetFocus
    End If

---

Any ideas?


Jamie

i would believe you should open the recordset with a where clause
where agent = text1

otherwise you would need to use find or loop through the recordset to match the username

I'm using code below to connect Access DB and have about 6 user using Form Login. please change it to fit your requirement. Hope you got benefit in return. Thank you

Code:

---

Private Sub cmdOK_Click()
'This Makes sure that the user name exists and has a password
On Error GoTo ErrorHandle
If OKPro = False Then Exit Sub
If txtPass.Text = PassCheck Then
Uname = txtUser.Text
frmLaunch.Show
Unload Me
Else
MsgBox "Incorrect Password"
End If
Exit Sub
ErrorHandle:
MsgBox err.Description
End Sub

Private Sub Form_Load()
'This Opens the database connections
On Error GoTo ErrorHandle

If App.PrevInstance = True Then End

Call OpenLocalDB
OKPro = False
Exit Sub
ErrorHandle:
MsgBox err.Description

End Sub

Private Sub Form_Unload(Cancel As Integer)
'This Closes the UserRec recordset when the form closes
On Error GoTo ErrorHandle
UserRec.Close
Exit Sub
ErrorHandle:
MsgBox err.Description
End Sub

Private Sub txtUser_KeyPress(KeyAscii As Integer)
'This makes the charecters in the txtUser text box all upper case
On Error GoTo ErrorHandle
KeyAscii = Asc(UCase(Chr(KeyAscii)))
Exit Sub
ErrorHandle:
MsgBox err.Description
End Sub

Private Sub txtUser_LostFocus()
'When the focus moves from txtUser it checks that the user is in the database and gets the password for the user
On Error GoTo ErrorHandle
If txtUser.Text = "" Then Exit Sub

Call GetUserPassword
Exit Sub
ErrorHandle:
MsgBox err.Description
End Sub

Private Sub GetUserPassword()
'This is the sub that checks the user and gets the password
On Error GoTo ErrorCheck

SQLUserRec = "Select UserName, Password FROM Security "
SQLUserRec = SQLUserRec & "WHERE UserName Like '" & txtUser.Text & "'"


Set UserRec = New Recordset
UserRec.Open SQLUserRec, dbContact

PassCheck = UserRec.Fields(1).Value
OKPro = True
Exit Sub


ErrorCheck:
MsgBox "Incorrect Username "
OKPro = False
UserRec.Close
txtUser.SetFocus
End Sub

Sub OpenLocalDB()
On Error GoTo OpenLocalDB_Error
     
 
  Screen.MousePointer = vbHourglass
  dbPath = GetSetting(App.Title, "Settings", "Path")
    'loads the database path, if there isnt one it loads D:
 
  If dbPath = "" Then
    SaveSetting App.Title, "Settings", "Path", "D:"
    dbPath = GetSetting(App.Title, "Settings", "Path")
    End If
 
 
  Screen.MousePointer = vbHourglass
  RecPath = dbPath & "\Reports\"
    'uses dbPath to build database path

    g_strDBName = dbPath & "\Data\DSS.mdb;Jet " & "OLEDB:Database Password=blabla"
    'creates a new databse conection and sets the conection string
  Set dbContact = New ADODB.Connection
  Constring = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
  "Data Source=" & g_strDBName
  dbContact.ConnectionString = Constring
  'Opens the database conection
  dbContact.Open
   
  Screen.MousePointer = vbDefault
 
  Exit Sub
OpenLocalDB_Error:
      Dim ans As Variant
 
    ans = MsgBox("Please make sure your Database located in Drive D then proceed", vbYesNo, "Connection Error")
 
   
End Sub

---

Yes you should use a where clause either where agent = or where agent = and password - depending on how you want to do it.

Using the current method you would need to loop through the resultign recordset and check each record until you find a match or run out of records.

Hi data, Is it possible for a quick example of how to use the where clause?

regards
Jamie

Code:

---

Set rs = db.OpenRecordset("SELECT * FROM `agents` where username = '" & text1.text &"'")
If rs.recordcount>0 then
  If Text2.Text = rs("password") Then
        'valid login
  Else
        'invalid password
  End If
Else
  'Invalid Username
End If

---