Advice sought from newbie

[jd_boss_hogg]

Hi All - hoping some kind soul can point me in the right direction. Uing Acess 2003 across the company, i am responsible and have bodged together over the years a production control database. I spend 10 mins a week tinkering, so very much a newbie and i'm learning as i go along. Main problem i have is understanding the structuring of how things interact. I dont even know how to structure my question, its that bad !

I have a form that shows a table of all jobs dispatched in the last week that have not been invoiced. I have a button on this form which runs a query (table view), exports it to a csv file, then closes it. I now want to print invoices for each of these jobs. I cant get my head around how to structure it.

I have a macro that prints invoices (amongst other stuff) . It works great, but works on a record by record basis. So, i look at the finished job in a form, click a button which runs the macro, and hey-ho, invoice prints.

How do i get a macro to print all of the invoices that need doing? I cant get my head around how to run this print macro for every job in the query ?

Hoping somebody can take pity and give me a pointer. Any replies very greatly received....

 

[pbaldy]

Are you familiar with VBA? Sounds like you could open a recordset on the query and loop through its records. In that loop, put the current record number on the form and run the macro. Might be better ways, but that would leverage what you already have.

 

[jd_boss_hogg]

Im afraid i'm not very good with VBA - ...

Below is working on one record only, so i need to just create a loop... it's the loop i cant quite understand.

My script that hangs off a button is this (cropped):-

      DoCmd.OpenQuery "NewCsvExport", acViewNormal, acReadOnly
      DoCmd.Close acQuery, "NewCsvExport"
      DoCmd.OpenReport "Invoices_to_sage"
      DoCmd.OpenQuery "NewCsvNotExported"
      Forms!new_csv_Export!.Refresh
      
        DoCmd.RunMacro "printPDFinvoice"
End Sub

And my macro "printPDFinvoice" is this...

<Action Name="OpenReport">
<Argument Name="ReportName">PDFInvoiceUK</Argument>
<Argument Name="View">Print</Argument>
<Argument Name="WhereCondition">[jb-2001]![Job No]=[Forms]![New_CSV_export]![Job No]</Argument>

So this works by printing the first invoice in the list - but only that one invoice. How can i get it to print all of the invoices that are shown in the "NewCsvExport" query ?

 

[pbaldy]

This is my template code for a recordset loop:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    'code here
    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing

So where it says "code here" you'd have your code to place a value from the recordset in that form control and run the macro. By the way, this would be the VBA equivalent of that macro (with OpenReport of course):

http://www.baldyweb.com/wherecondition.htm