macro error due to editor

[Guest]

I have an Access database with a visual Basic module run by a macro.

The macro runs fine until I use the VB editor. If I just open and close the
VB editor
I get “Run Time error 13 Type Mismatch†on the following line.

“Set EMP_No = DB.OpenRecordset("ct01 UnFilled Employees", dbOpenDynaset)â€

This appears to be a VB problem as I have changed nothing in the VB module,
only open and closed it.
I did have some cleanup work done on my computer recently and installed
PC-Illin for security, but I have also just tested a copy of the same
database on a second PC and exactly the same problem has occurred – so I do
not think that is the problem

I am testing with a clean, unaltered copy of the original Access database
each time.

 

[Douglas J. Steele]

How is EMP_No declared?

I'm suspecting that you've got

Dim EMP_No As Recordset

Try changing that to

Dim EMP_No As DAO.Recordset

If that gives you an error of "User type not defined" (or something like
that), you most likely don't have a reference set to DAO. With any code
module open, select Tools | References from the menu bar, scroll through the
list of available references until you find the one for Microsoft DAO 3.6
Object Library, and select it.

If you've got something other than Recordset in your declaration, you cannot
use EMP_No with the OpenRecordset method.

If you haven't got a declaration, create one!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"gmckinlay(deletethispart)@hotmail.com"

 

[Guest]

Thanks.
I do have Dim EMP_No As Recordset so I tried "Dim EMP_No As DAO.Recordset"
but received the same error.
I already had the reference to Microsoft DAO 3.6 Object Library selection in
place

Other suggestions?

 

[Ken Snell \(MVP\)]

Post all the code so that we can see the context of the step. You don't tell
us what the DB object is nor what you declare it to be.

--

Ken Snell
<MS ACCESS MVP>

"gmckinlay(deletethispart)@hotmail.com"

 

[Guest]

Full code posted below.
However I do not thnk this is a coding problem.
It worked fine in December, and works fine now, until I open and close the
macro for editing. Simply opening and closing making no changes causes the
problem to appear.

Option Compare Database
Option Explicit

Function FillEmploy()

Dim DB As Database, Qry As QueryDef, Qry_def As String
Dim EMP_No As Recordset, Emp_Earnings As Recordset, Er As Recordset, EMPFill
As Recordset
Dim NL As String, TrmQrt As String, RetVal As Variant
Dim i As Integer, j As Integer, k As Integer, n As Integer
Dim ErId As String, SD As String, ED As String, Fill As Boolean
Dim ib As Integer, ie As Integer, IFirst As Integer, IHoldB As Integer,
IHoldE As Integer
Dim IStart As Boolean, Init As Boolean
Dim ErNo(150), ErEst(150), ErEarn(150), EmpDate(150) As String
Dim HoldEmpNo As Variant, HoldEarn As Variant
Dim ErSum As Integer

Set DB = CurrentDb

RetVal = SysCmd(SYSCMD_SETSTATUS, "Running Gross Output Crosstab")
Set EMP_No = DB.OpenRecordset("ct01 UnFilled Employees", dbOpenDynaset)
RetVal = SysCmd(SYSCMD_SETSTATUS, "Running employees Crosstab")
Set Emp_Earnings = DB.OpenRecordset("ct03 UnFilled Earnings", dbOpenDynaset)
Set Er = DB.OpenRecordset("Employer_List", dbOpenTable)
Set EMPFill = DB.OpenRecordset("EmployerHistoryFill", dbOpenTable)

