Access 2013 - sequential numbering without autonumber

Question

New to this site and access. I am trying to create a database to keep track of a master log. Need help with sequential numbering. I currently have the following tables.

PO Table POID - Autonumber(PK)
PODate - Date/Time
Supplier - String

Item Table ItemID - Autonumber(PK)
POID - Ingeter(FK)
ItemDescription - String
Quantity - Integer

MasterLog Table MasterLogID - Autonumber(PK)
ItemID - Integer(FK)
PieceNumber - Integer ( 1,2,3 ... etc)
MFG - String
Process - String
Length - Integer
MfgIDNum - String (ABD123XTY-1234)

I am trying to automate the data entry of the PieceNumber field. So when you enter a new PO and add items to it, once received. It will add a row to the masterlog table starting at piece number 1 through how ever many pieces we have. We number the pieces based on the items we purchased.(i.e. with Item 1 we purchased 100 pieces. So I would have Item 1 piece 1 through 100.) Then we are able to add the other data to the table. Just trying to reduce some data entry time and avoid some mistakes in the numbering of this field.

Any ideas?

JJ32 JJ32 · Accepted Answer · 2016-06-02T21:24:31

Something like this would be a very simple way of doing it. You'd have to have a predefined table called Numbers with integers starting from 1 to however high a quantity you might have:

INSERT INTO MasterLog (ItemID, PieceNumber)
SELECT Item.ItemID, Numbers.Number
FROM Item, Numbers
WHERE (Item.ItemID = Forms!Items!ItemID) AND 
   (Numbers.Number <= Item.Quantity) 
ORDER BY Numbers.Number

If you wanted to add the pieces one by one you could default the PieceNumber field on the related form. Make sure you default MasterLog.ItemID as well:

=Nz(DMax("[PieceNumber]","[MasterLog]","[ItemID] = " & Forms!Items!ItemID), 0) + 1

For a VBA solution try something like this:

Dim db As Database
Dim strSQL As String
Dim frm As Form
Dim i As Integer

    Set db = CodeDb()
    Set frm = Forms!Items

    If frm!Quantity > 0 Then
        For i = 1 To frm!Quantity
            strSQL = "INSERT INTO MasterLog (ItemID, PieceNumber) " & _
                "SELECT " & frm!Item & " AS ItemID, " & _
                i & " AS PieceNumber"
            db.Execute strSQL, dbFailOnError
        Next i
    End If

    Set db = Nothing

All of these presume a form displaying your current item called Items.

Access 2013 - sequential numbering without autonumber

1 Answers