Pulling Sequence Number Depending on Other Fields

[Debra Ann]

MS Access 2003 question:

I have a numbering system on a form made up of five fields in a table. The
user picks from combo boxes the first four fields and then he will click on a
command button that will need to create the sequence number in the fifth
field.

The number filled in needs to be the next consecutive number in the table
depending on the uniqueness of the first four-field combination.

If the four-field combination does not exist in the table yet, the first
number given is 50001.

If it does exist, then it needs to take the last sequence number of that
four-field combination and add "1" to it.

For example, the table has the following numbers already:

AAA-BBB-CCC-DDD-50001
NNN-OOO-PPP-QQQ-50001
AAA-BBB-CCC-DDD-50002
AAA-BBB-CCC-DDD-50003
NNN-OOO-PPP-QQQ-50002

If I was to create a new record with the combination of AAA-BBB-CCC-DDD,
then the sequence number would need to be 50004.

However, if I create a new record with a new combination (FFF-GGG-HHH-III,
then the sequence number would need to be 50001 again.

I have no earthly idea how to query the table behind the scenes of a form to
find out what number to assign to the sequence field.

Guidance would be greatly appreciated.

Thank you,

Debra Ann

 

[Dorian]

Here is one way to do it:

Set up a separate table with 2 columns Prefix and Suffix.
The Prefix is your 5 fields, The Suffix is the last assigned number.
To create your numbers you first do a DLookup for a match on Prefix, if you
dont find it, you add a new row for the number and set the suffix to 50001.
If you do find it, you do a second DLookup to locate the latest suffix, you
then increment it by 1 and add a new row with the incremented number.
In this way you keep a note of the last sequence number used for each prefix.

Yes, it's a lot of work but you have burdened yourself with this due to the
numbering system.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Debra Ann]

I will try this and see if I can get it to work. And I agree with the burden
but I was given the task due to the company only being given the sequence
list of 50001 to 99999 due to other compnies using numbers before and after
it. Unfortunately, we will be creating way more documents than the numbering
allows so it requires duplication of sequence number.

 

[Debra Ann]

Ok,

I created a table with a prefix and suffix column and create the code just
to do the lookup first and I get an error stating "You cancelled the previous
operation" and am not sure what I am doing wrong. It highlights the last
line (strLookup =). Here is my code:

Dim strPrefix As String
Dim strLookup As Variant

strPrefix = strProject + "-" + strADoc + "-" + strBDoc + "-" + strLocation
strLookup = DLookup("[Suffix]", "tbl_TrackNumber", "[Prefix] = " &
strPrefix)

In the table, the suffix is a Long Integer. I do not know if that has
anything to do with it but thought I would let you know.

 

[Debra Ann]

Nevermind. I had to add apostraphes around my variable:

strLookup = DLookup("[Suffix]", "tbl_TrackNumber", "[Prefix] = '" &
strPrefix & "'")

 

[GB]

I don't see the need for a second table, if you already are
tracking/recording the complete "serial" number.

If you have your existing table that has 5 fields, then when you click on
the command button, you can do a query on your dataset/datatable using the
first four fields that have been populated, that would be sorted in
descending order based on the number field to be generated.

If the search is unsuccessful, then the number is 50001. If the search is
successful, then use the first record returned and increment by 1. If the
new value is >99999 then it is 50001 (or whatever new numbering system is to
be used to track the fact that you have 50000 items that have the same
prefix, and that a duplicate will occur by creating this new next item. :\

Be sure that in the data manipulation that you use a clone of the data, and
don't move the bookmark of the form data. All you are trying to do is lookup
the information not modify anything other than what is on the screen.

 

[GB]

Something learnt from John W. Vinson in a recent post, you could use
something like the following to pull the currently largest number that
matches your
NewNumber = NZ(DMax("[field with sequence number]", "tablename", "[field1] =
'" & TextBoxForField1 & "' AND [field2] = '" & TextBoxForField2 &
"'[etc...]"), 5000) +1

This will find the maximum value of the number field in the table that
matches your four other fields, if it is not found then the DMax function
returns Null and the NZ function will evaluate to provide the value of 5000.
The value returned by NZ will then be incremented by 1 to give the next
number. (either say 5022 or if a new series of the first four values the
value of 5001)