I took a slightly different approach avoiding report builder at this point as it has no bearing.
Although not a macro it is a bit similar - a Trigger. A trigger is a macro of sort within your database which executes upon an occurrence such as inserting, updating or deleting a record. It can execute before or after the occurrence. Now I have dabbled in this a bit before but certainly not proficient in it. But this is small and seems to fit what you want.
With the sample attached, there is a new form -
Invoice_new based upon a table of the same name. It is similar to your invoice table. When a new record is created, the Trigger
invoice_yr is executed. The invoice date is searched for the last two digits of the year and that is used to find the highest sequence number already used in that year. If not found
0 is used, otherwise one in added to the number and that is used. For the test kept these in two separate non key fields.
With this setup you can enter or delete invoices in any year.
Because of that, if you were to edit an invoice date, the numbers cannot be modified. It is better to delete the old and enter a new item.
To stop the update from occurring, a second Trigger was created -
ck_date. this checks the date before entry and after to see if there was a change and if there was, an EXCEPTION is called ( named
The three items are in the Query section only for your perusal. They actually reside in the Firebird database installed through menu item
Take a look and if further information is needed please ask.
Invoices.odb (68.6 KB)
After making entries on the form, you can examine the results in the actual table.