Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    EOF not working right (Access 2000 SP2)

    We're trying to loop through a temporary table and get records for printing crystal report forms. It's working great except for some unknown reason the rst.EOF is processing additional blank forms when it is clearly past the last record. If I only have one set of forms to print, it prints out an extra set of blank form. When I do 2 sets, it prints out an extra set of blank forms. However, when we tested out 6 sets of forms, it printed out four sets of blank forms before it decided that EOF meant EOF. I haven't seen this happen on my other looping and can't figure out what to do. Here's my code:

    <font face="Comic Sans MS">Private Sub cmdPrint_Packing_List_Click()
    On Error GoTo ErrHandler

    Dim strPath As String
    Dim strPath2 As String
    Dim job As Integer
    Dim Handle As Integer
    Dim moptionx As PEPrintOptions
    Dim iResult As Integer
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strCurrPONum As String
    Dim strPrevPONum As String

    DoCmd.SetWarnings False
    ' this updates the print flag for any records that happen to be in a certain condition
    DoCmd.OpenQuery "qupdFlagMIKCOM"
    'this makes a temporary table that is used for the forms
    DoCmd.OpenQuery "qmaktmpMIK"
    'this makes a temporary table for the first record to be printed
    DoCmd.OpenQuery "qmaktmpMIKPrint"

    ' strPath = "X:form1.rpt"
    strPath = "X:form1MIK.rpt"
    strPath2 = "X:form2MIK.rpt"
    ' StartDoc (strPath)
    moptionx.StructSize = PE_SIZEOF_PRINT_OPTIONS
    moptionx.collation = PE_UNCOLLATED
    'the RecCount goes through the first temporary table and if there's no data, no forms print out
    If RecCount = 0 Then
    MsgBox "No MIK Labels to print"
    Exit Sub
    Else
    strSQL = "SELECT * FROM [tmpMIK] ORDER BY [PONum]"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    'here is where the EOF begins
    Do While rst.EOF = False
    'this is the crystal report stuff we need to call in to print the forms
    Handle = PEOpenEngine
    job = PEOpenPrintJob(strPath)
    Handle = PESetPrintOptions(job, moptionx)
    Handle = PEOutputToPrinter(job, 1)
    Handle = PEStartPrintJob(job, True)
    PEClosePrintJob (job)
    job = PEOpenPrintJob(strPath2)
    Handle = PESetPrintOptions(job, moptionx)
    Handle = PEOutputToPrinter(job, 1)
    Handle = PEStartPrintJob(job, True)
    PEClosePrintJob (job)
    PECloseEngine
    ' this updates the print flag, indicating that the form has printed
    DoCmd.OpenQuery "qupdFlagMIK"
    'this selects the next record to be printed
    DoCmd.OpenQuery "qmaktmpMIKPrint"
    rst.MoveNext
    Loop
    End If

    DoCmd.SetWarnings True

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub</font face=comic>

    Any suggestions? <img src=/S/help.gif border=0 alt=help width=23 height=15> We're getting desperate here....
    Carpy Diem, it&#39;s .

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: EOF not working right (Access 2000 SP2)

    A quick glance doesn't yield anytthing obvious, but try this: immediately below the line

    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    insert a new line

    If rst.RecordCount = 0 Then GoTo ExitHandler

    You will have to move DoCmd.SetWarnings True to below the ExitHandler: label; that is a good idea anyway.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EOF not working right (Access 2000 SP2)

    Hans,

    I put the line it but the extra sets still came out. The users can live with this for now, as it's a one-shot deal, but I'm just curious as to why EOF works everywhere else but in this particular case. I'm just crossing my fingers that we don't kill too many trees here.
    Carpy Diem, it&#39;s .

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: EOF not working right (Access 2000 SP2)

    A recordset will never have a non-zero RecordCount if it has no records, so if you still get blank sets, something else must be wrong.

    Are you sure that the temporary table tmpMIK is populated correctly? If it contains null values in unexpected places, you might get blank sets.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EOF not working right (Access 2000 SP2)

    I checked the temporary tmpMIK table and it didn't have any null lines. Since I'm using a maketable query, would this cause any problems? Should I use a delete and append situation instead?
    Carpy Diem, it&#39;s .

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: EOF not working right (Access 2000 SP2)

    In itself, there is no objection to a make-table query. Can you find out what happens if you set a breakpoint on cmdPrint_Packing_List_Click and single-step through the code?

  7. #7
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EOF not working right (Access 2000 SP2)

    I wonder if changing the code as follows would help: Change the do while statement to - Do While Not rst.EOF

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EOF not working right (Access 2000 SP2)

    When I step through it, it takes the first record, prints the forms, loops and gets the next record....then after getting the last record (I've verified this by having 6 test records and knowing what each one would do when being processed), it loops through again, prints the two forms, loops 3 more times (4 total for 6 records, or only once for 1-2 records), and then rst.EOF = true , in which case it ends the looping. At all other times rst.EOF = false. At first I thought I was in the infinite loop-'o-doom until I stepped through this a few times. See, doesn't make sense, does it?
    Carpy Diem, it&#39;s .

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EOF not working right (Access 2000 SP2)

    David,

    I originally had it as Do while NOT rst.EOF but that didn't work, either. I still got the extra forms.

    FYI to you and Hans - when we went through 1000 + records, there were lots and lots of blank forms that we semi-successfully flushed out of the print queue. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    Carpy Diem, it&#39;s .

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EOF not working right (Access 2000 SP2)

    David & Hans,

    Well, I figured it out, finally. What I did was uncheck some records and made a mental note of how many orders they represented. Then I set up a temporary field in the VBA code so I could see what the record count of the orders was. I had selected 5 orders, but the record count showed 13. So I revisted the query I was using for my recordset and I had it ORDER BY the PONum when I should have had GROUP BY. When I reworked this query to:

    <font face="Comic Sans MS">SELECT tmpMIK.PONum FROM tmpMIK GROUP BY tmpMIK.PONum;</font face=comic>

    I then had 5 orders looping through 5 times.

    Thanks for your help! It made me look closer at what I was doing and saved a few trees at the same time.
    Carpy Diem, it&#39;s .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •