Re-try - BOF-EOF error

JpaulH · May 4th, 2006, 10:48 AM

I getting a msgbox "BOF or EOF is true, or the current record has been deleted, request operation requires a current record"
Can anyone explain this?

Here is my code:
Private Sub cmdNewtrans_Click()
On Error GoTo CmdRepErr

    Dim xlAdd As Integer
    Dim xlApp As Excel.Application
    Dim xlDoc As Excel.Worksheet
    Dim FileStr As String
    Dim Rst2 As ADODB.Recordset

    Dim DocPname As String
    Dim DocCname As String
    Dim DocNname As String

    Me.Requery
    Me.Refresh

    Set Cnx = New ADODB.Connection
    Set Rst = New ADODB.Recordset
    Set Rst2 = New ADODB.Recordset
    Set fso = CreateObject("Scripting.FileSystemObject") 'creates an activeX object

    Cnx.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Access.CurrentDb.Name & ";Persist Security Info=False"
    Cnx.Open
    If Rst.State = 1 Then Rst.Close

    'opens first Record set from tbltransmittals table
    Rst.Open "select * from tblTransmittals where [TID] = " & [TIDNo], Cnx, adOpenKeyset, adLockOptimistic

    'Opens next set of records from tbltransdwgs
    Rst2.Open "select * from tblTransdwgs where [TID] = " & [TIDNo], Cnx, adOpenKeyset, adLockOptimistic

    Set xlApp = New Excel.Application 'set the application object to a new excel session
    xlApp.Workbooks.Open fso.getparentfoldername(Access.CurrentDb.Name) & "\TRANS_template.xls" 'open the template xls file
    xlApp.Visible = True 'bring it up on screen
    Set xlDoc = xlApp.ActiveSheet 'set the document object to the template xls we just opened

    xlDoc.PageSetup.CenterHorizontally = True
    xlDoc.PageSetup.CenterVertically = True

    xlDoc.Range("N11").Value = Rst.Fields("TID")
    xlDoc.Range("AL8").Value = Rst.Fields("ProjNo")
    xlDoc.Range("AL6").Value = Rst.Fields("ProjTitle")
    'xlDoc.Range("AL7").Value = Rst.Fields("ProjTitle2")
    xlDoc.Range("AL9").Value = Rst.Fields("ProjEng")
    xlDoc.Range("N12").Value = Rst.Fields("TransBy")
    xlDoc.Range("N13").Value = Rst.Fields("Date")
    'xlDoc.Range("N14").Value = Rst.Fields("Tphone")
    xlDoc.Range("AL11").Value = Rst.Fields("Courier")
    xlDoc.Range("AL12").Value = Rst.Fields("Copyto")
    xlDoc.Range("AL13").Value = Rst.Fields("Company")
    xlDoc.Range("AL14").Value = Rst.Fields("CPhone")
    xlDoc.Range("AS57").Value = Rst.Fields("signed")
    xlDoc.Range("B52").Value = Rst.Fields("Comments")

    DocPname = cboProjNo.Value
    DocCname = cboCompany.Value
    DocNname = txtTID

    xlAdd = 16 'sets xladd varible - row 16

    'reset this to the first recordset.
     Do Until Rst.EOF 'for each record we just opened with the recordset
        If Rst.AbsolutePosition > 20 Then Exit Do
        If Rst.AbsolutePosition = 1 Then
            Select Case Rst.Fields("Status")
                Case "Issued for Approval": xlDoc.Range("C41").Value = "X"
                Case "Issued for Construction": xlDoc.Range("C43").Value = "X"
                Case "As Requested": xlDoc.Range("C45").Value = "X"
                Case "For Review & Comment": xlDoc.Range("C47").Value = "X"
                Case "Issued for Bid": xlDoc.Range("C49").Value = "X"
                Case "Issed for Estimates Only": xlDoc.Range("X41").Value = "X"
                Case "Approved as Noted": xlDoc.Range("X43").Value = "X"
                Case "Returned for Corrections": xlDoc.Range("X45").Value = "X"
                Case "For Your Use": xlDoc.Range("X47").Value = "X"
                Case "Prints Returned from Us": xlDoc.Range("X49").Value = "X"
                Case "Resubmit for Approval": xlDoc.Range("AW41").Value = "X"
                Case "Return for Distribution": xlDoc.Range("AW43").Value = "X"
                Case "Return Corrected Prints": xlDoc.Range("AW45").Value = "X"
                Case Else: xlDoc.Range("AW47").Value = "X"
            End Select
        End If

        'This is a part of the 2nd record set Cstr turns into string
        xlDoc.Range("A" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Rst2.Fields("Copies") 'basically, the range property of the document object is used
        xlDoc.Range("F" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Rst2.Fields("DwgNo") 'to get or set the values of each cell. Notice how the range
        xlDoc.Range("U" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Trim(Rst2.Fields("Rev")) 'object calls on a certain cell and sets the value
        xlDoc.Range("W" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Trim(Rst2.Fields("FileName")) 'to the corresponding field in the recordset.
        xlDoc.Range("AI" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Rst2.Fields("Media") 'im adding the absolute position of the recordset to 16 because
        xlDoc.Range("AN" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Rst2.Fields("DrawingTitle") 'the first record in the xls template starts at row 17...

' If Rst2.Fields("New") = -1 Then xlDoc.Range("BT" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = "X" 'and the first absoluteposition in the recordset is 1.
' If Rst2.Fields("Revised") = -1 Then xlDoc.Range("BU" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = "X" '1 + 16 = 17 and then the absoluteposition in the recordset will go
' If Rst2.Fields("Unrevised") = -1 Then xlDoc.Range("BV" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = "X" 'up as you execute rst.movenext. So this section of code
' 'fills the ENTIRE xl spreadsheet no matter how many records are being reported.
        Rst2.MoveNext 'move to the next record AND increment the absoluteposition value by 1
    Loop

    Rst.Close 'close recordset object
    Rst2.Close
    Cnx.Close 'close connection object
   'FileStr = fso.getparentfoldername(Access.CurrentDb.Name) & "DocPname" & "_" & "DocCname" & Format(Date, "mmddyy") & "_" & "DocNname"
    'xlDoc.SaveAs FileStr

Exit Sub
CmdRepErr:
    MsgBox Err.Description
    Resume

End Sub

I don't quite understand recordcount and positions yet.
Thanks very much for any help!

John Paul

mmcdonal · May 4th, 2006, 11:40 AM

Yes, there are no records in your recordset.

Right after you pull your recordset, add this:

If rs.RecordCount <> 0 Then

   'all the rest of your code I think

End If

This will handle the situation where you make a parameter query and no records are returned.

However, you have a note in your code to "'reset this to the first recordset." What I think you mean is "reset to the first record" which would be

Rst.MoveFirst

That missing line could also be the culprit here.

Also, after

Do Until Rst.EOF

You have

Rst2.MoveNext
Loop

So I am not sure if you are incrementing these correctly.

After you open your recordsets, you should have this syntax

'-----
rst.MoveFirst
rst2.MoveFirst

If rst.RecordCount <> 0 Then
   Do Until rst.EOF

   If rst2.RecordCount <> 0 Then
      Do Until rst2.EOF
      ...
      rst2.MoveNext
      Loop
   End If

   rst.MoveNext
   Loop
End If
'-----

I can't find this structure (without the If Then), but I may be missing it.

HTH

mmcdonal

JpaulH · May 4th, 2006, 02:16 PM

Thanks, I'm going to give this a try.
Here is the orig. code that I took this from:
It may give a clue to the code structure:
This works great, but I cant seem to find where
the code fixes the BOF-EOF problem.
Private Sub cmdReport_Click()
On Error GoTo CmdRepErr
    Dim xlAdd As Integer
    Dim xlApp As Excel.Application 'reference excel application object
    Dim xlDoc As Excel.Worksheet 'reference excel worksheet object. Worksheet object is basically a document object
    Dim FileStr As String 'string for the filename i will rename the xls file to
    Dim wrkOrder As String
    Dim projNum As String
    Dim transNum As String
    Dim transDat As String
    Dim Rst2 As ADODB.Recordset

    Me.Requery
    Me.Refresh

    TransCmd = "XL"

    Set Cnx = New ADODB.Connection 'set cnx to a new adodb connection
    Set Rst = New ADODB.Recordset 'set rst to a new adodb recordset
    Set Rst2 = New ADODB.Recordset
    Set fso = CreateObject("Scripting.FileSystemObject") 'set fso to the filesystemobject
    'filesystemobject allows you to do almost any kind of file operation...such as renaming, getting extensions or filepaths, etc
    Cnx.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Access.CurrentDb.Name & ";Persist Security Info=False"
    Cnx.Open 'set the connectionstring and open the connection
    If Rst.State = 1 Then Rst.Close 'if the recordset is open then close it
    Rst.Open "select * from Transmittals_tbl where ([Pick] = -1 and " & Right(Me.Filter, Len(Me.Filter) - 1) & " order by [Trans_No]", Cnx, adOpenKeyset, adLockOptimistic
    'open every record from linelist_tbl that is selected and also meets the current filter.
    If Rst.RecordCount > 1 Then
        Rst.Close
        DoCmd.OpenForm "SelectTrans"
        Exit Sub
    End If
    If Rst.RecordCount <> 0 Then
        If IsNull(Rst.Fields("date")) = True Then
            transDat = ""
        Else
            transDat = Rst.Fields("date")
        End If
        If IsNull(Rst.Fields("trans_no")) = True Then
            transNum = ""
        Else
            transNum = Rst.Fields("trans_no")
        End If

        Rst2.Open "select * from transmittaldwgs_tbl where ([transdate] = '" & Format(transDat, "mmddyy") & "' and trans_no = '" & transNum & "')", Cnx, adOpenKeyset, adLockOptimistic
    Else
        Rst.Close
        Exit Sub
    End If

    Set xlApp = New Excel.Application 'set the application object to a new excel session
    xlApp.Workbooks.Open fso.getparentfoldername(Access.CurrentDb.Name) & "\TRANS_template.xls" 'open the template xls file
    xlApp.Visible = True 'bring it up on screen
    Set xlDoc = xlApp.ActiveSheet 'set the document object to the template xls we just opened

    xlDoc.PageSetup.CenterHorizontally = True
    xlDoc.PageSetup.CenterVertically = True
    'xlDoc.PageSetup.LeftMargin = 0.5 'set margins
    'xlDoc.PageSetup.RightMargin = 0.5

    xlDoc.Range("AL6").Value = Rst.Fields("Project_Title")
    xlDoc.Range("AL7").Value = Rst.Fields("Proj_Title2")
    xlDoc.Range("AL8").Value = Rst.Fields("ProjNo")
    xlDoc.Range("AL9").Value = Rst.Fields("Project_Engineer")
    xlDoc.Range("N11").Value = Rst.Fields("transdate") & " - " & Rst.Fields("Trans_No")
    xlDoc.Range("N12").Value = Rst.Fields("Transmitted_by")
    xlDoc.Range("N13").Value = Rst.Fields("Date")
    xlDoc.Range("N14").Value = Rst.Fields("Phone Number")
    xlDoc.Range("AL11").Value = Rst.Fields("Transmitted_To")
    xlDoc.Range("AL12").Value = Rst.Fields("CC_To")
    xlDoc.Range("AL13").Value = Rst.Fields("Company")
    xlDoc.Range("AL14").Value = Rst.Fields("TransToPhone")
    xlDoc.Range("AS57").Value = Rst.Fields("Approval_Sign")
    xlDoc.Range("B52").Value = Rst.Fields("Comments1")
    xlDoc.Range("B53").Value = Rst.Fields("Comments2")
    xlDoc.Range("B54").Value = Rst.Fields("Comments3")
    xlDoc.Range("H56").Value = Rst.Fields("CopyTo1")
    xlDoc.Range("H57").Value = Rst.Fields("CopyTo2")

    xlAdd = 16

    Do Until Rst2.EOF 'for each record we just opened with the recordset
        If Rst2.AbsolutePosition > 20 Then Exit Do
        If Rst2.AbsolutePosition = 1 Then
            Select Case Rst2.Fields("Status")
                Case "Issued for Approval": xlDoc.Range("C41").Value = "X"
                Case "Issued for Construction": xlDoc.Range("C43").Value = "X"
                Case "As Requested": xlDoc.Range("C45").Value = "X"
                Case "For Review & Comment": xlDoc.Range("C47").Value = "X"
                Case "Issued for Bid": xlDoc.Range("C49").Value = "X"
                Case "Issed for Estimates Only": xlDoc.Range("X41").Value = "X"
                Case "Approved as Noted": xlDoc.Range("X43").Value = "X"
                Case "Returned for Corrections": xlDoc.Range("X45").Value = "X"
                Case "For Your Use": xlDoc.Range("X47").Value = "X"
                Case "Prints Returned from Us": xlDoc.Range("X49").Value = "X"
                Case "Resubmit for Approval": xlDoc.Range("AW41").Value = "X"
                Case "Return for Distribution": xlDoc.Range("AW43").Value = "X"
                Case "Return Corrected Prints": xlDoc.Range("AW45").Value = "X"
                Case Else: xlDoc.Range("AW47").Value = "X"
            End Select
        End If
        xlDoc.Range("A" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Rst2.Fields("Copies") 'basically, the range property of the document object is used
        xlDoc.Range("F" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Rst2.Fields("DrawingNumber") 'to get or set the values of each cell. Notice how the range
        xlDoc.Range("U" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Trim(Rst2.Fields("Revision")) 'object calls on a certain cell and sets the value
        xlDoc.Range("W" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Trim(Rst2.Fields("File_Name")) 'to the corresponding field in the recordset.
        xlDoc.Range("AI" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Rst2.Fields("Media") 'im adding the absolute position of the recordset to 16 because
        xlDoc.Range("AN" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = Rst2.Fields("Drawing Title") 'the first record in the xls template starts at row 17...
        If Rst2.Fields("New") = -1 Then xlDoc.Range("BT" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = "X" 'and the first absoluteposition in the recordset is 1.
        If Rst2.Fields("Revised") = -1 Then xlDoc.Range("BU" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = "X" '1 + 16 = 17 and then the absoluteposition in the recordset will go
        If Rst2.Fields("Unrevised") = -1 Then xlDoc.Range("BV" & CStr(xlAdd + Rst2.AbsolutePosition)).Value = "X" 'up as you execute rst.movenext. So this section of code
                                                                'fills the ENTIRE xl spreadsheet no matter how many records are being reported.
        Rst2.MoveNext 'move to the next record AND increment the absoluteposition value by 1
    Loop

    Rst.Close 'close recordset object
    Rst2.Close
    Cnx.Close 'close connection object
    FileStr = fso.getparentfoldername(Access.CurrentDb.Name) & "\TransLog" & Format(Date, "mmddyy") & "_" & Format(Time, "hhmmss")
    'JRB * xlDoc.SaveAs FileStr 'saveas the string we just made by concatenating the database path with the current date and time; formatted as numbers.
Exit Sub
CmdRepErr:
    MsgBox Err.Description
    Resume
End Sub

Thanks

John Paul