Generating a Part Number

[rousseaud]

I’m trying to generate a part number of sort. It must be a total of 11 characters and have two “sections” separated by the letter ‘P’. The first section is a number I select from a combo box and can consist of 1 to 6 characters in length. The second section must be a unique incremental number starting from 1 that will have leading zeros to make up the total 11 character part number. It would look something like this: 0000P000000. Any help?

[Denburt]

I’m trying to generate a part number of sort. It must be a total of 11 characters and have two “sections” separated by the letter ‘P’. The first section is a number I select from a combo box and can consist of 1 to 6 characters in length. The second section must be a unique incremental number starting from 1 that will have leading zeros to make up the total 11 character part number. It would look something like this: 0000P000000. Any help?

ok is your combo box set up and ready to go?

What are your field names?

Output:
Me!MytextOut

Combo Box:
Me!MyBox.column(0)

My AutoNumber:
Me!MyAutoNumb

Me!MytextOut = Me!MyBox.column(0) & "P" & Me!MyAutoNumb


There are a number of ways to auoto generate an id number and I will try to get back to this soon.

[rousseaud]

ok is your combo box set up and ready to go?

What are your field names?

Output:
Me!MytextOut

Combo Box:
Me!MyBox.column(0)

My AutoNumber:
Me!MyAutoNumb

Me!MytextOut = Me!MyBox.column(0) & "P" & Me!MyAutoNumb


There are a number of ways to auoto generate an id number and I will try to get back to this soon.

Thanks for the reply. I do have a combo box setup. There is one field beside the ID, called Product. The field in another table that I want to store the Part Number is called PartNumber. I would appreciate any help you could provide. Thanks

[Denburt]

Thanks for the reply. I do have a combo box setup. There is one field beside the ID, called Product. The field in another table that I want to store the Part Number is called PartNumber. I would appreciate any help you could provide. Thanks

Not sure if I understand everything, you are storing the products in one table and the part number in another correct?

If this is the case then this should work.

Dim strVar as string, str0 as string
Dim inInt as integer
strVar = Me!MyBox.column(0) & "P" & Me!MyAutoNumb
inint = len(strVar)
if inint<11 then
Do untill inint = 11
str0 = str0 & 0
strVar = Me!MyBox.column(0) & "P" & str0 & Me!MyAutoNumb
inint = len(strVar)
loop
 
Docmd.runsql("insert into PartNumberTable (PartNumber) VALUES ('" & strVar & "'))
 

[rousseaud]

Thanks! This is a big help. I had some trouble with the SQL in the last line and changed it a bit. Here's what I ended up using:

Dim strVar As String, str0 As String
Dim inInt As Integer
strVar = Me!MyBoxP.Column(0) & "P" & Me!MyAutoNumb
inInt = Len(strVar)
If inInt < 11 Then
Do Until inInt = 11
str0 = 0 & str0
strVar = Me!MyBox.Column(0) & "P" & str0 & Me!MyAutoNumb
inInt = Len(strVar)
Loop

End If

Me.PartNumber = strVar