Solved: Userform looping & adding 2nd record to database

[Rob342]

Hi
I have created a userform that adds a record to a sheet, which works ok for the 1st one.
The problem comes when i need to add a 2nd record to the sheet, keeping the same details except the fault code will be different.

The user will fill out the form & be added to the main sheet, i then need it to loop around the form again allowing the user to input a new flt code and resetting the wipno & jobno to "".

I have tried putting in a counter but this resets to zero everytime the form is initialised.

Can anybody point me in the right direction or have got any code that i could use.

Rob

[Bob Phillips]

Private Sub AddNewRec_Click()
Dim IRow As Long
Dim IFran As String
Dim Imonth As String
Dim IYear As String
Dim cboBranFran As String
Dim cboMonth As String
Dim cboYear As String
Dim cboFaultCode As String
Dim TxtTechNo As String
Dim TxtRecpId As String
Dim TxtWipNo As String
Dim TxtJobNo As String
Dim ws As Worksheet

Set ws = Worksheets("Main")
IRow = ws.Cells(Rows.count, 1) _
.End(xlUp).Offset(1, 0).Row

IFran = Me.cboBranFran.ListIndex
Imonth = Me.cboMonth.ListIndex
IYear = Me.cboYear.ListIndex

'// Check for BranchFranchise
If Trim(Me.cboBranFran.Value) = "" Or IFran = "-1" Then
Me.cboBranFran.SetFocus
MsgBox "You Must Select a Franchise", , "Branch Franchise Selection "
Exit Sub
End If
'// Check For Month
If Trim(Me.cboMonth.Value) = "" Or Imonth = "-1" Then
Me.cboMonth.SetFocus
MsgBox " You Must Select a Month", , "Month Selection"
Exit Sub
End If

'// Input the Year Automatically
'If Trim(Me.cboYear.Value) = "" Or IYear = "-1" Then
' Me.cboYear.SetFocus
' MsgBox "You Must Select a Year", , "Year selection"
' Exit Sub
'End If

'// Check for valid fault code
If Trim(Me.cboFaultCode.Value) = "" Or cboFaultCode = "-1" Then
Me.cboFaultCode.SetFocus
MsgBox " You Must Select a Valid Fault Code From The List", , "Fault Code Selection"
Exit Sub
End If

'// Check for Technician number dont validate as field could be empty
'If Trim(Me.TxtTechNo.Value) = "" Or IsNumeric(Me.TxtTechNo.Value) = False Then
' Me.TxtTechNo.SetFocus
' MsgBox "The Technician Number must be Numeric", , "Technician Number"
' Exit Sub
'End If

'// Check for Receptionist ID dont validate as the field could be empty
'If Trim(Me.TxtRecpId.Value) = "" Or IsNumeric(Me.TxtRecpId.Value) = True Then
' Me.TxtRecpId.SetFocus
' MsgBox "The Receptionist Id Must Not Be Numeric", , "Receptionist Id"
' Exit Sub
'End If

'// Check for a Wip Number
If Trim(Me.TxtWipNo.Value) = "" Or IsNumeric(Me.TxtWipNo.Value) = False Then
Me.TxtWipNo.SetFocus
MsgBox "The Wip Number Cannot Be Blank & Must be Numeric", , "Wip Number"
Exit Sub
End If

'// Check for a Job Number
If Trim(Me.TxtJobNo.Value) = "" Or IsNumeric(Me.TxtJobNo.Value) = False Then
Me.TxtJobNo.SetFocus
MsgBox "The Job Number Cannot Be Blank & Must be Numeric", , "Job Number"
Exit Sub
End If

'// All the checks done & ok then write the data to the main sheet
With ws

.Cells(IRow, 1).Value = Me.cboBranFran.Value
.Cells(IRow, 2).Value = Me.cboMonth.Value
.Cells(IRow, 3).Value = Me.cboYear.Value
.Cells(IRow, 4).Value = Me.cboFaultCode.Value
.Cells(IRow, 5).Value = Me.TxtTechNo.Value
.Cells(IRow, 6).Value = Me.TxtRecpId.Value
.Cells(IRow, 7).Value = Me.TxtWipNo.Value
.Cells(IRow, 8).Value = Me.TxtJobNo.Value
End With

If MsgBox(" Do You Want To Add Another Fault For The Same Wip / Job no ?", vbYesNo) _
= vbYes Then

'// Routine to be added to loop for another fault with same wip/job no
' keep the franchise,month,year same reset focus to fltcode & wipno/jobno set to ""
' code to be inserted here ***************************************
Else

Unload Me
End If
End Sub

[Rob342]

Hi Xld
Thanks for the reply

Did i miss something, thats the same code as in the template
Are you saying thats correct ?
Now confused.

Rob

[Bob Phillips]

No, I changed it at the end.

[Rob342]

Xld

I missed the else bit, the simplist corrections are always the best.
I think i was trying too hard to make it work in another way.
Can carry on and finish this now.

Thanks again
Rob