Accessing an unbiund table (3 Viewers)

[ryetee]

I've been dragged out of retirement to do a 5 minute job. Unfortunately after 6 years I've forgotten more than I knew. So bear with me if I use the wrong names for things but....
I have a form that is bound to some query. It has sub forms bound to different queries. The form displays list of items and the quantity of those items that have been sold. There is a print button that prints a report which is basically the contents of the form. The user wants to add in an invoice number to the report. The invoice number is sequential starting from 1. The last invoice number is held on a stand alone table. I want to access that table. Use the value in the report and then update it by adding 1 to the field.
I've tried docmd.runsql which I've discovered is a no no. My latest attempts have been using record sets. Is this the right route. I'm getting nowhere fast at the moment.

 

[xavier.batlle]

Try using DMax and adding 1 to the value
https://learn.microsoft.com/en-us/office/vba/api/access.application.dmax

 

[Gasman]

I would have thought the invoice number would be tied to the invoice, not some report?

 

[plog]

The last invoice number is held on a stand alone table. I want to access that table. Use the value in the report and then update it by adding 1 to the field.
I've tried docmd.runsql which I've discovered is a no no.

Lots to unpack:

1. Why is DoCmd.RunSQL a no no? Sounds like a valid way to achieve this bad idea.

2. The invoice number is ephemeral? You calculate it, use it, update the table with it, but then its gone with the next update? The invoice number exists only on this report and is not stored anywhere else but temporarily in this one field?

3. It's not even permanent to the report? I run the report, your code runs and generates an invoice number and throws it on the report, I accidentally close out the report and reopen it and I get a new invoice number for the exact same report I ran 3 seconds ago? That's what you want to occur?

 

[ryetee]

Try using DMax and adding 1 to the value
https://learn.microsoft.com/en-us/office/vba/api/access.application.dmax

Not sure if this is what I need but I'll take a look

 

[ryetee]

I would have thought the invoice number would be tied to the invoice, not some report?

The report is the invoice. The invoice number is related to the order. I need a unique invoice number per order. Once the invoice is created I need to use the next number in sequence and then update that number for the next one to be created,

 

[ryetee]

Lots to unpack:

1. Why is DoCmd.RunSQL a no no? Sounds like a valid way to achieve this bad idea.

2. The invoice number is ephemeral? You calculate it, use it, update the table with it, but then its gone with the next update? The invoice number exists only on this report and is not stored anywhere else but temporarily in this one field?

3. It's not even permanent to the report? I run the report, your code runs and generates an invoice number and throws it on the report, I accidentally close out the report and reopen it and I get a new invoice number for the exact same report I ran 3 seconds ago? That's what you want to occur?

1. I used this and got some sort of error and when I was investigating I read, in more than 1 place, not to use it. This one https://nolongerset.com/avoid-docmd-runsql/ said Avoid DoCmd.RunSQL in Microsoft Access. What and why is it a bad idea?
2. No, it's stored on the order itself. When the invoice is first printed an invoice number is generated. The next invoice will have the value incremented by 1. So I want to read a "control" table for the next invoice number and then update it for the next use.
3. Yes it is. I may have badly worded things. AS soon as the report is run it will get stored and used from then on

 

[GPGeorge]

It's easier to offer example SQL when the actual table structure is available.

In this case, we assume that there is an order table, plus an order detail table if an order contains one or more line items.

This does sound standard: "The invoice number is related to the order. I need a unique invoice number per order."

If the invoice number is a field in that table, this works out in a straightforward manner. Unfortunately, the description so far doesn't make that clear. As others have questioned, it sounds like you are waiting to create that invoice number until the invoice is printed, rather than when the order is created. That's also reasonable, as an order might be generated on Monday and not invoiced until Friday, for example.

The key point, though, is that once the invoice number is created, it needs to be stored in the invoice field in the order table, not in a separate table. There is no point to having a separate "control" table because the appropriate way to store each invoice number is in the order table as part of the order.

SELECT Max(InvoiceNumber) +1 As NewInvoiceNumber FROM Orders

