update query to increment invoice number by one

[dpimental]

All, I am trying to use an update query to add invoice numbers.

For example, if the last invoice number was 20001 and there are 5 records to update, the invoice numbers should be as following.

record 1 20006
record 2 20007
record 3 20008
record 4 20009
record 5 20010

The problem is that for each reocrd to be updated, I need to get the max invoice number and increase it by one.

I have a function that I am calling; but it seems to just increment the 1st record's invoice number and use that number for all other records that are updated.

David Pimental
(US, Oh)

 

[Golom]

First, are you trying to update all five (in your example) records in a single UPDATE statement or you doing them one at a time using five separate update statements?

Second, are you updating existing records or are you inserting new records?

Third, can you post your code and/or SQL?

 

[dpimental]

I am trying to update all five with the same update statement.

I am setting the field name = the function name.

These are existing records that I am updating.

My function is below.

Function AssignInvoiceNumber() As Long
Dim results As Long
results = DMax("[INVOICE_NUMBER]", "ORDERS")
results = results + 1
AssignInvoiceNumber = results
End Function

The update query does the following

UPDATE ORDERS SET ORDERS.INVOICE_NUMBER = AssignInvoiceNumber()
WHERE (((ORDERS.ORDER_ID) In (910591,52422,41125,55129,905951,52562,54022,52082)));

David Pimental
(US, Oh)

 

[Golom]

The SQL optimizer assumes that, because AssignInvoiceNumber has no arguments, it does not depend on the records being processed. It therefore optimizes the call so that it is evaluated only once and that value is returned every time it appears.

Even if you gave it an argument forcing evaluation on every call it still won't work because the records are not updated in the database until the UPDATE statement completes. You are therefore picking up the same DMAX value on every call to AssignInvoiceNumber.

You may need to do this with seperate UPDATE statements for each ORDER_ID in your IN clause.

 

[dpimental]

Golom, I passed in the OrderID and it worked.

I put some break points in the function and it is cycling through it each time.

I will do more testing to confirm, though.

Thanks for the insight.

David Pimental
(US, Oh)

 

[Golom]

I would be interested to hear if it still works without the breakpoints.

The reason being that a breakpoint can cause an implicit equivalent of a DoEvents that could cause your database to be updated and thus produce the correct result. Without the breakpoint you may not get the updated record available from one record in time for the next pass through your function to detect it.

 

[dpimental]

Actually, it still works without break points.



David Pimental
(US, Oh)