increase form number by 1 for each order

[jrobin5881]

I have a invoice app that I created in Access a long time ago. It creates a work ticket that is given to the mechanic to take out the job. The tickets are pre-numbered and the FORM in Access was originally developed to mirror the form number incrementally by 1. For example the next hardcopy form number is 19979 in Access in the Invoice Number field I would expect the default value for the Invoice Number to keep pace with the hard copy and it has been working well for years. Now for some reason it stopped. As stated before the next number is 19979 and Access has it as 318513 In the InvoiceID field.

The properties for this field are as follows:
Control Source: Customer.InvoiceID Default Value: =GetNextInvoice()

In VBA I have the following code
Public Function GetNextInvoice()
'get next free Invoice Number
GetNextInvoice = Nz(DMax("[InvoiceID]", "[Customer]") + 1, 1)
End Function

I looked back into the table and the last number in the InvoiceID field is 19978 (the girls are typing each one in instead of accepting the default value) so I would expect the default value to increase by one as it's written but it's not. Like I said it has the crazy number that it's selecting on its own and I can't correlate to any other field in the tables

Any help here????

 

[MrMerlotti]

It all works for me and returns the next number in sequence: ie 19979

I know this obvious but as users are allowed to over-write the field and as there are presumably now 19978 records has someone has over-written an early InvoiceID with 318512? The DMax function would then return 318513.

 

[ProgramError]

Disable and lock the textbox showing the InvoiceID field!!!
Dont let them alter it. put the code

= Nz(DMax("[InvoiceID]", "[Customer]") + 1, 1)

in the default value of the textbox. the control source should be the InvoiceID field).




Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[jrobin5881]

ProgramError - so your saying to move the code to the invoice field instead of having the call to VBA there and that should fix it?

MrMerlotti- I was thinking the same as you that the girls somehow changed the sequence but the table has all of the numbers matching the hardcopy invoices due to them having to overwrite the default value each time.

 

[ProgramError]

Yes!

the invoiceID in the control source property of the textbox and put
=DMax("InvoiceID","Customer")+1
in the default property.



Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[JoeAtWork]

This may be obvious but are you absolutely sure the number 318512 is not in your table? How did you look for it? If you simply scrolled to the bottom of the table you wouldn't necessarily see it there - unless you sorted by InvoiceID column first.

What result do you get if you run

?DMax("[InvoiceID]", "[Customer]")

in the Immediate window?

 

[jrobin5881]

Joe,

My problem is I can't get to the program till later this afternoon to test everyone's suggestions. So I'll give it a shot then and repost to let you all know what happens.

 

[jrobin5881]

Okay - I took everybodys advice I ran the code in the immediate window and got the same number so I did a sort and lo and behold a set of numbers came back that were higher and the dmax function was actually doing its job correctly. I locked down the text box and put the suggested code in the default value and all is fine.