Accessing an unbiund table (1 Viewer)

[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.