Sequential Numbering

[mattc66 via AccessMonster.com]

I need to create a form that will create records based on the following
details.

The user will tell me the quantity of serial numers they need. I get the
first part of the serial number and then it's a 3 digit sequential number to
the total needed.

Example: K05-272-xxx The xxx is the sequence number starting with 001 to the
number needed. How would I do the code to create the sequence number.

Matt

 

[Marco]

Friends and friends around the world.
We are presenting the new messenger.
He will be in the commissioning by the friends that we indicate to use the
system.
Access the system by clicking the link below and register free.

You get something for using orkut?
You get something for using skype?
You gain something by using twiter?
You get algumaocisa for using facebook?

Enjoy this is your time!!

Sign up and join for free.


http://www.sqipcom.com/?ref=webempreendedor

http://stakeholder.sqipcom.com/user/webempreendedor

 

[mattc66 via AccessMonster.com]

The below code does almost what I need. With one issue. I want the sequence
number to be 3 digits that include leading zeros. So if it's 40 it would read
040 if it was 4 it would read 004 and if it was 400 it would read 400. What
could I do that accomplish this?
Private Sub cmdSeqCreate_Click()

Dim Qty As Integer
Qty = Me.Qty
Dim Serial As String
Serial = Me.Serial
Dim LocID As String
LocID = Me.LocID
Dim Item As String
Item = Me.Item
Dim DateInput As Date
DateInput = Me.DateInput
Dim RecAdded As Date
RecAdded = Me.RecAdded
Dim InputID As String
InputID = Me.InputID

Dim db As Database


Set db = CurrentDb
Set rs = db.OpenRecordset("tblSeqNumRooms", dbOpenTable)

Do While Qty <> 0

rs.AddNew
rs.Qty = Qty
rs.Serial = Serial
rs.LocID = LocID
rs.Item = Item
rs.InputID = InputID
rs.DateInput = DateInput
rs.RecAdded = RecAdded
rs.SerialNum = [Serial] & "-" & [Qty]

rs.Update

Qty = Qty - 1
Loop
rs.Close

Me!sfrmSeqNumRoom.Form.Requery
End Sub

 

[Marshall Barton]

rs.SerialNum = Serial & "-" & Format(Qty, "000")

BUT! You should not combine two values into a single field
(it's a Normaliation Rule). Instead, you shoud use two
fields, one for the serial string and another for the
sequential number. Also helpful if you should ever need to
add more numbers to the same serial. When if comes time to
display the combined string to users, just use a text box
with an expression like the above.
--
Marsh
MVP [MS Access]

The below code does almost what I need. With one issue. I want the sequence
number to be 3 digits that include leading zeros. So if it's 40 it would read
040 if it was 4 it would read 004 and if it was 400 it would read 400. What
could I do that accomplish this?
Private Sub cmdSeqCreate_Click()

Dim Qty As Integer
Qty = Me.Qty
Dim Serial As String
Serial = Me.Serial
Dim LocID As String
LocID = Me.LocID
Dim Item As String
Item = Me.Item
Dim DateInput As Date
DateInput = Me.DateInput
Dim RecAdded As Date
RecAdded = Me.RecAdded
Dim InputID As String
InputID = Me.InputID

Dim db As Database


Set db = CurrentDb
Set rs = db.OpenRecordset("tblSeqNumRooms", dbOpenTable)

Do While Qty <> 0

rs.AddNew
rs.Qty = Qty
rs.Serial = Serial
rs.LocID = LocID
rs.Item = Item
rs.InputID = InputID
rs.DateInput = DateInput
rs.RecAdded = RecAdded
rs.SerialNum = [Serial] & "-" & [Qty]

rs.Update

Qty = Qty - 1
Loop
rs.Close

Me!sfrmSeqNumRoom.Form.Requery
End Sub

I need to create a form that will create records based on the following
details.

The user will tell me the quantity of serial numers they need. I get the
first part of the serial number and then it's a 3 digit sequential number to
the total needed.

Example: K05-272-xxx The xxx is the sequence number starting with 001 to the
number needed. How would I do the code to create the sequence number.

Matt

 

[mattc66 via AccessMonster.com]

That worked -Thanks

rs.SerialNum = Serial & "-" & Format(Qty, "000")

BUT! You should not combine two values into a single field
(it's a Normaliation Rule). Instead, you shoud use two
fields, one for the serial string and another for the
sequential number. Also helpful if you should ever need to
add more numbers to the same serial. When if comes time to
display the combined string to users, just use a text box
with an expression like the above.

The below code does almost what I need. With one issue. I want the sequence
number to be 3 digits that include leading zeros. So if it's 40 it would read

[quoted text clipped - 56 lines]

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum forseeable.

INSERT INTO tblSeqNumRooms ( Serial )
SELECT [Enter Start of Serial #] & Right("00" & [CountNUM],3) AS Expr2
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=[Enter qunaity of records] And
(CountNumber.CountNUM)>0));


--
Build a little, test a little.

The below code does almost what I need. With one issue. I want the sequence
number to be 3 digits that include leading zeros. So if it's 40 it would read
040 if it was 4 it would read 004 and if it was 400 it would read 400. What
could I do that accomplish this?
Private Sub cmdSeqCreate_Click()

Dim Qty As Integer
Qty = Me.Qty
Dim Serial As String
Serial = Me.Serial
Dim LocID As String
LocID = Me.LocID
Dim Item As String
Item = Me.Item
Dim DateInput As Date
DateInput = Me.DateInput
Dim RecAdded As Date
RecAdded = Me.RecAdded
Dim InputID As String
InputID = Me.InputID

Dim db As Database


Set db = CurrentDb
Set rs = db.OpenRecordset("tblSeqNumRooms", dbOpenTable)

Do While Qty <> 0

rs.AddNew
rs.Qty = Qty
rs.Serial = Serial
rs.LocID = LocID
rs.Item = Item
rs.InputID = InputID
rs.DateInput = DateInput
rs.RecAdded = RecAdded
rs.SerialNum = [Serial] & "-" & [Qty]

rs.Update

Qty = Qty - 1
Loop
rs.Close

Me!sfrmSeqNumRoom.Form.Requery
End Sub

I need to create a form that will create records based on the following
details.

The user will tell me the quantity of serial numers they need. I get the
first part of the serial number and then it's a 3 digit sequential number to
the total needed.

Example: K05-272-xxx The xxx is the sequence number starting with 001 to the
number needed. How would I do the code to create the sequence number.

Matt

--
Matt Campbell
mattc (at) saunatec [dot] com




.