TransferSpreadSheet anomaly (A2007)

[JimS]

I posted this in another forum. No help so far. Hoping someone here has an
idea...

The following code has been running just fine. I've changed nothing related
to it anytime recently. I've checked the contents of "viewname". It contains
a valid query name. I've checked the contents of that query after the code
has been stopped,and it contains a simple, valid sql statement as shown. When
I run the code referenced by viewname, it runs perfectly, and it exports
manually just fine.

But, when the code shown executes, it shows an error message that
indicates the query is an action query whose action has produced an error
(fields not updated....) I'll try to quote the exact message.

What could possibly do this?

----------------Start Code

Private Sub btnRunExport_Click()
On Error GoTo btnRunExport_ClickErr

If Me.lbxStatusSelection.ItemsSelected.Count = 0 Then
MsgBox "Select at least one project status or press Cancel",
vbOKOnly, "Project Status Selection Error"
GoTo btnRunExport_ClickExit
End If

Dim strWhere As String
Dim comma As String
Dim varItem As Variant
comma = ""
Dim frm As Form
Set frm = Forms("frmExport")
Dim Flds As String
DoCmd.Hourglass True
'Flds = FieldNames("mdlCompositeProjectSummaryDiscipline")
Flds = " * "
strWhere = "Select " & Flds & " from " & frm!QueryName & " Where
ProjectStatus in ("
With Me.lbxStatusSelection
For Each varItem In .ItemsSelected
strWhere = strWhere & comma
strWhere = strWhere & .ItemData(varItem)
comma = ", "
Next varItem
End With

strWhere = strWhere & ")"
Dim ViewName As String
ViewName = "tmpExport" & frm!QueryName & Format(Now(), "yymmddhhmmss")
ADOXAddView ViewName, strWhere

Dim Filename
DoCmd.Hourglass False
Filename = GetExcelFileName(CurrentProject.Path)
If Len(Nz(Filename, "")) > 0 Then
DoCmd.Hourglass True
If Dir(Filename) <> "" Then
Kill Filename
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
ViewName, Filename
MsgBox "Export Complete"
End If

btnRunExport_ClickExit:
On Error Resume Next
'DoCmd.DeleteObject acQuery, ViewName
DoCmd.Hourglass False
DoCmd.Close acForm, Me.Form.Name
Exit Sub
btnRunExport_ClickErr:
If err.Number = 70 Then 'file busy, cannot be deleted
MsgBox "Export file: " & Filename & " is open in another program." _
& vbCrLf & vbCrLf _
& "Please close file or choose a different file name."
ElseIf err.Number = 7874 Then 'tmp table does not exist
Resume Next
Else
MsgBox "Error " & err.Number & ": " & err.Description, vbCritical, _
"Error detected in " & Me.Form.Name & ":btnRunExport_Click"
End If
Resume btnRunExport_ClickExit
End Sub

---------------------End Code

Error Message:

"The contents of fields in 2612 record(s) were deleted, and 0 record(s) were
lost due to key violations.....
Do you want to proceed anyway?....Yes/No/Help"

SQL contents of "viewname" query (as stored...)

SELECT *
FROM qryCompositeProjectSummary
WHERE (((qryCompositeProjectSummary.[ProjectStatus]) In (7,1,5,2,4)));

-------------------------End SQL

Again, I executed this EXACT sql query and it worked perfectly, an exported
to Excel perfectly.

What did I do wrong? (A2007 (12.0.6423.1000) SP2 MSO (12.0.6425.1000))


Jim