VB to print multiple reports in access

[Rik_StHelens]

Hi,

I am a complete newbie to VB so i hope i can explain my problem thoroughly.

I currently have a database which allows us to create invoices for all our customers who owe us money.

Each customer has a customer code, and at present to create an invoice i have to run a report, enter the code, and print the report.

The problem is i have to do over 100 invoices a week.

What i would like is a piece of code which would take all the customer codes from our STATMENT table (the system i take the data from is on DOS...8 character limits...) and print them one by one.

E.g. Get Code, Enter Code, Create Invoice, Print Invoice, Get Next Code and so on...

Firstly is this possible? and secondly how?

Probably a big question but i appreciate your help.

Any better ideas than VB would be welcome, VB was just my instinct..


Many thanks

PS i do understand the theoretics of programming to an extent, but i have never used VB. I'm guessing i'd need a variable which could be assigned a customer code, entered into the report/query, and then cleared to assign the next customer code in the table....but i have no idea how to code that

 

[pbaldy]

Presuming you have a report which produces the required data, you could probably remove the criteria and just print the report for everybody. It can probably be set up to break on each customer, so they would print on different pages. Failing that, I would take the criteria out of the report and use a technique like this to restrict it to one customer:

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

From there, it should be a fairly simple matter to open a recordset on your table, and loop through it to print a report out for each customer. More info on loops here:

http://www.granite.ab.ca/access/email/recordsetloop.htm

which is unfortunately more complex than it needs to be to demonstrate the recordset loop itself.

 

[Rik_StHelens]

Thanks for your answer. I did think of reorganising the whole report set but unfortunately i doubt its possible as each invoice must have a letter head which draws data from another table to add in addresses etc.

The code i have come up with (and please be kind as its my first time...) is as follows.

Sub PrintAll()

Dim CustCode
Dim PrintNum
Dim PrintTotal
PrintTotal = "SELECT COUNT (*) FROM " & "qry Statement Customer.Customer Code"
CustCode = "qry Statement Customer.Customer Code"
DoCmd.OpenReport "Statement Customer Report", , , "Customer Code = CustCode" & "qry Statement Customer.Customer Code"
If "Statement Customer Report.Outstanding Bal =0" Then
DoCmd.Close "Statement Customer Report"
PrintNum = PrintNum + 0
ElseIf "Statement Customer Report <>0" Then
DoCmd.PrintOut (acPrintAll)
DoCmd.Close "Statement Customer Report"
PrintNum = PrintNum + 1
End If
If PrintNum = PrintTotal
Do Until PrintNum = PrintTotal
If PrintNum = PrintTotal Then Exit Do
Loop
MsgBox ("You successfully printed " & PrintNum & " times")

End Sub


I am guessing i am still quite a way off, but i hope this helps to explain what im trying to do, and that you can help. I wrote it in the VB editor that comes up from access 2007, not sure what version that is. The end if statement is where my first error is flagged up. I also doubt that after each loop the CustCode variable will be assigned the next code in the recordset, but i would like it too.

Thanks for your help

 

[DCrake]

Rik

A bit confused over the letter head aspect of your code is this the address of the person being invoiced?

If so then in your statement you must have a link between the customers table and the invoices using the customer code. If you bring down the customers into your query you should be able to populate the adressee. Also you select has no condition on it. If you expanded it to where [Outstanding Amt] > 0 it would filter out all the customers with no balances.

Also would it not be prudent to only generate a customer invoice if the amount outstanding was greater than a specified value. As a customer receivng an invoice for 50p would be a little OTT.

If you can post a pre 2007 version I will take a look at it for you.

David

 

[Rik_StHelens]

Rik

A bit confused over the letter head aspect of your code is this the address of the person being invoiced?

If so then in your statement you must have a link between the customers table and the invoices using the customer code. If you bring down the customers into your query you should be able to populate the adressee. Also you select has no condition on it. If you expanded it to where [Outstanding Amt] > 0 it would filter out all the customers with no balances.

Also would it not be prudent to only generate a customer invoice if the amount outstanding was greater than a specified value. As a customer receivng an invoice for 50p would be a little OTT.

If you can post a pre 2007 version I will take a look at it for you.

David

Many thanks for your reply David, database is at the following link (i kept getting a connection failure trying to upload through this site)

http://rapidshare.com/files/292861995/Blank_Accounts_DB.mdb.html

I have been reconfiguring the database so i could convert to access 2003 (it used to link into some dos generated .dbf files from our job logging system) so i have imported the previoulsy linked tables and removed the data

there is one record, and the code for the customer is "tyr123"

Thank you very much for your time and help

 

[pbaldy]

That bears no real resemblance to the link. See if this works:

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

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

  Do While Not rs.EOF
    DoCmd.OpenReport "Statement Customer Report", , , "[Customer Code] = " & rs![Customer Code]
    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing

 

[DCrake]

Could not get back on line yesterday afternoon, anyway here is a copy of a report that is in the style you want. I have taken out any filtering to show you how to print more than one customers invoices.

David

 

[Rik_StHelens]

Could not get back on line yesterday afternoon, anyway here is a copy of a report that is in the style you want. I have taken out any filtering to show you how to print more than one customers invoices.

David

Thats great thank you.

The only problem i am having is that when i edit the report in design view to add our payment terms (at the bottom of the onvoice) + bank details (below customer address) for payment, it stops putting each invoice on a seperate page and just runs them one after the other.

Why would it do this?

Thanks again

 

[DCrake]

You need to put the customers details in the customer group footer. Where the total amount resides.

Send it back if you are still having issues (Pre 2007)

David

 

[Rik_StHelens]

You need to put the customers details in the customer group footer. Where the total amount resides.

Send it back if you are still having issues (Pre 2007)

David

I have reattached the file David

thank you for your patience.

I have put in the statements that we need on our real invoices, if you could show me how to place them in the invoice without messing up the "1 invoice per page" format we need i'd be very grateful

Thank you

 

[DCrake]

Cannot really see what the problem was, using the RptStatement report. From what I can gather the payment terms are the same for every customer along with the BACS details. So they need to go on the page footer. If each customer had different payment terms then you would have to include these terms in your query and display them at customer level in the customer group footer section.

I have attached a revised version for you to look at. If it is still wrong tell me exactly what the issue is.

David

 

[Rik_StHelens]

Cannot really see what the problem was, using the RptStatement report. From what I can gather the payment terms are the same for every customer along with the BACS details. So they need to go on the page footer. If each customer had different payment terms then you would have to include these terms in your query and display them at customer level in the customer group footer section.

I have attached a revised version for you to look at. If it is still wrong tell me exactly what the issue is.

David

My bad.

It was just that when i ran the report it stuck every invoice in continuously, but when i print it does break the pages up so that is great.

Thank you for your time!

It is very much appreciated

 

[DCrake]

Glad you finally have it sorted