NL = Chr$(13) & Chr$(10)
TrmQrt = InputBox("Please enter terminal quarter of fiscal year" & NL & "For
example 2005_3")

EmptyTable ("EmployerHistoryFill")

n = EMP_No.Fields.Count - 1
ib = 10
ie = n
EMP_No.MoveFirst
For i = ib To ie
EmpDate(i) = EMP_No(i).Name
If EmpDate(i) = TrmQrt Then
ie = i
Exit For
End If
Next

RetVal = SysCmd(SYSCMD_SETSTATUS, "Generating Data")

k = 0
Do Until EMP_No.EOF
k = k + 1
ErId = EMP_No!EMP_IDCK
Fill = EMP_No(7)
SD = EMP_No(8)
ED = EMP_No(9)
For i = ib To ie
ErNo(i) = EMP_No(i)
ErEst(i) = 0
ErEarn(i) = Emp_Earnings(i)
Next

If Fill Then
Init = True
IFirst = 0
For i = ib To ie

If Not IsNull(ErNo(i)) Then
' Identify I for first real value
If IFirst = 0 Then
IFirst = i
End If
If IStart And Not Init Then
' Fill between values
HoldEmpNo = (HoldEmpNo + ErNo(i)) / 2
HoldEarn = (HoldEarn + ErEarn(i)) / 2
For j = IHoldB + 1 To IHoldE
ErNo(j) = HoldEmpNo
ErEarn(j) = HoldEarn
ErEst(j) = 1
Next
End If
IHoldB = i
HoldEmpNo = ErNo(i)
HoldEarn = ErEarn(i)
IStart = False
Init = False
Else
IHoldE = i
IStart = True
End If
Next

' Fill to End date or terminal quarter based on last real value
For j = IHoldB + 1 To n
If EmpDate(j) <= ED Then
ErNo(j) = HoldEmpNo
ErEarn(j) = HoldEarn
ErEst(j) = 2
End If
Next

' Fill from Start date based on first real value
For j = ib To (IFirst - 1)
If EmpDate(j) >= SD Then
ErNo(j) = ErNo(IFirst)
ErEarn(j) = ErEarn(IFirst)
ErEst(j) = 3
End If
Next

End If

' Fill a value of 0.001 if all quarters = null or 0
ErSum = 0
For i = ib To ie
If Not IsNull(ErNo(i)) Then
ErSum = ErSum + ErNo(i)
Exit For
End If
Next
If ErSum = 0 Then
For i = ib To ie
ErNo(i) = 0.001
ErEarn(i) = 0.001
ErEst(i) = 4
Next
End If

For i = ib To ie
If ErNo(i) > 0 And ErId <> "0000004" Then
' Exclude dummy id 0000000 used to force cross tablations to show
all quarters
EMPFill.AddNew
EMPFill!EMP_IDCK = ErId
EMPFill!State = 4
EMPFill!Yr_Qtr = EmpDate(i)
EMPFill!EMPLOYEES = ErNo(i)
EMPFill!NGROSS = ErEarn(i)
EMPFill!Fill = ErEst(i)
EMPFill.Update
End If
Next

' Fill a value of 0.001 if total data for employer=0
If IFirst = 0 Then
If i = ie Then
ErNo(ie) = 0.001
ErEarn(ie) = 0.001
ErEst(ie) = 4
End If
End If



EMP_No.MoveNext
Emp_Earnings.MoveNext
RetVal = SysCmd(SYSCMD_SETSTATUS, "Generating Record # " & Str(k))

Loop

Emp_Earnings.Close
EMP_No.Close
Er.Close
EMPFill.Close
' EmpNoFill.Close
' Ind.Close
RetVal = SysCmd(SYSCMD_CLEARSTATUS)

MsgBox "Data Filled"

End Function

 

[Ken Snell \(MVP\)]

Your VBA code never sets DB or the Recordset variables to Nothing. That runs
a risk of memory leak. You should put these code steps in the procedure near
the end (after you close the recordsets):

Set Emp_Earnings = Nothing
Set EMP_No = Nothing
Set Er = Nothing
Set EMPFill = Nothing
Set DB = Nothing

Do you declare DB as a global variable anywhere in the database file (e.g.,
Public DB As Database)? That can lead to confusion when you then have a
local variable with the same name in a procedure.

Same question for EMP_No ... is it declared anywhere as a global variable?

Do you have a Timer event running on any form while you're in the Visual
Basic Editor? If yes, what is that code doing?


A bit off topic, but if you mean for this line of code to declare all the
variables as String variables, it won't work:

Dim ErNo(150), ErEst(150), ErEarn(150), EmpDate(150) As String

You need to rewrite this as

Dim ErNo(150) As String, ErEst(150) As String, ErEarn(150) As String,
EmpDate(150) As String
--

Ken Snell
<MS ACCESS MVP>


"gmckinlay(deletethispart)@hotmail.com"