Create list of serial numbers and insert into table (1 Viewer)

[Avoraightu]

I'm having a bit of trouble with my code:

    Dim strModel As String
    Dim strDateCode As String
    Dim strMsg As String
    Dim nValue As Variant
    Dim nQuantity As Variant
    Dim i As Integer
   
    If IsNull(Me.DateCode) Or IsNull(Me.ModelNumber) Or IsNull(Me.Quantity) Then
        MsgBox "Please ensure that all fields have been filled in.", vbExclamation
        Exit Sub
    Else
        strModel = Me.ModelNumber
        strDateCode = Me.DateCode
        nQuantity = Me.Quantity
    End If
    
    For i = 1 To nQuantity
        nValue = Me.subqryTEGSystemNumbers!AutoNumber + i
        
            If Me.ComboDC = "YES" Then
                strMsg = strModel & "-" & nValue & "-" & strDateCode
            Else
                strMsg = strModel & "-" & nValue
            End If
    Next i

What I am trying to do is generate a list of serial numbers to be inserted.

On the main form I have 3 main fields: Model, DateCode, Quantity

The user selects a model, and determines how many numbers they need.
The Date Code is generated on the form based on the date selected on the mini calendar of the date field.

So far I can get it to generate "n" numbers but I am struggling to get a list of those numbers into a single message box.
I have tried putting a messagebox before "Next i" and I get one message box for every serial number.
If I put it after the "Next i" I only get one message box with the last serial on it.

Once I have a list of numbers to display to the user, I will give them the option to print and insert the numbers into the table.

Thanks in advance

 

[Isaac]

build up strMsg inside the loop, show the msgbox after the loop.

 

[plog]

Your loop assigns a whole new value to strMsg each iteration. You need each iteration to add to whats in strMsg.

strMsg="First Assignment"
strMsg="Second Assignment"
strMSg="Third Assignment"
MsgBox(strMsg)

strMsg="Fourth Assigment"
strMsg=strMsg & " , First Addition"
strMsg=strMsg & ", Second Addition"
MsgBox(strMsg)

The above code spits out 2 message boxes. The first says "Third Assigment", the second will say "Fourth Assigment, First Addition, Second Addition". Your code inside the loop needs to make additions, not whole new assigments.

 

[plog]

Additionally, if/elses are kinda like algebra--if code is common to both cases you can move common code outside of the if/else:

4xy + 8x + 16y + 24 = 4(xy + 2x + 4y + 6)

Your if/else inside the loop doesn't need to have an else. Everything that happens in the else happens in the if, so you can move that code outside both of them and just use an if.

 

[arnelgp]

    Dim strModel As String
    Dim strDateCode As String
    Dim strMsg As String
    Dim nValue As Variant
    Dim nQuantity As Variant
    Dim i As Integer
  
    If IsNull(Me.DateCode) Or IsNull(Me.ModelNumber) Or IsNull(Me.Quantity) Then
        MsgBox "Please ensure that all fields have been filled in.", vbExclamation
        Exit Sub
    Else
        strModel = Me.ModelNumber
        strDateCode = Me.DateCode
        nQuantity = Me.Quantity
    End If
    
    For i = 1 To nQuantity
        nValue = Me.subqryTEGSystemNumbers!AutoNumber + i
        
            If Me.ComboDC = "YES" Then
                strMsg = strMsg & strModel & "-" & nValue & "-" & strDateCode & vbCrLf
            Else
                strMsg = strMsg & strModel & "-" & nValue & vbCrLf
            End If
    Next i

 

[gemma-the-husky]

@Avoraightu

Funny things, serial numbers.

They stop items being homogenous. I think a Serial Number might be placed on an item (eg a TV), but the vendor won't pick or check or even care about the supplied serial number. I think vendors wait for the buyer to register the product, and then store the serial number declared in the registration.

eg - what do you intend to do with your serial number records?

 

[Pat Hartman]

I have a similar process that creates labels for cartons based on the parameters in the receive form. Here's the code. Pay attention to the loop. That's what creates one record for each carton. The rest of the code validates the data.

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
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

Here's the form that goes with the code. It is all unbound. The code above is in the "Receive" button's click event.