VB not working (1 Viewer)

[jd_boss_hogg]

Hoping some kind hearted sole can help me....(i'm a novice).
I have the following code running from a button on a form, which uses "NewCsvExport" query as its record source. I want to open and print a report for each record in the query. When running, i just bomb straight out to 'on error'. I think its maybe the way i have my recordset set up ?

Private Sub Command18_Click()
On Error GoTo Export_Invoice_Err

       Dim rstJobs
        Dim dbs As DAO.Recordset
           

      DoCmd.OpenQuery "NewCsvExport", acViewNormal, acReadOnly
             
       Set rstJobs = dbs.OpenRecordset("NewCsvExport", dbOpenDynaset)
              
        If rstJobs.BOF And rstJobs.EOF Then
            Exit Sub ' empty record set, nothing to do here
        End If
        
            Do While Not rstJobs.EOF
                
                ' print invoices to emails'
           
                DoCmd.OpenReport "PDFInvoiceUK", acViewNormal, , [Rate] = "T1" Or "T0" And [IN_VIA] <> "Re-Make"
                DoCmd.OpenReport "PDFInvoiceEurope", acViewNormal, , [Rate] = "T4" And [IN_VIA] <> "Re-Make"
                DoCmd.OpenReport "PDFInvoiceForeign", acViewNormal, , [Rate] = "T9" And [IN_VIA] <> "Re-Make"
           
                rstJobs.MoveNext
            Loop
     

    DoCmd.Close acQuery, "NewCsvExport"
      DoCmd.OpenReport "Invoices_to_sage"

      
      DoCmd.OpenQuery "NewCsvNotExported"
      Forms!New_CSV_export!.Refresh
    
    
    MsgBox "All Done !"
    Exit Sub
    
Export_Invoice_Err:
    MsgBox "Problems !"
  
End Sub

 

[namliam]

It is your openreport methods, the "Where clause" is supposed to be a string... not an "actual" where clause....
i.e. not
DoCmd.OpenReport "PDFInvoiceUK", acViewNormal, , [Rate] = "T1" Or "T0" And [IN_VIA] <> "Re-Make"

but
DoCmd.OpenReport "PDFInvoiceUK", acViewNormal, , "[Rate] = ""T1"" Or ""T0"" And [IN_VIA] <> ""Re-Make"" "

also... your where clause is flawed, in "SQL" you cannot do an OR like that...
It is either:
- ( [Rate] = ""T1"" or [Rate] = ""T0"" )
- [Rate] in (""T1"", ""T0"")

Good luck on this project

 

[jd_boss_hogg]

Thanks Namliam, but it appears that it's not those line. Ive rem'd out those DoCmd lines so all the script does is loop through the record set, and it still bombs out. The open query works, because the table displays on screen, but then it jumps straight to 'problems"...

My query is an append query - could that be the problem ?

 

[jd_boss_hogg]

Ok, this no longer crashes, but instead of the OpenReport running on the 5 items in my query result, it appears to be running on the whole database table of thousands...

Private Sub Command18_Click()
On Error GoTo Export_Invoice_Err
  
      
       Dim rstJobs
Dim dbs As DAO.Recordset '
           
 
      DoCmd.OpenQuery "NewCsvExport", acViewNormal, acReadOnly
     
              
              Set rstJobs = CurrentDb.OpenRecordset("NewCsvExport")
              
        If rstJobs.BOF And rstJobs.EOF Then
            Exit Sub ' empty record set, nothing to do here
        End If
        
            Do While Not rstJobs.EOF
                
                ' print invoices to emails'
           
           DoCmd.OpenReport "PDFInvoiceUK", acViewNormal '
           
                rstJobs.MoveNext
            Loop
     
  
    DoCmd.Close acQuery, "NewCsvExport"
      DoCmd.OpenReport "Invoices_to_sage"

      
      DoCmd.OpenQuery "NewCsvNotExported"
      Forms!New_CSV_export!.Refresh
    
    MsgBox "All Done !"
    Exit Sub
    
Export_Invoice_Err:
    MsgBox "Problems !"
  
End Sub

 

[JHB]

Comment out your error handling (until it runs ok), then you'll see what code line cause the problem.

 

[namliam]

if your query NewCsvExport is an append query then yes this is your problem, you cannot open an action query / append query in a recordset inside VBA.

Strongly suggest you have a read of this: http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=Name+convention