will generate the next invoice number in sequence when you need to update the order with that invoice number at the time it the invoice is printed.

Perhaps you should combine the two processes.

UPDATE Orders
SET InvoiceNumber = (SELECT Max(InvoiceNumber) +1 As NewInvoiceNumber FROM Orders)
WHERE Orders.OrderID = Forms!frmPrintInvoice.OrderID

 

[CJ_London]

That code probably needs adjusting to not create a new number if the field is already populated - on a reprint for example perhaps because the original can’t be found or a printer failure

 

[ryetee]

It's easier to offer example SQL when the actual table structure is available.

In this case, we assume that there is an order table, plus an order detail table if an order contains one or more line items.

This does sound standard: "The invoice number is related to the order. I need a unique invoice number per order."

If the invoice number is a field in that table, this works out in a straightforward manner. Unfortunately, the description so far doesn't make that clear. As others have questioned, it sounds like you are waiting to create that invoice number until the invoice is printed, rather than when the order is created. That's also reasonable, as an order might be generated on Monday and not invoiced until Friday, for example.

The key point, though, is that once the invoice number is created, it needs to be stored in the invoice field in the order table, not in a separate table. There is no point to having a separate "control" table because the appropriate way to store each invoice number is in the order table as part of the order.

SELECT Max(InvoiceNumber) +1 As NewInvoiceNumber FROM Orders

will generate the next invoice number in sequence when you need to update the order with that invoice number at the time it the invoice is printed.

Perhaps you should combine the two processes.

UPDATE Orders
SET InvoiceNumber = (SELECT Max(InvoiceNumber) +1 As NewInvoiceNumber FROM Orders)
WHERE Orders.OrderID = Forms!frmPrintInvoice.OrderID

I've not looked at a piece of code for over 6 years so I didn't remember , think or even realise that Max(invoice number) was a thing but that seems a great idea. I do have a complication for that as the invoice number is effectively RRnnnn where RR is the region code and nnnn is a unique 4 digit number. I know this can easily be overcome by having 2 fields and concatenating for the report. The other complication I have which is why I'm thinking I need to store the last invoice number on another table is that if the order is deleted after an invoice has been produced then the number HAS to be reused. I thought having a separate table that stored the last used and also "spares" would be easier. I'm still open to anyone saying it's a lot easier if you...

 

[ryetee]

That code probably needs adjusting to not create a new number if the field is already populated - on a reprint for example perhaps because the original can’t be found or a printer failure

Yes I have that covered.
1. has the order an invoice number
2. no then create
3 yes then use that

 

[ryetee]

I've managed to use record sets to get this working but indications suggest I shouldn't be using another table to store latest invoice number. I'm open to suggestions to what should be in it's place. I like GPGeorge's suggestion but I've posted another issue that it may not cover.

 

[CJ_London]

if the order is deleted after an invoice has been produced then the number HAS to be reused.

What is the rationale for requiring the invoice numbers are contiguous so you have to fill in the blanks?

And why delete an order once it has been invoiced? It is presumably then in your financial system and would be ‘cancelled’ with a credit note. Or you would have a deleted flag of some sort? The setting of which could then generate the credit note

 

[GPGeorge]

I've not looked at a piece of code for over 6 years so I didn't remember , think or even realise that Max(invoice number) was a thing but that seems a great idea. I do have a complication for that as the invoice number is effectively RRnnnn where RR is the region code and nnnn is a unique 4 digit number. I know this can easily be overcome by having 2 fields and concatenating for the report. The other complication I have which is why I'm thinking I need to store the last invoice number on another table is that if the order is deleted after an invoice has been produced then the number HAS to be reused. I thought having a separate table that stored the last used and also "spares" would be easier. I'm still open to anyone saying it's a lot easier if you...

I doubt that an independent auditor would sign off on the idea that an order could be deleted after an invoice for that order was produced. That is an open invitation to fraud.

