incrementing key field

[jim]

My client does not want invoice number larger than 4 positions and it's
nearing "9999" so I wrote this routine (the 8568 represents the first number
in the file):
If NewRecord Then
If DMax("InvoiceID", "tblInvoicesBrad") = "9999" Then
Me.InvoiceID = 101
Else
Me.InvoiceID = DMax("InvoiceID", "tblInvoicesBrad", "InvoiceID"
< "8568") + 1
End If
end if

After looking at it though I realize DMax will always be "9999" and take the
first if and make Me.InvoiceID = 101. What is the best way to write routine
so that it will add 1 to the largest number under 8568 after the one time
"9999" to "101"?
TIA

 

[Dirk Goldgar]

My client does not want invoice number larger than 4 positions and it's
nearing "9999" so I wrote this routine (the 8568 represents the first
number
in the file):
If NewRecord Then
If DMax("InvoiceID", "tblInvoicesBrad") = "9999" Then
Me.InvoiceID = 101
Else
Me.InvoiceID = DMax("InvoiceID", "tblInvoicesBrad", "InvoiceID"
< "8568") + 1
End If
end if

After looking at it though I realize DMax will always be "9999" and take
the
first if and make Me.InvoiceID = 101. What is the best way to write
routine
so that it will add 1 to the largest number under 8568 after the one time
"9999" to "101"?

What is the data type of your InvoiceID field? Your code treats it both as
text and numeric. I'm going to assume for this code revision that it's
numeric:

'------ start of untested "air code" ------
Dim lngMaxID As Long

lngMaxID = Nz(DMax("InvoiceID", "tblInvoicesBrad"), 100)

If lngMaxID >= 9999 Then
lngMaxID = _
Nz(DMax("InvoiceID", "tblInvoicesBrad", "InvoiceID < 8568"),
100)
End If

Me.InvoiceID = lngMaxID + 1
'------ end of code ------

 

[jim]

Thanks Dirk, works perfect. Sorry about the post -the field was a long
integer.

 

[Tony Toews [MVP]]

My client does not want invoice number larger than 4 positions and it's
nearing "9999"

Of course his accountant is going to be a little annoyed.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/