Recordset Type Mismatch

[MrBologna]

For the past year+, a form I created has worked flawlessly on all PC's my front end is distributed to.



As of today, some PCs are getting a type mismatch (error 13) when I try to set a variable to the return from a DAO recordset.
I checked the windows update history of one of these, and it hasn't had any updates in the past 2 weeks. This function was confirmed working yesterday.

As far as I know I'm only using DAO objects, so the microsoft help doc that says to explicitly define ADODB and DAO variables shouldn't apply, right?

Access will also crash completely if I try to do a watch on the recordset.


Here is the subroutine, the line it fails on has a comment:

Private Sub Employee_List_AfterUpdate()

'Pull the new employee ID:


Employee_ID_Temp = Me.Employee_List.Value


'Pull the employee's name as a string:


Dim strSQL As String
Dim strFirstName As String
Dim strLastName As String
Dim rst As DAO.Recordset
Dim Total_Vacation_Days As Integer
Dim Vacation_Days_Used As Integer


strSQL = "SELECT Employees.First_Name, Employees.Last_Name FROM Employees WHERE (((Employees.ID)=" & Employee_ID_Temp & "));"
Set rst = CurrentDb.OpenRecordset(strSQL)


If IsNull(rst.Fields("First_Name").Value) = False Then
    strFirstName = rst!First_Name
ElseIf IsNull(rst.Fields("First_Name").Value) = True Then
    strFirstName = " "
End If


If IsNull(rst.Fields("Last_Name").Value) = False Then
    strLastName = rst!Last_Name
ElseIf IsNull(rst.Fields("Last_Name").Value) = True Then
    strLastName = " "
End If


Employee_Name = (strFirstName & " " & strLastName)


'Set the textbox equal to the employee's name string:


Me.Current_Employee_Text.Value = Employee_Name
Me.txtEmployeeID.Value = Employee_ID_Temp


'Update vacation days textbox:


strSQL = "SELECT Vacation_Master.Total_Vacation_Days FROM Vacation_Master WHERE (((Vacation_Master.Calendar_Year)= Year(Date())) AND ((Vacation_Master.Employee_ID)=" & Employee_ID_Temp & "));"
Set rst = CurrentDb.OpenRecordset(strSQL)


If rst.EOF = False Then
    Total_Vacation_Days = rst!Total_Vacation_Days 'this is the line it fails on
ElseIf rst.EOF = True Then
    Total_Vacation_Days = 0
End If


Me.txtEmployee_Vacation_Days.Value = Total_Vacation_Days


strSQL = "SELECT Sum(Vacation_Time.Vacation_Days_Used) AS SumOfVacation_Days_Used FROM Vacation_Time GROUP BY Vacation_Time.Employee_ID, Year([Start_Date]) HAVING (((Vacation_Time.Employee_ID)=" & Employee_ID_Temp & ") AND ((Year([Start_Date]))=Year(Date())));"
Set rst = CurrentDb.OpenRecordset(strSQL)


If rst.EOF = False Then
    Vacation_Days_Used = rst!SumOfVacation_Days_Used
ElseIf rst.EOF = True Then
    Vacation_Days_Used = 0
End If


Me.txtVacationDaysRemaining = (Total_Vacation_Days - Vacation_Days_Used)




'requery by updating the table recordset:


subformSQL = "SELECT TimeClock.Employee_ID, TimeClock.Clock_Date, TimeClock.Time_In, TimeClock.Lunch_Out, TimeClock.Lunch_In, TimeClock.Time_Out, (Abs(DateDiff(""n"",[Timeclock].[Time_In],[Timeclock].[Lunch_Out]))+Abs(DateDiff(""n"",[Timeclock].[Lunch_In],[Timeclock].[Time_Out])))/60 AS Hours_Worked, TimeClock.Notes FROM TimeClock WHERE (((TimeClock.Employee_ID)=" & Employee_ID_Temp & ") AND ((TimeClock.Clock_Date) Between [Forms]![TimeClock_Form]![weekList] And ([Forms]![TimeClock_Form]![weekList]-Weekday([Forms]![TimeClock_Form]![weekList])+8))) ORDER BY TimeClock.Clock_Date DESC;"


'MsgBox ([Forms]![TimeClock_Form]![weekList])


Me.Employee_TimeClock_Query_subform.Form.RecordSource = subformSQL


'Update the graph to show current employee:


strSQL = "SELECT (Abs(DateDiff(""n"",[Timeclock].[Time_In],[Timeclock].[Lunch_Out]))+Abs(DateDiff(""n"",[Timeclock].[Lunch_In],[Timeclock].[Time_Out])))/60 AS Hours_Worked, TimeClock.Clock_Date, TimeClock.Employee_ID, Employees.First_Name, Employees.Last_Name FROM Employees INNER JOIN TimeClock ON Employees.ID = TimeClock.Employee_ID WHERE (((TimeClock.Employee_ID)=" & Employee_ID_Temp & ")) AND ([Timeclock.Clock_Date] Between [Forms]![TimeClock_Form]![weekList] And ([Forms]![TimeClock_Form]![weekList]-Weekday([Forms]![TimeClock_Form]![weekList])+8));"
CurrentDb.QueryDefs("Employee_Hours_Worked").SQL = strSQL


Me.Employee_Hours_Chart.RowSource = "Employee_Hours_Worked"


