Auto Set Date and Auto Serial Number

[jarrallian]

Hi,

I have this situation:

My userform has Date, RV # and INV # as the first three fields. I want the Date field to show current date automatically, and secondly, I want my RV # to increase in a serial 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 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.txtDate.Value = ""
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()
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.txtQuantity.Value = ""
Me.txtNetAmount.Value = ""
Me.txtDate.SetFocus

End Sub