increment by 1

[YouMust]

[SOLVED] Increment by 1

hello,

I hope you can help I've made a script that takes:

1. Barcode
2. Part Number
3. Recieved date
4. quantity

User inputs the first barcode '100' part number and date received followed by the quantity (10).

the VBA should now make 10 entries in the table and increase the barcode on each record by one.

so we will have 10 barcodes starting at 100 and finishing at 109 (total 10 barcodes).

But my code is just adding another digit on to the end of the barcode and counting up from there, so the barcode reads 1001 instead of 101.

Private Sub ok_Click()
On Error GoTo ErrorHandler
    Dim i, count As Long
    
    If (IsNull(Me.txtBarCode) Or Trim(Me.txtBarCode & "") <> "") Or (IsNull(Me.QtyTxt) Or Trim(Me.QtyTxt & "") <> "") Or IsNull(Me.Recieved_date) Then
        DoCmd.Hourglass True
        count = CLng(Me.QtyTxt) - 1
        For i = 0 To count
        DoCmd.RunSQL "INSERT INTO tblGoodsIn (Barcode, PrtNo, [Recieved date]) VALUES(" & CLng(Me.txtBarCode & i) & ", '" & Me.PrtNo & "', #" & Me.Recieved_date & "#);"
        Next i
        DoCmd.Hourglass False
        MsgBox "Successfully Added", vbInformation, "Completed"
    Else
        MsgBox "Barcode, Recieved Date and Quantity is required.", vbExclamation, "Warning!"
        Exit Sub
    End If

Exit_Handler:
    DoCmd.Hourglass False
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DeleteFailure", vbCritical, "ERROR"
    Resume Exit_Handler

End Sub

some advise would be fantastic!

Kind Regards


Edit fixed just changed the & to +

VALUES(" & CLng(Me.txtBarCode + i)

 

[arnelgp]

DoCmd.RunSQL "INSERT INTO tblGoodsIn (Barcode, PrtNo, [Recieved date]) VALUES(" & (CLng(Me.txtBarCode) + i) & ", '" & Me.PrtNo & "', #" & Me.Recieved_date & "#);"

 

[YouMust]

thanks arnelgp

I owe you a drink

 

[Cronk]

To stop the Barcode value in the table being incremented by one, remove the bit in red
VALUES(" & (CLng(Me.txtBarCode) + i) & ", '" & Me.PrtNo & "', #" & Me.Recieved_date & "#);"

 

[YouMust]

hey cronk thanks for your input,

the issue was that it was adding an extra digit to the end of the barcode due to the use of & i changing that to + i corrected the issue

although the part to check for values is not working

    If (IsNull(Me.txtBarCode) Or Trim(Me.txtBarCode & "") <> "") Or (IsNull(Me.QtyTxt) Or Trim(Me.QtyTxt & "") <> "") Or IsNull(Me.Recieved_date) Then
        DoCmd.Hourglass True

if i put no values in it still says successful :/

 

[arnelgp]

If (Trim(Me.txtBarCode & "") <> "") And (Trim(Me.QtyTxt & "") <> "") And (Trim(Me.Recieved_date & "") <> "") Then

 

[YouMust]

arnelgp to the rescue again!

you should have a donation button in your signature!