Type mismatch error

[Jason]

I'm running the code below and keep getting the error "Run-time error '13':
Type mismatch.

When I debug, it stops on the line set rst = dbs.openrecordset (strsql)
I've tried the query that strsql builds as a standalone, and it works fine.

Any ideas? Thanks in advance.

Private Sub cmbPsswd_AfterUpdate()


Dim dbs As Database, rst As Recordset, strSQL As String
Dim strPWD As String, strPWD2 As String, strUserID As String

Me.Refresh

strUserID = Me.Combo0
strPWD = Me.cmbPsswd

Set dbs = CurrentDb

'Build sql to retrieve password from table
strSQL = "SELECT tblName.Password " & _
"FROM tblName " & _
"WHERE ((tblName.NameID)=" & [Forms]![frmSOPSelection].[Combo0] &
");"

'Set strPWD2 equal to retrieved password
Set rst = dbs.OpenRecordset(strSQL)
strPWD2 = rst.Fields(0)

'If the passwords are enable SOP box
If strPWD = strPWD2 Then
Me.lstSOP.Enabled = True
'Otherwise clear password and prompt user to reenter
Else
MsgBox "Your User ID or Password is incorrect. Please try again."
Me.cmbPsswd = Null
Me.lstSOP.Enabled = False
End If

rst.Close
Set dbs = Nothing

End Sub

 

[Rick Brandt]

I'm running the code below and keep getting the error "Run-time error
'13': Type mismatch.

When I debug, it stops on the line set rst = dbs.openrecordset
(strsql) I've tried the query that strsql builds as a standalone, and
it works fine.

Any ideas? Thanks in advance.

Private Sub cmbPsswd_AfterUpdate()


Dim dbs As Database, rst As Recordset, strSQL As String
Dim strPWD As String, strPWD2 As String, strUserID As String

Me.Refresh

strUserID = Me.Combo0
strPWD = Me.cmbPsswd

Set dbs = CurrentDb

'Build sql to retrieve password from table
strSQL = "SELECT tblName.Password " & _
"FROM tblName " & _
"WHERE ((tblName.NameID)=" &
[Forms]![frmSOPSelection].[Combo0] & ");"

'Set strPWD2 equal to retrieved password
Set rst = dbs.OpenRecordset(strSQL)
strPWD2 = rst.Fields(0)

'If the passwords are enable SOP box
If strPWD = strPWD2 Then
Me.lstSOP.Enabled = True
'Otherwise clear password and prompt user to reenter
Else
MsgBox "Your User ID or Password is incorrect. Please try again."
Me.cmbPsswd = Null
Me.lstSOP.Enabled = False
End If

rst.Close
Set dbs = Nothing

End Sub

Sounds like you have references in your file to both ADO and DAO object
libraries. These both have a Recordset object and your code is trying to
use an ADO Recordset which doesn't work with the Database object ( which is
a DAO Object ).

Either specify DAO recordset...
Dim rst As DAO.Recordset

....or remove the ADO reference in Tools References ( you have to be in the
VBA editor for that menu item ).

 

[Jason]

Excellent - I never would have figured that one out! Thanks!
--
Thanks,

Jason

I'm running the code below and keep getting the error "Run-time error
'13': Type mismatch.

When I debug, it stops on the line set rst = dbs.openrecordset
(strsql) I've tried the query that strsql builds as a standalone, and
it works fine.

Any ideas? Thanks in advance.

Private Sub cmbPsswd_AfterUpdate()


Dim dbs As Database, rst As Recordset, strSQL As String
Dim strPWD As String, strPWD2 As String, strUserID As String

Me.Refresh

strUserID = Me.Combo0
strPWD = Me.cmbPsswd

Set dbs = CurrentDb

'Build sql to retrieve password from table
strSQL = "SELECT tblName.Password " & _
"FROM tblName " & _
"WHERE ((tblName.NameID)=" &
[Forms]![frmSOPSelection].[Combo0] & ");"

'Set strPWD2 equal to retrieved password
Set rst = dbs.OpenRecordset(strSQL)
strPWD2 = rst.Fields(0)

'If the passwords are enable SOP box
If strPWD = strPWD2 Then
Me.lstSOP.Enabled = True
'Otherwise clear password and prompt user to reenter
Else
MsgBox "Your User ID or Password is incorrect. Please try again."
Me.cmbPsswd = Null
Me.lstSOP.Enabled = False
End If

rst.Close
Set dbs = Nothing

End Sub

Sounds like you have references in your file to both ADO and DAO object
libraries. These both have a Recordset object and your code is trying to
use an ADO Recordset which doesn't work with the Database object ( which is
a DAO Object ).

Either specify DAO recordset...
Dim rst As DAO.Recordset

....or remove the ADO reference in Tools References ( you have to be in the
VBA editor for that menu item ).