Auto Number Fill

[Balfour211]

I am using Access 2003. My question concerns serial numbers and auto fill in
when shipments arrive. My widgits usually come in cases of 24, but not
always. Each of them have their own serial number. When they come in the
case, all of the serial numbers are in order going up by 1. Here is what I
would like to do:

I have the main table that tracks the widgits using the serial numbers.
When a shipment comes in, I would like to use a form to enter the new
inventory. I would have the serial number box bound to the main table, an
unbound text box where I could enter the number of widgits in the case, and
an unbound text box where I could enter the starting serial number. I would
then like to push a command button and have the widgits entered into my
inventory with their serial numbers.

My idea was to enter the first widgit with the starting serial number into
the table, have the code add 1 to the starting serial number, enter that
value into the table, and continue to do this until 24 widgits have been
entered.

Could someone help me with the code that would do this.

Thanks,
Balfour211

 

[KARL DEWEY]

Create a table named CountNumber with field named CountNUM containing number
from 0 (zero) through your maximum spread.
INSERT INTO YourTable ( SN )
SELECT [Enter Starting Serial #]+[CountNUM] AS Expr1
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=[Enter qunaity of records]-1));

Reference the textboxes on the form instead of [Enter Starting Serial #]
and [Enter qunaity of records] to enter data from form.

 

[Christopher Robin]

Would something like this work for you?

Dim SerNo As Double
Dim i As Integer, WidgetCount As Integer
Dim stSQL As String

i = 1
WidgetCount = Me.WidgetCount
SerNo = Me.StartingSerialNumber

While i <= WidgetCount

stSQL = "INSERT INTO YourTable(SN)VALUES (" &
Convert.ToString(SerNo) & ")"
CurrentDb.Execute(stSQL)

SerNo = SerNo + 1
i = i + 1

End While

Create a table named CountNumber with field named CountNUM containing number
from 0 (zero) through your maximum spread.
INSERT INTO YourTable ( SN )
SELECT [Enter Starting Serial #]+[CountNUM] AS Expr1
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=[Enter qunaity of records]-1));

Reference the textboxes on the form instead of [Enter Starting Serial #]
and [Enter qunaity of records] to enter data from form.

--
KARL DEWEY
Build a little - Test a little

I am using Access 2003. My question concerns serial numbers and auto fill in
when shipments arrive. My widgits usually come in cases of 24, but not
always. Each of them have their own serial number. When they come in the
case, all of the serial numbers are in order going up by 1. Here is what I
would like to do:

I have the main table that tracks the widgits using the serial numbers.
When a shipment comes in, I would like to use a form to enter the new
inventory. I would have the serial number box bound to the main table, an
unbound text box where I could enter the number of widgits in the case, and
an unbound text box where I could enter the starting serial number. I would
then like to push a command button and have the widgits entered into my
inventory with their serial numbers.

My idea was to enter the first widgit with the starting serial number into
the table, have the code add 1 to the starting serial number, enter that
value into the table, and continue to do this until 24 widgits have been
entered.

Could someone help me with the code that would do this.

Thanks,
Balfour211

 

[Balfour211]

My SQL is not very good, so please bear with me. I have tried to use your
example, and I think I have missed the mark somewhere. On my form, I am
using the text boxes for the starting serial number (txtStart_Number) and for
the new widget count (txtHow_Many). The table that the widget info is stored
in is called "Widgets", and the field for the serial number is "Serial_No".
I put the code that you suggested in so it would be started with the use of a
command button (cmdEnter). Here is what I got.

Private Sub cmdEnter_Click()

Dim Serial_No As Double
Dim i As Integer, txtHow_Many As Integer
Dim stSQL As String

i = 1
txtHow_Many = Me.txtHow_Many
Serial_No = Me.txtStart_Number

While i <= txtHow_Many

stSQL = "INSERT INTO (Widgets) values (" & convert.tostring(Serial_No) & ")"

CurrentDb.Execute (stSQL)

Serial_No = Serial_No + 1
i = i + 1

Wend

End Sub

Would something like this work for you?

Dim SerNo As Double
Dim i As Integer, WidgetCount As Integer
Dim stSQL As String

i = 1
WidgetCount = Me.WidgetCount
SerNo = Me.StartingSerialNumber

While i <= WidgetCount

stSQL = "INSERT INTO YourTable(SN)VALUES (" &
Convert.ToString(SerNo) & ")"
CurrentDb.Execute(stSQL)

SerNo = SerNo + 1
i = i + 1

End While

Create a table named CountNumber with field named CountNUM containing number
from 0 (zero) through your maximum spread.
INSERT INTO YourTable ( SN )
SELECT [Enter Starting Serial #]+[CountNUM] AS Expr1
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=[Enter qunaity of records]-1));

Reference the textboxes on the form instead of [Enter Starting Serial #]
and [Enter qunaity of records] to enter data from form.

--
KARL DEWEY
Build a little - Test a little

I am using Access 2003. My question concerns serial numbers and auto fill in
when shipments arrive. My widgits usually come in cases of 24, but not
always. Each of them have their own serial number. When they come in the
case, all of the serial numbers are in order going up by 1. Here is what I
would like to do:

I have the main table that tracks the widgits using the serial numbers.
When a shipment comes in, I would like to use a form to enter the new
inventory. I would have the serial number box bound to the main table, an
unbound text box where I could enter the number of widgits in the case, and
an unbound text box where I could enter the starting serial number. I would
then like to push a command button and have the widgits entered into my
inventory with their serial numbers.

My idea was to enter the first widgit with the starting serial number into
the table, have the code add 1 to the starting serial number, enter that
value into the table, and continue to do this until 24 widgits have been
entered.

Could someone help me with the code that would do this.

Thanks,
Balfour211