Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query returns 20 records from QBE, but 13 when run thru DAO objects via code

Status
Not open for further replies.

Pack10

Programmer
Feb 3, 2010
495
US
Got a wierd one here. I have a query which returns 20 records when run from the QBE.
I actually run the query thru VBA code and pass the recordset to excel. When I run the query thru
the code module, I get 13 records copied from the query in Access that make it into Excel.
Any ideas on what could be happening here.???????
 
Here is the code: When I test the recordset value (rs) down in the copyfromRecordset, it correctly shows 20 in the
rs.RecordCount property, so I dont understand why only 13 records end up in the worksheet tab.....


Function OutPut_2nd_Fail_Date()

On Error Resume Next

Dim fld As DAO.Field
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim x As Integer
Dim dte_Parameter As Date

Set db = CurrentDb
Set qdf = db.QueryDefs("2nd Fail Date")
dte_Parameter = DateValue(Forms!frmImport!dt_Meishan)
qdf.Parameters(0) = dte_Parameter
'qdf.Parameters(0) = #6/28/2013#
Set rs = qdf.OpenRecordset

DoCmd.SetWarnings False

Set fld = rs.Fields(0)

' This code fires the Excel from Access.

Dim objXLApp As Object
Dim objXLBook As Object
Dim objXLSheet As Object
Dim xlRng As Object
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Application.Visible = True

Set objXLBook = objXLApp.Workbooks.Open("S:\Quality Metrics\Input Files\Weekly SP Input Files\Query_Results.xlsx")
Set objXLSheet = objXLBook.Sheets("2nd Fail Date")
objXLSheet.Activate

objXLSheet.Range("A1:DC2000").ClearContents

' print column headers
x = 1
For Each fld In rs.Fields
objXLSheet.Cells(1, x).Value = fld.Name
x = x + 1
Next fld

Set xlRng = objXLSheet.Cells(2, 1)
xlRng.CopyFromRecordset rs
objXLSheet.Columns.AutoFit

' clean up
objXLApp.Application.Visible = False
objXLBook.Save
objXLBook.Close (True)

Set objXLApp = Nothing
Set objXLBook = Nothing
Set objXLSheet = Nothing
Set xlRng = Nothing

'objXLSheet.Range("A2:DC2000").ClearContents
'objXLSheet.Range("A2").CopyFromRecordset rs


' clean up
objXLApp.Application.Visible = False
objXLBook.Save
objXLBook.Close (True)

Set objXLApp = Nothing
Set objXLBook = Nothing
Set objXLSheet = Nothing
Set xlRng = Nothing

End Function
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top