"autonumber" based on subset of "autonumber" field

[SteveP.]

I am working on a product returns DB that manages any product that is
returned to our facility. Any time a customer returns product to us, they
have to get a Debit Memo Number. When this DB was originally implemented in
our facility, these numbers started in the 6000 range. Every once in a while,
product is returned to us without one of these Debit Memo Numbers. In the
past when this has occurred, starting with 0, we would gradually increment
these numbers adding 1 each time (obviously). In 2 years, we are up to 34, so
it will take a long time to reach 6000. As I have had to open up the table to
determine what this number should be, what I would like to have happen is,
when an associate receives one of these units without a Debit Memo Number,
they press a button on the form and it automatically locates the highest
number (as of right now, it would be 34) and adds 1 to it. On other forms, I
have used both a "=DMax..." and extensive code that runs a query to add one
to the highest number in the table. Both of these approaches locate the
highest number in the table. Trying either of these approaches with a query
that lists numbers <6000 gives me a "circular reference" error.
Field name: DebitMemoNumber
Table: tblReturn2
Any help is appreciated. Thank you

 

[Daryl S]

Steve -

The DMax should work if in your criteria section you indicate "[keyfield] <
6000".

You might want a public function that is simple, something like this:

Public Function NextLowDebitMemoNum() As Integer
' Returns next available DebigMemoNumber under 6000
Dim intMaxID As Integer
intMaxID = DMax("[DebitMemoNumber]", "[tblReturn2]", "[DebitMemoNumber] <
6000")

NextLowDebitMemoNum = intMaxID + 1

End Function

You can then call this in code, in a query, or wherever you need.

 

[Stefan Hoffmann]

hi Daryl, Steve,

Public Function NextLowDebitMemoNum() As Integer

No Way! Don't use an Integer. It's maximum number is 32768. Use a Long
instead.

' Returns next available DebigMemoNumber under 6000
Dim intMaxID As Integer
intMaxID = DMax("[DebitMemoNumber]", "[tblReturn2]", "[DebitMemoNumber]<
6000")

NextLowDebitMemoNum = intMaxID + 1

This assumes that you have numbers. It may throw an error otherwise.
Test for Null:

Public Function NextLowDebitMemoNum() As Long

NextLowDebitMemoNum = Nz( _
DMax( _
"[DebitMemoNumber]", _
"[tblReturn2]", _
"[DebitMemoNumber] < 6000"), _
0) + 1

End Function


mfG
--> stefan <--

 

[Daryl S]

Steve, Stefan -

Yes, Long will work, but remember the highest this can be is 6000, so
Integer would be fine. The table already has records up to 34, so you won't
get a null response unless records are deleted from the table. You may want
to test for the 6000 being returned, but the assumption was that the numbers
would never get that high.

--
Daryl S

hi Daryl, Steve,

Public Function NextLowDebitMemoNum() As Integer

No Way! Don't use an Integer. It's maximum number is 32768. Use a Long
instead.

' Returns next available DebigMemoNumber under 6000
Dim intMaxID As Integer
intMaxID = DMax("[DebitMemoNumber]", "[tblReturn2]", "[DebitMemoNumber]<
6000")

NextLowDebitMemoNum = intMaxID + 1

This assumes that you have numbers. It may throw an error otherwise.
Test for Null:

Public Function NextLowDebitMemoNum() As Long

NextLowDebitMemoNum = Nz( _
DMax( _
"[DebitMemoNumber]", _
"[tblReturn2]", _
"[DebitMemoNumber] < 6000"), _
0) + 1

End Function


mfG
--> stefan <--
.

 

[Stefan Hoffmann]

hi Daryl,

Yes, Long will work, but remember the highest this can be is 6000, so
Integer would be fine. The table already has records up to 34, so you won't
get a null response unless records are deleted from the table. You may want
to test for the 6000 being returned, but the assumption was that the numbers
would never get that high.

While this is true in this special case, it is an unnecessary
restriction which may lead at least to confusion. But I have seen too
many databases failing during work because people used Integers for
working with numbers, especially when handling AutoNumbers in code.

Thus always use Long, its cheap to avoid that kind of error.


mfG
--> stefan <--

 

[Daryl S]

Stefan -

Thanks - you are right - it is cheap insurance. Hope it works for Steve.