"Type Mismatch" after conversion to Access 2007

[Sean O'Halloran]

Dear Colleagues,

I'm in over my head. After converting an Access 2003 mdb to 2007 accdb I get a "Type Mismatch" error when trying to login. The error fires when it hits the code in red.

"WorkerIDNumber" is a Primary Key - Autonumber - long integer.
"Password" is a text field.

Any help / advice will be greatly appreciated... - Sean

Private Sub cmdLogin_Click()
On Error GoTo Err_cmdLogin_Click

Dim szPassword As String, szSQL As String
Dim lWorker As Long
Dim iReturn As Integer
Dim db As Database
Dim rstWorker As Recordset
Set db = GetcurrentDB()
szSQL = "SELECT * FROM [tblAPS_Workers] WHERE WorkerIDNumber = " & cboWorker & " AND Password = """ & Trim(txtPassword) & """"

Set rstWorker = db.OpenRecordset(szSQL, dbOpenSnapshot)
If rstWorker.RecordCount = 1 Then
rstWorker.MoveFirst
lWorker = rstWorker!WorkerIDNumber
SetWorker (lWorker)
'Clearing objects
rstWorker.Close
Set rstWorker = Nothing
Set db = Nothing
Set rstWorker = Nothing

DoCmd.Close
DoCmd.OpenForm ("frmMain")
Exit Sub
Else
iAttempt = iAttempt + 1
'If more than 4 logins, abort
If iAttempt = 5 Then
MsgBox ("More than 4 Unsuccessful Attempts, Exiting Access")
rstWorker.Close
Set db = Nothing
Set rstWorker = Nothing
'Exit ACCESS
DoCmd.Quit

Exit Sub
Else
iReturn = MsgBox("Could Not Log In, Please Try Again", , "Log In Failure")
txtPassword.SetFocus
GoTo Exit_cmdLogin_Click
End If
End If

Exit_cmdLogin_Click:
rstWorker.Close
Set db = Nothing
Set rstWorker = Nothing
Exit Sub

Err_cmdLogin_Click:
MsgBox Err.Description
Resume Exit_cmdLogin_Click

End Sub

 

[spikepl]

You need to disambiguate declarations.

Recordset-> Dao.Recordset
DDATABASE -> Dao.Database

 

[Sean O'Halloran]

Well - THAT was simple! THANK YOU!

If I can impose on your goodwill - can you explain why the code worked in 2003, but not in 2007?

Smilin' Sean

 

[spikepl]

AFAIK MS changed the default sequence of reference libraries. Meaning, in 2007 an ambiguous Database or Recordset defaults to another library than in 2003.

 

[Sean O'Halloran]

Thank you; I will research. I greatly appreciate your fast, accurate and informative assistance. Have a blessed weekend!

Smilin' Sean

 

[HiTechCoach]

Well - THAT was simple! THANK YOU!

If I can impose on your goodwill - can you explain why the code worked in 2003, but not in 2007?

Smilin' Sean

You were just luck that it worked in 2003.

IIRC, Access 2007 adds some new references libraries. It will not automatically remove the old references. This could cause an issue with duplicate libraries. Also the order of the reference from Top down can cause this issue. Even in 2003, if you change the order of the Libraries in the list you could get the same error.


You should always avoid ambiguous coding. It saves you from many headaches and time trying to debug issues.