Part Number Generation

[rnash]

I would like to know how to generate a table of unique part numbers in access using the following template:

ABC-DE

where possible entries for each letter are:

A=1,2,3,4,5
B=CD,ER,FF,FE
C=A,B
D=43,23,12
E=1,11,111,1111

A sample part number would be 1CDA-431. What is the best way of generating all possible part numbers in access?

 

[Uncle Gizmo]

I reckon there are 480 permutations, with such a low number it hardly seems worth going to the trouble of writing code to build the numbers in MS Access. I think I would be more inclined to build up the numbers manually in a spreadsheet.

 

[jdraw]

I would like to know how to generate a table of unique part numbers in access using the following template:

ABC-DE

where possible entries for each letter are:

A=1,2,3,4,5
B=CD,ER,FF,FE
C=A,B
D=43,23,12
E=1,11,111,1111

A sample part number would be 1CDA-431. What is the best way of generating all possible part numbers in access?

Here's one way. You'll have to put the Gen_no() values somewhere.
The

'---------------------------------------------------------------------------------------
' Procedure : GenPartNums
' Author    : Jack
' Created   : 1/13/2010
' Purpose   : To generate part numbers _ Acess World Forums
'how to generate a table of unique part numbers in access
'using the following template: ABC-DE where
'A=1,2,3,4,5
'B=CD,ER,FF,FE
'C=A,B
'D=43,23,12
'E=1,11,111,1111
'
'A sample part number would be 1CDA-431.
'What is the best way of generating all possible part numbers in access?
'
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub GenPartNums()
'A=1,2,3,4,5
'B=CD,ER,FF,FE
'C=A,B
'D=43,23,12
'E=1,11,111,1111

Dim A(4) As String
Dim B(3) As String
Dim C(2) As String
Dim D(2) As String
Dim E(3) As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim n As Integer
Dim Gen_No(479) As String
Dim idx As Integer
   On Error GoTo GenPartNums_Error

idx = 0
A(0) = "1"
A(1) = "2"
A(2) = "3"
A(3) = "4"
A(4) = "5"
B(0) = "CD"
B(1) = "ER"
B(2) = "FF"
B(3) = "FE"
C(0) = "A"
C(1) = "B"
D(0) = "43"
D(1) = "23"
D(2) = "12"
E(0) = "1"
E(1) = "11"
E(2) = "111"
E(3) = "1111"

For i = 0 To 4
 For j = 0 To 3
  For k = 0 To 1
    For m = 0 To 2
      For n = 0 To 3
'
'****  You could adjust this next line to write to a table
'****
      Gen_No(idx) = A(i) & B(j) & C(k) & "-" & D(m) & E(n)
      idx = idx + 1
      Next n
    Next m
  Next k
 Next j
Next i
Debug.Print  ' I put a breakpoint here to ensure the array was populated ####

   On Error GoTo 0
   Exit Sub

GenPartNums_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure GenPartNums of Module Module4"
End Sub

 

[Galaxiom]

I reckon there are 480 permutations, with such a low number it hardly seems worth going to the trouble of writing code to build the numbers in MS Access. I think I would be more inclined to build up the numbers manually in a spreadsheet.

Solution1: Build numbers in spreadsheet.
Effort: Overcoming boredom.
Potential problems: Typographical errors and omissions
Learn: Nothing.
Future purpose: Nil

Solution2: Write code
Effort: meeting challenge
Potential Problems: Might need help
Learn: Wider knowledge of coding
Future purpose: Potential for adaption to other similar tasks.

I know which I would choose.
Gizmo you are showing your age.

 

[rnash]

I follow the code just fine as I have some experience with VB but I am new to access. I am having trouble getting the code to execute properly. I copied the code into a new module and clicked run. You said that I needed to put the Gen_no() values somewhere. Is this the same thing you are referring to in your comment "You could adjust this next line to write to a table"? When I click run, nothing happens. I'm assuming that the code is being executed properly (because I don't get an error) but I just haven't told it where to go. How could I populate one column of a table with the part numbers?

Yes I will be using this as a template for a larger part numbering system.

I greatly appreciate your help and I apologize for my 'rookieness'.

Thanks,
Jarrett

 

[Guus2005]

You can let Access do the work and create a cartesian product, thus creating all possible permutations

Create Table A and a field a containing records 1,2,3,4,5
Create Table B and a field b containing records CD,ER,FF,FE
Create Table C and a field c containing records A,B
Create Table D and a field d containing records 43,23,12
Create Table E and a field e containing records 1,11,111,1111

Now create a query

select A.a & B.b & C.c & "-" & D.d & E.e as Result from A,B,C,D,E

Enjoy!

 

[rnash]

Thanks! that worked great. Such a simple solution.

 

[Uncle Gizmo]

Gizmo you are showing your age.

I think you’re right, the only solution I could think of was writing some ADO code one loop within another loop within another loop .... .... ... I could just see how it was going to be a headache.

As for age yes, when I was younger I would have jumped at the challenge. But now I sort of think “why”?

 

[Rabbie]

As for age yes, when I was younger I would have jumped at the challenge. But now I sort of think “why”?

I know the feeling