why does this not work with microsoft access database?

[JamieWarren09]

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

 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

[westconn1]

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

[brss]

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

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

[DataMiser]

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.

[JamieWarren09]

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

regards
Jamie

[DataMiser]

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