Code: Select all
Private Sub cmdSubmit_Click()
Dim i As Integer
Dim f As Integer
Dim selectedPeople As String
Dim selectedAssets As String
Dim selectedAssembly As String
Dim selectedProcedures As String
Dim selectedTests As String
Dim strList As String
Dim ctl As Control
'Make certain fields required
If Me.cboLogEntryType.Value = "" Or Me.txtTitle.Value = "" Or _
Me.cboPerformedBy.Value = "" Or Me.cboLocation.Value = "" Or _
Me.txtDescription = "" Then
MsgBox "You are missing one or more required fields. Please make all required selections."
Exit Sub
End If
' Open file for appending
f = FreeFile
Open "C:\Desktop\LogFiles\TestLog.csv" For Append As #f
'Assign variable to multi-select listbox controls selected items
For i = 0 To Me.lstAdditionalPeople.ListCount - 1
If Me.lstAdditionalPeople.Selected(i) = True Then
selectedPeople = selectedPeople & "," & Chr(34) & _
Replace(Me.lstAdditionalPeople.List(i), Chr(34), Chr(39)) & Chr(34)
End If
Next i
For i = 0 To Me.lstAssets.ListCount - 1
If Me.lstAssets.Selected(i) = True Then
selectedAssets = selectedAssets & "," & Chr(34) & _
Replace(Me.lstAssets.List(i), Chr(34), Chr(39)) & Chr(34)
End If
Next i
For i = 0 To Me.lstAssembly.ListCount - 1
If Me.lstAssembly.Selected(i) = True Then
selectedAssembly = selectedAssembly & "," & Chr(34) & _
Replace(Me.lstAssembly.List(i), Chr(34), Chr(39)) & Chr(34)
End If
Next i
For i = 0 To Me.lstProcedures.ListCount - 1
If Me.lstProcedures.Selected(i) = True Then
selectedProcedures = selectedProcedures & "," & Chr(34) & _
Replace(Me.lstProcedures.List(i), Chr(34), Chr(39)) & Chr(34)
End If
Next i
For i = 0 To Me.lstDefinedTests.ListCount - 1
If Me.lstDefinedTests.Selected(i) = True Then
selectedTests = selectedTests & "," & Chr(34) & _
Replace(Me.lstDefinedTests.List(i), Chr(34), Chr(39)) & Chr(34)
End If
Next i
' Build comma-separated list
strList = Chr(34) & Replace(Me.cboLogEntryType, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.txtTitle, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.cboPerformedBy, Chr(34), Chr(39)) & Chr(34) & "," & _
selectedPeople & "," & Chr(34) & Replace(Me.dtpStartDate, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.dtpStartTime, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.dtpEndDate, Chr(34), Chr(39)) & "," & _
Chr(34) & Replace(Me.dtpEndTime, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.cboTestStation, Chr(34), Chr(39)) & Chr(34) & "," & selectedAssets & "," & _
selectedAssembly & "," & Replace(Me.cboTGINDataset, Chr(34), Chr(39)) & "," & _
Replace(Me.cboClassification, Chr(34), Chr(39)) & "," & Replace(Me.cboLocation, Chr(34), Chr(39)) & "," & _
Chr(34) & Replace(Me.cboLocationDetail, Chr(34), Chr(39)) & Chr(34) & "," & _
selectedProcedures & "," & selectedTests & "," & _
Chr(34) & Replace(Me.txtDescription, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.txtResults, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.txtStationPowerStartTime, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.txtStationPowerEndTime, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.txtStartupPowerStartTime, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.txtStartupPowerEndTime, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.txtScriptRepository, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.txtLoadRepository, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.cboBranch, Chr(34), Chr(39)) & Chr(34) & "," & _
Chr(34) & Replace(Me.txtOrProvideBranch, Chr(34), Chr(39)) & Chr(34)
' Output to file
Write #f, strList
' Close file
Close #f
' Clear controls
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox", "ComboBox"
ctl.Value = ""
End Select
Next ctl
For i = 0 To Me.lstAdditionalPeople.ListCount - 1
Me.lstAdditionalPeople.Selected(i) = False
Next i
For i = 0 To Me.lstAssets.ListCount - 1
Me.lstAssets.Selected(i) = False
Next i
For i = 0 To Me.lstAssembly.ListCount - 1
Me.lstAssembly.Selected(i) = False
Next i
For i = 0 To Me.lstProcedures.ListCount - 1
Me.lstProcedures.Selected(i) = False
Next i
For i = 0 To Me.lstDefinedTests.ListCount - 1
Me.lstDefinedTests.Selected(i) = False
Next i
End Sub