Issue Creating Database in Visual Basic

[Titan1]

I an new to Visual Basic but cannot seem to get this code to populate the form data onto the spreadsheet. First row is fine but then wont add further data. Think that issue is in the 'RowCount' function... any help appreciated!



Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub cmdClear_Click()
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub


Private Sub cmdAdd_Click()


Dim RowCount As Long
Dim ctl As Control


' Check user input


If Me.txtName.Value = "" Then
MsgBox "Please enter a Name.", vbExclamation, "Contractor Form"
Me.txtName.SetFocus
Exit Sub
End If
If Me.txtSurname.Value = "" Then
MsgBox "Please enter a Surname.", vbExclamation, "Contractor Form"
Me.txtSurname.SetFocus
Exit Sub
End If
If Me.txtCompany.Value = "" Then
MsgBox "Please enter a Company.", vbExclamation, "Contractor Form"
Me.txtCompany.SetFocus
Exit Sub
End If
If Me.txtWebsite.Value = "" Then
MsgBox "Please enter a Website.", vbExclamation, "Contractor Form"
Me.txtWebsite.SetFocus
Exit Sub
End If
If Me.txtPosition.Value = "" Then
MsgBox "Please enter a Position.", vbExclamation, "Contractor Form"
Me.txtPosition.SetFocus
Exit Sub
End If
If Me.txtTel.Value = "" Then
MsgBox "Please enter a Tel.", vbExclamation, "Contractor Form"
Me.txtTel.SetFocus
Exit Sub
End If
If Me.txtMobile.Value = "" Then
MsgBox "Please enter a Mobile.", vbExclamation, "Contractor Form"
Me.txtMobile.SetFocus
Exit Sub
End If
If Me.txtEmail.Value = "" Then
MsgBox "Please enter a Email.", vbExclamation, "Contractor Form"
Me.txtEmail.SetFocus
Exit Sub
End If
If Me.txtnotes.Value = "" Then
MsgBox "Please enter a Notes.", vbExclamation, "Contractor Form"
Me.txtnotes.SetFocus
Exit Sub
End If

RowCount = Worksheets("ContractorData").Range("A1").CurrentRegion.Rows.Count

With Worksheets("ContractorData").Range("A1")

.Offset(RowCount, 0).Value = Me.txtName.Value
.Offset(RowCount, 1).Value = Me.txtSurname.Value
.Offset(RowCount, 2).Value = Me.txtCompany.Value
.Offset(RowCount, 3).Value = Me.txtWebsite.Value
.Offset(RowCount, 4).Value = Me.txtPosition.Value
.Offset(RowCount, 5).Value = Me.txtTel.Value
.Offset(RowCount, 6).Value = Me.txtMobile.Value
.Offset(RowCount, 7).Value = Me.txtEmail.Value
.Offset(RowCount, 8).Value = Me.txtnotes.Value
If Me.chkElectrician.Value = True Then
.Offset(RowCount, 9).Value = "Yes"
Else
.Offset(RowCount, 9).Value = "No"
End If

If Me.chkGeneralPlumber.Value = True Then
.Offset(RowCount, 10).Value = "Yes"
Else
.Offset(RowCount, 10).Value = "No"
End If

If Me.chkGasSafe.Value = True Then
.Offset(RowCount, 11).Value = "Yes"
Else
.Offset(RowCount, 11).Value = "No"
End If

If Me.chkHandyman.Value = True Then
.Offset(RowCount, 12).Value = "Yes"
Else
.Offset(RowCount, 12).Value = "No"
End If

If Me.chkCarpentry.Value = True Then
.Offset(RowCount, 13).Value = "Yes"
Else
.Offset(RowCount, 13).Value = "No"
End If

If Me.chkBrickwork.Value = True Then
.Offset(RowCount, 14).Value = "Yes"
Else
.Offset(RowCount, 14).Value = "No"
End If

If Me.chkTiling.Value = True Then
.Offset(RowCount, 15).Value = "Yes"
Else
.Offset(RowCount, 15).Value = "No"
End If

If Me.chkDecorating.Value = True Then
.Offset(RowCount, 16).Value = "Yes"
Else
.Offset(RowCount, 16).Value = "No"
End If

If Me.chkGutterClearance.Value = True Then
.Offset(RowCount, 17).Value = "Yes"
Else
.Offset(RowCount, 17).Value = "No"
End If

If Me.chkLiftMaintenance.Value = True Then
.Offset(RowCount, 18).Value = "Yes"
Else
.Offset(RowCount, 18).Value = "No"
End If

If Me.chkCommercialAC.Value = True Then
.Offset(RowCount, 19).Value = "Yes"
Else
.Offset(RowCount, 19).Value = "No"
End If

If Me.chkResidentialAC.Value = True Then
.Offset(RowCount, 20).Value = "Yes"
Else
.Offset(RowCount, 20).Value = "No"
End If

If Me.chkPlumbingCommercial.Value = True Then
.Offset(RowCount, 21).Value = "Yes"
Else
.Offset(RowCount, 21).Value = "No"
End If

If Me.chkPlasterer.Value = True Then
.Offset(RowCount, 22).Value = "Yes"
Else
.Offset(RowCount, 22).Value = "No"
End If

If Me.chkDrainJetting.Value = True Then
.Offset(RowCount, 23).Value = "Yes"
Else
.Offset(RowCount, 23).Value = "No"
End If
If Me.chkHabitationCheck.Value = True Then
.Offset(RowCount, 24).Value = "Yes"
Else
.Offset(RowCount, 24).Value = "No"
End If
If Me.chkVacantPropertyInspection.Value = True Then
.Offset(RowCount, 25).Value = "Yes"
Else
.Offset(RowCount, 25).Value = "No"
End If
If Me.chkRiskAssessment.Value = True Then
.Offset(RowCount, 26).Value = "Yes"
Else
.Offset(RowCount, 26).Value = "No"
End If
If Me.chkSecurity.Value = True Then
.Offset(RowCount, 27).Value = "Yes"
Else
.Offset(RowCount, 27).Value = "No"
End If
If Me.chkGlazing.Value = True Then
.Offset(RowCount, 28).Value = "Yes"
Else
.Offset(RowCount, 28).Value = "No"
End If

If Me.chkLocksmith.Value = True Then
.Offset(RowCount, 29).Value = "Yes"
Else
.Offset(RowCount, 29).Value = "No"
End If

If Me.chkPestControl.Value = True Then
.Offset(RowCount, 30).Value = "Yes"
Else
.Offset(RowCount, 30).Value = "No"
End If

If Me.chkWasteClearance.Value = True Then
.Offset(RowCount, 31).Value = "Yes"
Else
.Offset(RowCount, 31).Value = "No"
End If

If Me.chkPATTesting.Value = True Then
.Offset(RowCount, 32).Value = "Yes"
Else
.Offset(RowCount, 32).Value = "No"
End If

End With


' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl


End Sub