Me!Employee_Hours_Chart.Requery


Me!weekList.Requery


End Sub

I have this issue on my home PC, but not on my work PC. So I have a test environment for both working and not.

[kd2017]

Maybe try this instead

If Not (rst.BOF And rst.EOF) Then
    Total_Vacation_Days = rst!Total_Vacation_Days 'this is the line it fails on
Else
    Total_Vacation_Days = 0
End If

http://allenbrowne.com/ser-29.html

[MrBologna]

Same thing.

However, I did notice that I have 32bit on my work pc and 64bit at home. Going to reinstall as 32bit and see if it's fixed.

[MrBologna]

Reinstalled as 32bit at home, still broken.

[Gicu]

What line is raising the error?

Cheers,

[MrBologna]

the error is on line 56.

[ssanfu]

I noticed that the variable "Employee_ID_Temp" has not been declared.

Is "Employee_Name" a variable or a control on the form?? It is not declared in the VBA either.




Do you have

Option Compare Database
Option Explicit

as the top 2 lines of EVERY code module??


Another thing to try:
change the code to

.
.
.
    'Update vacation days textbox:
    strSQL = "SELECT Vacation_Master.Total_Vacation_Days FROM Vacation_Master WHERE (((Vacation_Master.Calendar_Year)= Year(Date())) AND ((Vacation_Master.Employee_ID)=" & Employee_ID_Temp & "));"
    Debug.Print strSQL     '<<<<-----  Add this line

    Set rst = CurrentDb.OpenRecordset(strSQL)

    If rst.EOF = False Then
        Total_Vacation_Days = rst!Total_Vacation_Days    'this is the line it fails on
    ElseIf rst.EOF = True Then
        Total_Vacation_Days = 0
    End If
.
.
.

Run the code, then copy the SQL from the immediate, create a new query, switch to SQL View, paste in the SQL you copied and execute the query.
Any errors??



Also you should close and destroy the record set. Just above "End Sub" add

.
.
.
    Me!Employee_Hours_Chart.Requery
    Me!weekList.Requery

    rst.Close
    Set rst = Nothing
End Sub

[MrBologna]

Employee_Name (str), SubformSQL (str) and Employee_ID_Temp (int) are declared at the top of the module with the options because I use them in multiple subroutines, as well as pass them to a popout form.

I only had Option Compare Database, not Option Explicit at the top of my module. Adding option explicit did not change anything, I think I have everything declared.

The results of the SQL seem fine, produced a single value:

SELECT Vacation_Master.Total_Vacation_Days FROM Vacation_Master WHERE (((Vacation_Master.Calendar_Year)= Year(Date())) AND ((Vacation_Master.Employee_ID)=24));

and closing/uninitializing the recordset also made no difference, though I agree it should be done.

[MrBologna]

I can currently run the exact same code on two different computers, one will break and one will not. Maybe it's something to do with the references...?

[ssanfu]

OK, the 3 variables are global (to this module), which is why there was an error when I added "Option Explicit". Having "Option Explicit" at the top of every module helps reduce errors in the code. You can have it added to any NEW modules if you set a flag - in the IDE, click on TOOLS > Options, then check "Require Variable Declaration".

And the SQL seems to be formed properly, since the query returned a value.

The next thing I would suspect is corruption. Have you done a "Compile and Repair"?
Or you could copy all objects into a new dB to see if that fixes the problem. Hard to say without seeing your dB.


Closing/uninitializing the recordset is just good programming practice. Helps to eliminate/reduce memory leaks.



Edit: forgot to ask, would you post your dB for analysis??

[kd2017]

...

Access will also crash completely if I try to do a watch on the recordset.

...
Dim Total_Vacation_Days As Integer
...
If rst.EOF = False Then
    Total_Vacation_Days = rst!Total_Vacation_Days 'this is the line it fails on
ElseIf rst.EOF = True Then
    Total_Vacation_Days = 0
End If

...

Can you try adding a breakpoint BEFORE the if statement, say where you build the sql, then step through it (F8 key) and report what's happening at each step? What's the actual value of rst!Total_VacationDays ?
Can you try Total_Vacation_Days = CInt(rst!Total_Vacation_Days) ?
Or maybe try changing Total_Vacation_Days to a variant?

[MrBologna]

I compact/repair fairly frequently, tried that just now to no avail. (I just compact/repair instead of closing/reopening when I turn the navigation pane on/off)

I've also had issues with corruption in the past, I can try moving to a new file for the second time... it's a lot of stuff to move so I'm going to avoid it for now. Maybe I'll try it with just the pertinent stuff and see if it helps, then I can provide you guys with something that you can run as well.

using CInt did not work.

declaring Total_Vacation_Days as a variant also did not work.

Here's something interesting though, when moving through it in steps, I cannot view the recordset fields without receiving an error:


on the previous step I was able to view the previous recordset (with the name stuff) no problem.

[pbaldy]

Is it this?

https://support.office.com/en-us/art...7-2aaa01e07fc7

[MrBologna]

My data types are integers as far as I know, not decimals, but I'm attempting to roll back now. The version on my work PC is 1910 so it certainly could be this.

UPDATE: Yep, I rolled back to version 2002 and it's fixed my problem! Guess I'm swapping everyone to semi-annual updates...

Thank you to everyone for your assistance, it's much appreciated. +rep all around!

[pbaldy]

Happy to help!