Create list of serial numbers and insert into table

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