SQL in Access

[murthyspd]

I have a table with manager's name and employees' name. I have written a code that will pull the orders created by employees' in an excel sheet. The code is as follows :

The value in text box txtFromDate and txtToDate is from a calendar.
txtFromDate.SetFocus
dtFromDate = Format(CDate(txtFromDate.Text), "mm/dd/yyyy")

txtToDate.SetFocus
dtToDate = Format(CDate(txtToDate.Text), "mm/dd/yyyy")

While Not rsEmployeeDetails.EOF
MySQL = "SELECT * FROM tblOrderEntry WHERE Reviewed_By = '" & rsEmployeeDetails!badge_no & "' AND Review_Date BETWEEN " & "#" & dtFromDate & "# AND #" & dtToDate & "# ORDER BY sno"

rsManagerReport.Open MySQL, con, adOpenKeyset, adLockPessimistic

<code to transfer into excel sheet>
rsManagerReport.Close
Wend

The problem is that the excel sheet shows output of only the 1st employee in the list and fails for others. Any idea what could be going wrong.

 

[pbaldy]

You never move the rsEmployeeDetails recordset to the next record. I would think it gets caught in an endless loop.

 

[murthyspd]

Hi I do that below before Wend, though I did not paste it. I have pasted the whole code down. The issue is that, the excel sheet remains empty except the Heading. Another thing that I noticed is that if I give a specific badge_no instead of rsemployeedetails!badge_no in MySQL, access transfers data to Excel. It does not happen with a variable.

txtFromDate.SetFocus
dtFromDate = CDate(txtFromDate.Text)

txtToDate.SetFocus
dtToDate = CDate(txtToDate.Text)


Private Sub ManagerReport()

i = 1
j = 0

Set rsEmployeeDetails = New ADODB.Recordset
Set rsManagerReport = New ADODB.Recordset

rsEmployeeDetails.Open "select * from tblEmployeeDetails WHERE Designation='OP'", con, adOpenKeyset, adLockReadOnly

If rsEmployeeDetails.RecordCount > 0 Then
Form_ManagerForm.Repaint
Me.ProgressBar1.Max = rsEmployeeDetails.RecordCount
On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set ExcelApp = CreateObject("Excel.Application")

End If

Set ExcelWorkBook = ExcelApp.Workbooks.Add
Set ExcelWorkSheet = ExcelWorkBook.Sheets(1)

ExcelWorkSheet.Rows(1).Font.Bold = True

ExcelWorkSheet.Cells(i, 1).Value = "Badge No" 'Column A

ExcelWorkSheet.Cells(i, 2).Value = "Name" 'Column B

ExcelWorkSheet.Cells(i, 3).Value = "PO Number" 'Column C

ExcelWorkSheet.Cells(i, 4).Value = "Order Numbers" 'Column D


While Not rsEmployeeDetails.EOF

i = i + 2

MySQL = "SELECT * from tblOrderEntry where Reviewed_By= '" & rsEmployeeDetails!badge_no & "' AND Review_Date BETWEEN " & "#" & dtFromDate & "# AND #" & dtToDate & "# ORDER BY sno"
MsgBox MySQL
rsManagerReport.Open MySQL, con, adOpenKeyset, adLockPessimistic


' rsManagerReport.MoveLast
' MsgBox rsManagerReport.RecordCount & " " & rsEmployeeDetails!badge_no
rsManagerReport.MoveFirst

If rsManagerReport.RecordCount > 0 Then
With rsManagerReport
Do While Not .EOF
ExcelWorkSheet.Cells(i, 1).Value = rsEmployeeDetails!badge_no
ExcelWorkSheet.Cells(i, 2).Value = rsEmployeeDetails!Name
ExcelWorkSheet.Cells(i, 3).Value = !PO_Number
i = ExportOrderNumbers(rsManagerReport, i, 4) 'Function to print order numbers in column 6, one in each row.
'The function would return the row number from which next set of data would
'be printed.
.MoveNext

Loop

End With
rsManagerReport.Close
' ExcelWorkSheet.Rows(i).Font.Bold = True

ExcelWorkSheet.Rows(i).Font.Bold = True
ExcelWorkSheet.Cells(i, 1).Value = "Total"
ExcelWorkSheet.Cells(i, 2).Value = "Orders : " & rsManagerReport!Total
rsManagerReport.Close

rsManagerReport.Open "SELECT DISTINCT po_number FROM tblOrderEntry where Reviewed_By = '" & rsEmployeeDetails!badge_no & "' AND Review_Date BETWEEN " & "#" & dtFromDate & "# AND #" & dtToDate & "#", con, adOpenKeyset, adLockPessimistic
rsManagerReport.MoveLast
ExcelWorkSheet.Cells(i, 3).Value = "PO : " & rsManagerReport.RecordCount
rsManagerReport.Close
End If

ProgressBar1.Value = ProgressBar1.Value + 1
rsEmployeeDetails.MoveNext
Wend

ExcelApp.Visible = True
ExcelWorkSheet.Visible = xlSheetVisible


Set ExcelWorkSheet = Nothing
Set ExcelWorkBook = Nothing
Set ExcelApp = Nothing

MsgBox "Finished exporting to Excel.", vbInformation, "Export"
txtFromDate.SetFocus
txtFromDate.Text = ""
txtToDate.SetFocus
txtToDate.Text = ""
ProgressBar1.Value = 0
Else
MsgBox "No records found", vbInformation, "Order Entry Report"
End If

Set rsManagerReport = Nothing
end sub

 

[murthyspd]

Hi

Please help. It's urgent.

 

[pbaldy]

I didn't notice anything right off. Did you set a breakpoint and step through the code? That should reveal where it's going wrong.

 

[murthyspd]

I tried everything I could. Breakpoints, msgbox after each MySQL assignment but to no avail. Another thing that I noticed is that, it pulls data only for the first person in the EmployeeDetails. For others it does not pull data eventhough data is there.

 

[pbaldy]

Can you post a sample db?

 

[murthyspd]

Not able to upload the file. It is too huge. Can I send to your personal ID ?

 

[murthyspd]

Sorry, I compressed the file. I have zipped it.

 

[pbaldy]

I should have mentioned that I only have A2k here at work. I'll look at it at home tonight.

 

[RV]

Another thing that I noticed is that if I give a specific badge_no instead of rsemployeedetails!badge_no in MySQL, access transfers data to Excel. It does not happen with a variable.

Are you sure that badge_no is text string, as you refer to it as being a text string in your code:

'" & rsEmployeeDetails!badge_no & "'

If it's a numeric string, change this bit of your code into

" & rsEmployeeDetails!badge_no & "

RV

 

[murthyspd]

Data type is text. eg 592479

 

[pbaldy]

Not sure how I'm supposed to debug password protected code.

 

[murthyspd]

it's celeron

 

[murthyspd]

Hey Paul

I found the bug . I was closing the rsManagerReport inside the "If" condition. There will be cases when "If" condition would be false. And Access was re-opening the recordset with different parameters without closing it . I should have closed the recordset outside of the "If" condition Also. But I wonder, why it did not give error in

rsManagerReport.Open

when it was re-opening without closing. Any idea ?
Thank you for taking trouble in resolving. Do reply for the above query. Thanks again !!!