I once worked on such a system as a matter of fact. One of the main reasons the company I worked for at that time was brought in to upgrade their Access database was precisely what you describe, deleting transactions after an invoice was created. One person was caught creating invoices that were deleted after a check was written, and pocketing the money that should have been paid out. He was fired and the organization spent over $10,000 having us upgrade their system to prevent a recurrence.

An invoice, if cancelled, isn't lost. It remains in your system as a record of what really happened. I get that you can't have gaps in invoice numbers. However, allowing invoices to disappear is also a big problem.

 

[Gasman]

The report is the invoice. The invoice number is related to the order. I need a unique invoice number per order. Once the invoice is created I need to use the next number in sequence and then update that number for the next one to be created,

So the invoice number goes with the order.
As already mentioned if you print a repeat you get a new invoice number.

When you create the order, then you calculate the invoice number, or for what would be for most people the order number?

 

[ryetee]

So the invoice number goes with the order.
As already mentioned if you print a repeat you get a new invoice number.

When you create the order, then you calculate the invoice number, or for what would be for most people the order number?

No if you reprint you pick up the old number.

 

[CJ_London]

Just one further twist which may or may not be relevant. If an order comes in for 20 of product X and 10 of product Y. Product X is in stock and is despatched and invoiced immediately, product Y is despatched and invoiced later when back in stock.

In this case you have two invoices for one order. So the invoice numbers cannot be assigned to the order record - it needs to be assigned to the order line record

But then you have an order come in for 30 of product X, but can only despatch and invoice 10, the balance to follow when back in stock

So now you need separate invoice tables - invoice header and invoice lines - and these will contain the invoice number and a link back to the order header/line tables

Finally you may have to handle an invoice for multiple orders….

 

[ryetee]

I doubt that an independent auditor would sign off on the idea that an order could be deleted after an invoice for that order was produced. That is an open invitation to fraud.

I once worked on such a system as a matter of fact. One of the main reasons the company I worked for at that time was brought in to upgrade their Access database was precisely what you describe, deleting transactions after an invoice was created. One person was caught creating invoices that were deleted after a check was written, and pocketing the money that should have been paid out. He was fired and the organization spent over $10,000 having us upgrade their system to prevent a recurrence.

An invoice, if cancelled, isn't lost. It remains in your system as a record of what really happened. I get that you can't have gaps in invoice numbers. However, allowing invoices to disappear is also a big problem.

yeah i get that. i'm working for a man band living on a small group of islands. he's not going to steal from himself. the tax man needs to see invoice numbers with no breaks

 

[ryetee]

What is the rationale for requiring the invoice numbers are contiguous so you have to fill in the blanks?

And why delete an order once it has been invoiced? It is presumably then in your financial system and would be ‘cancelled’ with a credit note. Or you would have a deleted flag of some sort? The setting of which could then generate the credit note

rational is tax office wants it
why delete. you print off the invoice and the customer says you have my name wrong i want another report.. currently the system doesn't allow editing once the invoice has been produced. so before (when there was no real need for invoice numbers) the order was deleted and re-entered correcting the data. I'm looking to change this but the code is old and not straight forward so i need to investigate more. if not possible i want to be able to reuse the invoice number.

 

[ryetee]

Just one further twist which may or may not be relevant. If an order comes in for 20 of product X and 10 of product Y. Product X is in stock and is despatched and invoiced immediately, product Y is despatched and invoiced later when back in stock.

In this case you have two invoices for one order. So the invoice numbers cannot be assigned to the order record - it needs to be assigned to the order line record

But then you have an order come in for 30 of product X, but can only despatch and invoice 10, the balance to follow when back in stock

So now you need separate invoice tables - invoice header and invoice lines - and these will contain the invoice number and a link back to the order header/line tables

Finally you may have to handle an invoice for multiple orders….

It's not really like a true ordering system. The code originally (not written by me) was designed to mimic life. It's a 1 man band business.
He goes to a shop and leaves 30 x, 50 y, 23 z and many more
When he comes back there are 23 x, 10 y and 1z. So he produces a delivery not and ultimately an invoice for 7x, 40y and 22z. He then tops up what's been sold to the original 30 x, 50 y, 23 z