Creating a Sequential Number

[BDW Jr]

Hello,

I would like to Access to generate a sequential number based on a current table and fields on a form. I’ve been thinking about it for days and I’m stuck. Any and all help is appreciated. The table contains the following fields: Inventory Number, Prefix1, Seq and ClassCode.

InventoryNum: 1004-1119-1503
Prefix1: 1004
Seq: 1119
ClassCode: 1503

On my form I would require the person to enter the Prefix1 and Class Code fields. I’m looking to have the code look at the inventory numbers in the table, create the next available number and save it. Example using the table above: when the code is ran by a command button it would generate inventory number 1004-1120-1503. I’d also like the option of being able to create more than one inventory number (multiple numbers) all at the same time like 1004-1120-1503 through 1004-1122-1503. Is this possible?

Thanks
BDW Jr.

 

[Solo712]

Hello,

I would like to Access to generate a sequential number based on a current table and fields on a form. I’ve been thinking about it for days and I’m stuck. Any and all help is appreciated. The table contains the following fields: Inventory Number, Prefix1, Seq and ClassCode.

InventoryNum: 1004-1119-1503
Prefix1: 1004
Seq: 1119
ClassCode: 1503

On my form I would require the person to enter the Prefix1 and Class Code fields. I’m looking to have the code look at the inventory numbers in the table, create the next available number and save it. Example using the table above: when the code is ran by a command button it would generate inventory number 1004-1120-1503. I’d also like the option of being able to create more than one inventory number (multiple numbers) all at the same time like 1004-1120-1503 through 1004-1122-1503. Is this possible?

Thanks
BDW Jr.

Not sure why you need to store parts of the Inventory number in separate fields.

Best,
Jiri

 

[Minty]

Inventory number appears to be a composite of the others - I wouldn't store it, as you need to keep it accurate to the others.

Like Jiri - this looks a bit weird - can you explain the overall purpose, without database speak getting in the way?

 

[arnelgp]

to get the next Seq for a particular Prefix and ClassCode:

Nz(Dmax("Seq", "yourInvTableName", ""Prefix=" & Forms! yourForm! PrefixTextbox & "ClassCode=" & Forms! yourForm! ClassCodeTextbox), 0) + 1.

 

[The_Doc_Man]

BDW - with Access, nearly anything is possible, but the far more important question is this: Is it necessary?

First question: Can records ever be deleted from this table?
First sub-question: If so, are you going to have to renumber everything above the deletion?
Second sub-question: What would happen if the number weren't sequential? Would the world end?

The problem we have seen over too many years and in too many databases is that there is a mind-set that you need scrupulously sequential numbering when what you really needed was just some type of unique key.

So before we get too deep in the swamp, what is the business rule that requires attention to sequential numbering? Are you trying to use the number as a count of some kind? If so, Access can correctly count records that don't have sequential numbers.

Further, if the composite number is actually built from three other fields, then you should just power this "thing" (whatever it is) with a QUERY that includes a concatenated string built from the three components formatted as strings plus a couple of constant dashes.

As to synthesizing the sequence number, it would be a simple matter to do something like

strCriteria = "[Prefix1] = " & Me.Prefix1 & " AND [ClassCode] = " & Me.ClassCode
If DCount( "*", "mytablename", strCriteria ) = 0 THEN
    NewSeq = 1
ELSE
    NewSeq = 1 + DMax( "[Seq]", "mytablename", strCriteria )
END IF

As to whether you could generate more than one number this way as a single operation, sure. Write a loop and do the allocations one at a time, since you are storing them and THAT would probably need to be done one at a time, too.

 

[BDW Jr]

To everyone that provided answers, THANK YOU.

The Seq and ClassCode fields aren't needed in my opinion. It was something that was transferred over from the paper format. I was asked to automate the process using Access. So if a code can be written using the InventoryNum and Prefix1 fields, that's fine with me.

Answering The Doc Man:
First question: Can records ever be deleted from this table?
No. Once the number is created it is permanent to be used on the piece of equipment.

First sub-question: If so, are you going to have to renumber everything above the deletion?
No renumbering needed because numbers will not be deleted.

Second sub-question: What would happen if the number weren't sequential? Would the world end?
Our business rule is to use all inventory numbers from 1000-0001 to 1000-9999. The numbers need to be sequential for accounting purposes. The world wouldn't end, it would just make tracking equipment a nightmare. The inventory numbers are based on previous numbers and need to follow suit.

I will try the codes arnelgp and The Doc Man provided. Very thankful for your help. Hopefully I'll be able to piece it together.

Once again Thank You.