user form overlabps the records if one field is not filled

Hello members!

My worksheet with user form is here. if I start entering first record and I leave one field(suppose Sr.No) empty for the first record and when I enter the second record with Sr. No. it overlaps my first record.Your kind suggestions are needed.

Best Regards
Bhatti

So how to make it search first empty range instead. So that if we dont fill a filed in one record it should not mix the second record with the previous one? My code is given below:

Private Sub cmdAdd_Click()
Dim lngWriteRow As Long

Dim ws As Worksheet
Set ws = Worksheets("MainTable")

lngWriteRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

ws.Range("a" & lngWriteRow) = txtSrNo.Value
ws.Range("b" & lngWriteRow) = txtAdmnDate.Value
ws.Range("c" & lngWriteRow) = txtAdmnNo.Value
ws.Range("d" & lngWriteRow) = txtAppDate.Value
ws.Range("e" & lngWriteRow) = txtName.Value
ws.Range("f" & lngWriteRow) = txtDOB.Value
ws.Range("g" & lngWriteRow) = CBReligion.Value
ws.Range("h" & lngWriteRow) = CBCat.Value
ws.Range("i" & lngWriteRow) = CBServingCat.Value
ws.Range("j" & lngWriteRow) = txtFName.Value
ws.Range("k" & lngWriteRow) = txtAddress.Value
ws.Range("l" & lngWriteRow) = txtMob.Value
ws.Range("n" & lngWriteRow) = CBClass.Value
ws.Range("o" & lngWriteRow) = CBSection.Value
ws.Range("p" & lngWriteRow) = CBChildNo.Value
ws.Range("q" & lngWriteRow) = txtSLCdate.Value
ws.Range("r" & lngWriteRow) = txtSLCNo.Value
ws.Range("s" & lngWriteRow) = CBCurrStatus.Value

Dim Wingchoice As String
If Me.OBJNRWing Then
Wingchoice = "JNR Wing"
ElseIf Me.OBMidWing Then
Wingchoice = "Middle Wing"
ElseIf Me.OBSnrWing Then
Wingchoice = "SNR Wing"
Else
Wingchoice = ""
End If
ws.Range("m" & lngWriteRow).Value = Wingchoice

End Sub


Private Sub cmdClr_Click()

txtSrNo.Value = ""
txtAdmnDate.Value = ""
txtAdmnNo.Value = ""
txtAppDate.Value = ""
txtName.Value = ""
txtDOB.Value = ""
CBReligion.Value = ""
CBCat.Value = ""
CBServingCat.Value = ""
txtFName.Value = ""
txtAddress.Value = ""
txtMob.Value = ""
CBClass.Value = ""
CBSection.Value = ""
CBChildNo.Value = ""
txtSLCdate.Value = ""
txtSLCNo.Value = ""
CBCurrStatus.Value = ""

End Sub
Private Sub cmdQuit_Click()
Unload FrmData
End Sub
Private Sub CBClass_Change()
Me.CBSection = ""

Select Case Me.CBClass
Case "One"
Me.CBSection.RowSource = "One"
Case "Two"
Me.CBSection.RowSource = "Two"
Case "Three"
Me.CBSection.RowSource = "Three"
Case "Four"
Me.CBSection.RowSource = "Four"
Case "Five"
Me.CBSection.RowSource = "Five"
Case "Six"
Me.CBSection.RowSource = "Six"
Case "Seven"
Me.CBSection.RowSource = "Seven"
Case "Eight"
Me.CBSection.RowSource = "Eight"
Case "Nine"
Me.CBSection.RowSource = "Nine"
Case "Ten"
Me.CBSection.RowSource = "Ten"

Case Else
'do nothing
End Select
End Sub

Thanks buran . this is what I wanted.but beside this I did another change to the code

Please Login or Register  to view this content.