CREATE A CERTAIN NUMBER OF RECORDS WITH AUTONUMBER (1 Viewer)

[georg0307]

Dear all,
I would need a field where specify a certain number of records destined to print labels (rptLabels). The Trolley (SKU) field is "Autonumber", while in "NR" I would like to be able to enter the number of unique records to be created. Attached the Database. Thanks in advance to anyone who wanted to help me. For I.E:

NR: 20

The result should be:

TROLLEY
10000000
10000001
10000002
10000003
10000004
10000005
10000006
10000007
10000008
10000009
10000010
10000011
10000012
10000013
10000014
10000015
10000016
10000017
10000018
10000019

Best regards,
Georg B.

 

[arnelgp]

you need to Modify the Report since it uses my printer.

 

[arnelgp]

SKU should be Short text rather.

 

[georg0307]

Ciao,

thanks for the help, just a little thing because I print several times and I need to maintain uniqueness number, I mean:

first time I print 25 labels... starting 10000004 to 10000029

next time I print 11 labels... starting 10000029 to 10000040

... so and so

Sorry for my terrible English, I hope you understand..

 

[arnelgp]

See post #1. It is based on initial sku. So it you printed 10 for the sku, to print the next 10, chng the sku to orig sku+10.

 

[Pat Hartman]

Here's a form and the code behind it from an app where the client wanted to assign a unique ID to each carton received so he could control the order in which items were sold.

Code in the Receive button:

Private Sub cmdReceive_Click()
    Dim db As DAO.Database
    Dim Td As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim CtnCount As Integer
On Error GoTo Err_Proc
   
    Me.txtCountCtnsReceived.Visible = False
    If Me.cboProductID & "" = "" Then
        MsgBox "Please enter a Product ID.", vbOKOnly + vbExclamation
        Exit Sub
    End If
    If Me.cboWarehouse & "" = "" Then
        MsgBox "Please enter a Warehouse.", vbOKOnly + vbExclamation
        Exit Sub
    End If
    If Me.cboPO & "" = "" Then
        MsgBox "Please enter a PO.", vbOKOnly + vbExclamation
        Exit Sub
    End If
    If Me.cboBill & "" = "" Then
        MsgBox "Please enter a Bill.", vbOKOnly + vbExclamation
        Exit Sub
    End If
    If Me.txtStartCtnNum & "" = "" Then
        MsgBox "Please enter a starting carton number.", vbOKOnly + vbExclamation
        Exit Sub
    End If
    If Me.txtEndCtnNum & "" = "" Then
        MsgBox "Please enter an ending carton number.", vbOKOnly + vbExclamation
        Exit Sub
    End If
    If Me.txtStartCtnNum > Me.txtEndCtnNum Then
        MsgBox "Starting carton number must be less than ending carton number.", vbOKOnly + vbExclamation
        Exit Sub
    End If
   
    Set db = CurrentDb()
   
    i = Me.txtStartCtnNum
    CtnCount = 0
    Set Td = db.TableDefs!tblDetail
    Set rs = Td.OpenRecordset
    Do Until i > Me.txtEndCtnNum
        CtnCount = CtnCount + 1
        rs.AddNew
        rs!ProdID = Me.cboProductID
        rs!WarehouseID = Me.cboWarehouse
        rs!CtnNum = i
        rs!PO = Me.cboPO
        rs!Bill = Me.cboBill
        rs!ReceivedDate = Me.txtReceiveDate
        rs.Update
        i = i + 1
    Loop
    Me.txtCountCtnsReceived = CtnCount
    Me.txtCountCtnsReceived.Visible = True
'   Forms!frminventory!lstCartons.Requery

    Call cmdClear_Click         '' changed 3/10/17
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbOKOnly + vbCritical
            Resume Exit_Proc
    End Select
End Sub

Private Sub cmdClear_Click()
    Me.cboBill = Null
    Me.cboPO = Null
    Me.cboWarehouse = Null
    Me.cboProductID = Null
    Me.txtStartCtnNum = Null
    Me.txtEndCtnNum = Null
    Me.txtReceiveDate = Date
End Sub

 

[georg0307]

Hi,

thanks for prompt reply, I need really something very simple, an autonumber field and e a second field where I outnumber of label I want print.
This mean that the next time I go to print the autonumber may start form the last number created + 1.

Sorry if my English is not so good, I hope you understand me...

Thanks Best regards,

Georg

 

[Pat Hartman]

Your English is fine. Hope this response makes sense if you have to run it through a translator. Sometimes technical writing comes out very funny

There is no built in function. For simple tasks you might be able to use a table with a list of numbers. The table needs to include a row for each number. So if your max is 100, the table needs 100 rows with a field containing the values 1-100.

You can then create a query that joins to this table. Use the QBE to build the query and draw a join line from your count field to the sequence number field in the list of numbers. Then switch the query to design view and change the relational operator on the join from

From tbl1 Inner Join tbl2 ON tbl1.BoxCount = tbl2.SeqNum

To

From tbl1 Inner Join tbl2 ON tbl1.BoxCount <= tbl2.SeqNum

Once you make this change, you can NEVER open this query in QBE view again since QBE view only supports equi-joins (equal)

This join will get you n rows depending on the value of count because it selects all rows in tbl2 where the sequence number is <= the count field. If the count field = 5, you'll get 5 rows. If it is 100 you'll get 100 rows. If the max sequence number in tbl2 is 100 and count = 150, you'll get only 100 rows so be mindful of your actual max.