Generating Invoice Numbers In Queries

  • Thread starter Thread starter Dave Gibson
  • Start date Start date
D

Dave Gibson

Hi,

Could anyone please assist me with the generation of Invoice numbers
in queries.

I have a set of monthly invoices that are printed from a report based
on a query. I wish to be able to nominate the starting invoice number
then have the Invoices incrementaly number themselves in the query.

The invoices are printed on separate pages by a page break on the
customer number in the report. The Invoice Numbers are 5 digit
numerical without any alphabetic or other characters

Is this possible in the query via some form of automatic number
generation after a prompted for value is entered.


Regards
 
A query can call a function and the function can do whatever you want.
However, would you not want the invoice numbers to be saved somewhere?
Surely, you dont just want them generated for a report and then forgotten
about???
I would think the numbers should be generated independently of the reporting
mechanism.

-Dorian
 
Dorian,

Thats exactly it, I have no requirement to store the Invoice numbers
anywhere. It is simply creating them "on the fly" with the option to
select the starting number then have then sequentially increased by 1
per record in the query

Dave
 
After thinking about this, I think the best way to do it is to increment the
numbers in the report event procedure, you can then increment the numbers at
whatever grouping level you need. Also, I'd have the starting number stored
in a single row table (which could be set via a form).
This table can be read in the report open event to get the initial number
and written at report termination to store the last used number.
You will need a global variable in the report to hold the number as it
increments.

-Dorian
 
Dorian,

It sound like the solution, however, as a novice in these matters I
could do with a bit of help in setting it up.

I can set up the table to store the starting number from a form but
where in the report does the number from the table go and how is it
incremenred across multiple invoices within the one report?

Regards
 
It depends on how your report sorts and/or groups (click View--Sorting and
Grouping). For instance if your input contains line items for each invoice
obviously you dont want a new invoice for each item but only when skipping to
a new group. So whether your code goes in the Details section or a Group
Header section, you need the On Format event. In here you will increment the
invoice number which must be declared as a global variable. You will then
have a control on the report in the relevant section for that invoice number.
In the report On Open you read the starting number from the table (you can
use DLookup function) and set the global variable and in the On Close event
write the last number back to the table from the global variable.
Hope this helps.

-Dorian
 
Back
Top