Finding next sequential number

[Samantha]

I'm building a MsAccess "annex" interface to our existing MRP system which is
a SQL database , to automatically add another Item Number given certain
conditions. My problem is how I can set the next sequential number of the
Item Number.
The starting Item Number format is 01-00800, where the last 3 digits (800)
is sequential. The highest sequential can only go up to 999 (i.e 01-00999),
at which point the script stops and someone will have to decide what the new
sequence should be and reset.
The list of Item Numbers created are stored in table PartMaster, field
ItemNum. I'm thinking of storing the prefix "01-00" and the sequential
number "800" in a table "Settings". The biggest challenge for me is that a
new Item Number can be created via the MRP interface also. So, storing the
the next sequenctial number is not sufficient. But I'm not sure what else is
need.
Any help is very much appreciated.

 

[Graham Mandeno]

Hi Samantha

A method that generally works is:

NextNumber = Nz( DMax( <sequential number field>, <table name>, _
<where condition to select group> ), 0 ) + 1

This way, you don't need to store the next number anywhere - just look up
the last number and add one.

I don't know whether this will play nicely with your MRP system, but I would
suggest you store the prefix and sequence number in separate fields in your
table. That way it's easy to look up the last number, filtering on a given
prefix:

DMax( "SeqNum", "YourTable", "Prefix='" & strPrefix & "'" )

Alternatively, if the item number must be a single text field, you can do
this:

DMax( "ItemNumber", "YourTable", "Left(ItemNumber, 5)='" & strPrefix &
"'" )

Then, with the resulting string:
NextNumber = CInt( Right (LastItemNum, 3) ) + 1

However, given that this is a SQL Server backend, the best solution would be
to write (or have someone else write) a stored procedure to return he next
available number for a given prefix.

 

[Samantha]

Graham, thanks for your reply. I think I understand your advice. The problem
is that because people can generate the new Item Number via the MRP's GUI
also, someone might create a new Item Number that is out of sequence. For
example, if the last number created by the were 01-00850. Someone manually
created the next number 01-00890. We would like a way to find the "un-used"
Item Numbers as well. Can this be done?
Thanks again.

Hi Samantha

A method that generally works is:

NextNumber = Nz( DMax( <sequential number field>, <table name>, _
<where condition to select group> ), 0 ) + 1

This way, you don't need to store the next number anywhere - just look up
the last number and add one.

I don't know whether this will play nicely with your MRP system, but I would
suggest you store the prefix and sequence number in separate fields in your
table. That way it's easy to look up the last number, filtering on a given
prefix:

DMax( "SeqNum", "YourTable", "Prefix='" & strPrefix & "'" )

Alternatively, if the item number must be a single text field, you can do
this:

DMax( "ItemNumber", "YourTable", "Left(ItemNumber, 5)='" & strPrefix &
"'" )

Then, with the resulting string:
NextNumber = CInt( Right (LastItemNum, 3) ) + 1

However, given that this is a SQL Server backend, the best solution would be
to write (or have someone else write) a stored procedure to return he next
available number for a given prefix.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm building a MsAccess "annex" interface to our existing MRP system which
is
a SQL database , to automatically add another Item Number given certain
conditions. My problem is how I can set the next sequential number of the
Item Number.
The starting Item Number format is 01-00800, where the last 3 digits (800)
is sequential. The highest sequential can only go up to 999 (i.e
01-00999),
at which point the script stops and someone will have to decide what the
new
sequence should be and reset.
The list of Item Numbers created are stored in table PartMaster, field
ItemNum. I'm thinking of storing the prefix "01-00" and the sequential
number "800" in a table "Settings". The biggest challenge for me is that
a
new Item Number can be created via the MRP interface also. So, storing the
the next sequenctial number is not sufficient. But I'm not sure what else
is
need.
Any help is very much appreciated.