Auto Serial# linked to Invoice#

[jarrallian]

Hi,

I have this situation:

My userform has Serial# and INV # as two fields. I want the Serial# to increase by 1 every time the INV # changes.

Currently, I have the following Code. Please, suggest me the changes that I need to make.

Thanks in anticipation.

This is the CODE:

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lDate As Long
Dim ws As Worksheet
Set ws = Worksheets("Purchases")

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

lMedicine = Me.cboMedicine.ListIndex

'check for a Date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter a Valid Date"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.txtDate.Value
.Cells(lRow, 2).Value = Me.txtRV.Value
.Cells(lRow, 3).Value = Me.txtINV.Value
.Cells(lRow, 4).Value = Me.cboSupplier.Value
.Cells(lRow, 5).Value = Me.cboMedicine.Value
.Cells(lRow, 6).Value = Me.cboMedicine.List(lMedicine, 1)
.Cells(lRow, 7).Value = Me.txtQuantity.Value
.Cells(lRow, 8).Value = Me.txtNetAmount.Value

End With

'clear the data
Me.txtRV.Value = ""
Me.txtINV.Value = ""
Me.cboSupplier.Value = ""
Me.cboMedicine.Value = ""
Me.txtQuantity.Value = ""
Me.txtNetAmount.Value = ""


End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
txtDate.Text = Date
Dim cSupplier As Range
Dim cMedicine As Range
Dim ws As Worksheet
Set ws = Worksheets("Database")

For Each cSupplier In ws.Range("Supplier")
With Me.cboSupplier
.AddItem cSupplier.Value

End With

Next cSupplier

For Each cMedicine In ws.Range("Medicine")
With Me.cboMedicine
.AddItem cMedicine.Value
.List(.ListCount - 1, 1) = cMedicine.Offset(0, 1).Value

End With

Next cMedicine

Me.txtRV.SetFocus

End Sub


Private Sub txtDate_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 186 Then txtDate.Value = Format(Date, "mm-dd-yyyy") 'Ctrl+; = 186

End Sub
Private Sub txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtDate = vbNullString Then Exit Sub

If Not IsDate(txtDate) Then
MsgBox "Invalid Date"
Cancel = True
End If
End Sub

Private Sub cboSupplier_Change()
If cboSupplier.MatchFound = False Then
MsgBox "Please choose from the list"
End If
End Sub