I am using Microsoft ADO 6.1 Library. I am making a simple login form where the users and their password are stored in a worksheet (StaffList) in an Excel file (Reports.xlms) that is outside of the workbook where I am creating a Login userform(DataEntry.xlms). I am trying to query from Reports.xlms and verify if username and password match any of the entries entered in the userform.
On the userform, I have this code for reference for the login button.
Private Sub btn_Login_Click() Dim txtbx_staffname As String Dim txtbx_password As String If Me.txtbx_staffname.Value = "" Then 'Username MsgBox "Please enter your user name.", vbCritical Exit Sub End If If Me.txtbx_password.Value = "" Then MsgBox "Please enter your password.", vbCritical Exit Sub End If Call UserLogin(Me.txtbx_staffname.Value, Me.txtbx_password.Value) End Sub
I have this code to check if username and password entered is present in the StaffList
Sub UserLogin(User_Id As String, Password As String) Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim qry As String cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:_Download CenterCoop Drive_Coop DriveTHHC DBDatabaseReports.xlsm;Extended Properties=""Excel 12.0 Macro;HDR=YES;FMT=Delimited;IMEX=1;"";" qry = "Select * From [StaffList$] where User_Id = '" & User_Id & "'" rst.Open qry, cnn, adOpenKeyset, adLockOptimistic If rst.RecordCount = 0 Then MsgBox "Incorrect Usern name", vbCritical ElseIf rst.Fields("Password").Value = Password Then MsgBox "Incorrect Password", vbInformation Else MsgBox "Incorrect Password", vbCritical End If rst.Close cnn.Close End Sub
I having a run-time error that “Data type mismatch in criteria expression in the line. “rst.Open qry, cnn, adOpenKeyset, adLockOptimistic”
It seems fine if ì use “qry = “Select * From [StaffList$]”
I don’t understand why there is a data type mismatch when both variables are assigned as strings. What is the way to make a query here?
Sorry, amateur vba. Thanks for helping.