Vba code help

Ashfaque

Search Beautiful Girls from your town for night
Local time
Today, 15:51
Joined
Sep 6, 2004
Messages
897
Hi,

I have below code thru which I am exporting data to Excel. It works with other good...I mean opens excel and format as required but when it reaches to Red line (rst.MoveFirst), it producing "Runtime-error 3021...No Current Record" and halts. While there is record in the date range I selects.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim rst As DAO.Recordset
Dim iRow As Integer

Set rst = CurrentDb.OpenRecordset("SELECT RectNo, RectDept, RectSSaction, " & _
"RectWrkOrdLoc, RectRptdDate FROM T_Rect " & _
"WHERE RectRptdDate >= " & "#" & Format(Fdate, "dd/mm/yyyy") & "#" & " And " & _
"RectRptdDate <= " & "#" & Format(TDate, "dd/mm/yyyy") & "#" & " " & _
"ORDER BY T_Rect.RectNo asc;")

If Not rst.EOF And Not rst.BOF Then
MsgBox ("No Records In This Month"), vbInformation, "Null Records Inf."
Exit Sub
Else
Set objXL = New Excel.Application
objXL.Visible = True
.....
........
.........

Dim RowCount As Double
RowCount = 1

Dim X As String

rst.MoveFirst

Do While Not rst.EOF
....
.......
..........

Can someone help me ?

Thanks,

Ashfaque
 
Howzit

Perhaps this will work. I think the date format is around the wrong way and the checking for an empty recordset.

Code:
Set rst = CurrentDb.OpenRecordset("SELECT RectNo, RectDept, RectSSaction, " & _
"RectWrkOrdLoc, RectRptdDate FROM T_Rect " & _
"WHERE RectRptdDate >= " & "#" & Format(Fdate, [B]"mm/dd/yyyy"[/B]) & "#" & " And " & _
"RectRptdDate <= " & "#" & Format(TDate,[B] "mm/dd/yyyy"[/B]) & "#" & " " & _
"ORDER BY T_Rect.RectNo asc;")

[B]If rst.EOF And rst.BOF Then[/B]
    MsgBox ("No Records In This Month"), vbInformation, "Null Records Inf."
    Exit Sub
Else
   Set objXL = New Excel.Application
objXL.Visible = True
.....
........
.........

Dim RowCount As Double
RowCount = 1

Dim X As String

rst.MoveFirst

Do While Not rst.EOF
 
The dates will most likely still fail and the If Not rst.EOF ... part is wrong.
 

Users who are viewing this thread

Back
Top Bottom