Button to generate InvoiceID and save it for specific records

[crxftw]

Hello! I have a database that contains sales done to the clients and I am working on invoice printing function. The idea is that on client details page I have 'Print Invoice' button which runs query that requests all data from this client sales whos details are open where checkbox 'Invoiced' = False and puts all data returned to report form which will be saved as PDF. When file is successfully saved 'Invoiced' will be set to 'True". Before that I run append query that should generate new field onto 'invoices' table and it also shows this invoiceid on the report. The problem I have is that when I add new sales or for testing purpose removed change 'Invoiced' to False under 'sales' table and run 'Print Invoice' again then this generated id via append will be overwritten and every time I press it I get same invoiceid for every report.



What I like to know how I can generated invoiceid before report is made and pin this invoiceid to the sales that were selected in case to keep track in future that what sales belong to that invoice or something.

Hope you understood and can help me, will include code if needed.


Regards

[Rhino373]

So I'll be honest I'm not entirely following what is occuring but it appears you're problem can be fixed in your append statement. You appear to be replacing ALL generated IDs rather than just those that are null. You shouldn't be generating an entire new column just the ID itself. The ID column should already exist in the Invoices table. So in your append your criteria should be "Is Null". This will only generate an ID for "New" entries that don't already have an ID.

Technically your ID field should be an AutoNumber field where it is populated for you each time a new sale is entered.

You should have a clients table that has an AutoNumber and that's how you'll relate any invoices to that client. Your invoices table should have an InvoiceID field and a cross reference field that contains the client ID to reference it back to a client.

If you don't wish the InvoiceID to be an AutoNumber but to be custom based on criteria of the sale, it needs to be created at the time the new sale entry is created not when you are ready to print reports.

[crxftw]

Hello, 'invoices' table has InvoicesID as autonumber, after that comes clientid from clients table, area from clients table which is prefix for invoice, status which will be set to 'Unpaid' as default, date which is current Date().

Append query SQL:

INSERT INTO invoices ( clientid, area, date, status )
SELECT qryInvoiceClient.clientid, qryInvoiceClient.area, Date() AS Date, "Unpaid" AS Status
FROM qryInvoiceClient
GROUP BY qryInvoiceClient.clientid, qryInvoiceClient.area, Date(), "Unpaid";

qryInvoiceClient:

SELECT sales.clientid, clients.area
FROM clients INNER JOIN sales ON clients.clientid = sales.clientid
WHERE (((sales.completed)=True) AND ((sales.invoiced)=False));