Sequential numbering for each prefix?

[gemma-the-husky]

well you need to think about the data structure I think, and therefore how the prefix/item number relates to the item.

if an item consists of a number of sub-structures, and each substructure has a separate prefix - then the item itself cannot have a single prefix against which you can establish a next DCN number. Maybe all you can have is a single sequential DCN.

If all the sub-structures have the same prefix - then simply hold the prefix in the ToItem table, as storing it within the substructure is duplicating data.

etc.

 

[Sam Summers]

For each item there is only one sub-section with one prefix.

Each sub-section has its own prefix.

What (ideally) I am trying to do is to number each prefix sequentially as opposed to a series of numbers with prefixes attached?

Example:

CR001; CR002; CR003
BD001; BD002 and so on rather than

CR001; BD002; CR003; OA004; TDS005; CR006

I think I am close but just trying to crack it?

I think the final scenario in this is close?

https://scottgem.wordpress.com/2009/11/25/sequential-numbering/

 

[Sam Summers]

How about this idea?

If I create a lookup table for each prefix then this would create a sequence of numbers for each prefix?

I'm experimenting with this now.

 

[Sam Summers]

Or maybe this could work if I get it right?

Its not quite working yet and i'm trying to get to grips with the code?

Private Sub SubSection_AfterUpdate()

Dim strZ As String
Dim intCurr As Integer
Dim varSQL As Variant
Dim varFullNum As Variant
Dim strSQL As Variant

varSQL = "SELECT TOItem.ItemID, Max(TOItem.Sequence)"
varSQL = strSQL & " AS Sequence"
varSQL = strSQL & " FROM TOItem"
strSQL = strSQL & " GROUP BY TOItem.ItemID;"

Me.RptNo = DMax(Nz(Sequence.TOItem), 0) + 1
Me.Prefix = Me.SubSection.Column(2)

DoCmd.RunSQL varSQL


strZ = Me.Prefix
intCurr = strSQL + 1

varFullNum = strZ & intCurr


Me.Item = UCase([Prefix] & "" & [TxtNumber])

End Sub