Run Time error "1004"

[JoeGKushner]

On a database I've inherited in Access 97, that has worked for months with no problem, all of a sudden I'm getting a run time error 1004 application-defined or object defined error.

It happens at the bottom of this code.

Code
Private Sub AM_Top_25_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "delete_ShortPartItems", acNormal, acEdit
DoCmd.OpenQuery "append_to_Short_Part_Items", acNormal, acEdit

Dim Db As Database
Dim Rst As Recordset
Set Db = Nothing
Dim xlApp As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
Dim recArray As Variant
Dim xlWb As Object
Dim xlWs As Object
Dim pCount As Integer
Dim pTest As String

boolXL = True

Set Db = CurrentDb

'Top 25 Query With Information from WIP Tab
Set Rst = Db.OpenRecordset("AftTop25_From_pcPsub_Details")
pCount = Rst.RecordCount

Set xlApp = CreateObject("excel.Application")
Set xlWb = xlApp.workbooks.Open("S:\PURCHASE\USERS\PCiufo\backup\Aftermarket_Top_25Template.xls ")
Set xlWs = xlWb.worksheets("Priority $")
xlWb.Application.DisplayAlerts = False

xlApp.Visible = True
xlApp.UserControl = True
fldCount = Rst.Fields.Count
recArray = Rst.GetRows(pCount)
recCount = UBound(recArray, 2) + 1

'Dumps data into Rows and Columns
xlWs.cells(2, 1).Resize(recCount, fldCount).Value = TransposeDim(recArray)

That line is where the error shows up. Strangely enough, in Excel, the data is pasted but there are more sheets after this part of the code that do similar things where data is being copied and pasted into Excel on different worksheets.

The TransposeDim is the standard one I see on the web.

Function TransposeDim(v As Variant) As Variant

Dim x As Long
Dim Y As Long
Dim Xupper As Long
Dim Yupper As Long
Dim tempArray As Variant

Xupper = UBound(v, 2)
Yupper = UBound(v, 1)

ReDim tempArray(Xupper, Yupper)
For x = 0 To Xupper
For Y = 0 To Yupper
tempArray(x, Y) = v(Y, x)
Next Y
Next x

TransposeDim = tempArray
End Function


Any one have any idea? I've played with some of the references, tried the repair and compact.

 

[Cronk]

I'd guess that the issue will be with the data set and the error occurs in your TransposeDim function.

Add error handling code to stop the code running and examine the values at the time to see exactly what the problem is.