Embedded Files
Option Compare Database
Option Compare Database
Function Assurant_Parse()
'Reading from Database cell ***********
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
'Set rs = New ADODB.Recordset
Set rs = db.OpenRecordset("select * from qry_AssurantData")
With rs
If Not .BOF And Not .EOF Then
'We don’t know if the recordset has any records,
'so we use this line of code to check. If there are no records
'we won’t execute any code in the if..end if statement.
.MoveLast
.MoveFirst
'It is not necessary to move to the last record and then back
'to the first one but it is good practice to do so.
While (Not .EOF)
'With this code, we are using a while loop to loop
'through the records. If we reach the end of the recordset, .EOF
'will return true and we will exit the while loop.
Field1_VAR = rs.Fields("[Field1]")
Field2_VAR = rs.Fields("[Field2]")
Field3_VAR = rs.Fields("[Field3]")
Field4_VAR = rs.Fields("[Field4]")
Field5_VAR = rs.Fields("[Field5]")
If IsNull(Field1_VAR) Then
Field1_VAR = ""
End If
If IsNull(Field2_VAR) Then
Field2_VAR = ""
End If
If IsNull(Field3_VAR) Then
Field3_VAR = ""
End If
If IsNull(Field4_VAR) Then
Field4_VAR = ""
End If
If IsNull(Field5_VAR) Then
Field5_VAR = ""
End If
If Len(Field1_VAR) = 13 And IsNumeric(Field1_VAR) Then
Dim LessorVAR As String
Dim ContractVAR As String
Dim ScheduleVAR As String
Dim Contract As String
Dim CompanyName As String
Dim PolicyID As String
Dim EquipmentValue As String
Dim InsuranceCompany As String
LessorVAR = Left(Field1_VAR, 3)
ContractVAR = Mid(Field1_VAR, 4, 7)
ScheduleVAR = Right(Field1_VAR, 3)
Contract = LessorVAR & "-" & ContractVAR & "-" & ScheduleVAR
CompanyName = Trim(Field2_VAR)
PolicyID = Trim(Field3_VAR)
EquipmentValue = Trim(Replace(Field4_VAR, ",", ""))
InsuranceCompany = Trim(Field5_VAR)
End If
If Len(Field1_VAR) = 10 And IsDate(Field1_VAR) Then
OrigYear = Format(Field1_VAR, "YYYY")
OrigMonth = Format(Field1_VAR, "MM")
OrigDay = Format(Field1_VAR, "DD")
OriginationDate = OrigMonth & "/" & OrigDay & "/" & OrigYear
If OriginationDate = "//" Then
OriginationDate = ""
End If
CollateralClass = Trim(Field2_VAR)
EffYear = Format(Field3_VAR, "YYYY")
EffMonth = Format(Field3_VAR, "MM")
EffDay = Format(Field3_VAR, "DD")
EffDate = EffMonth & "/" & EffDay & "/" & EffYear
If EffDate = "//" Then
EffDate = ""
End If
EquipmentCode = Trim(Replace(Field4_VAR, ",", ""))
End If
If Field1_VAR = "LIAB" Or Field1_VAR = "COMMPD" Or Field1_VAR = "LIABAU" Or Field1_VAR = "AUTOPD" Then
TrackingClass = Field1_VAR
CollateralDescription = Field2_VAR
ExpYear = Format(Field3_VAR, "YYYY")
ExpMonth = Format(Field3_VAR, "MM")
ExpDay = Format(Field3_VAR, "DD")
ExpDate = ExpMonth & "/" & ExpDay & "/" & ExpYear
If ExpDate = "//" Then
ExpDate = ""
End If
CancelYear = Format(Field4_VAR, "YYYY")
CancelMonth = Format(Field4_VAR, "MM")
CancelDay = Format(Field4_VAR, "DD")
CancelDate = CancelMonth & "/" & CancelDay & "/" & CancelYear
If CancelDate = "//" Then
CancelDate = ""
End If
End If
If Len(Contract) = 15 And Len(OriginationDate) = 10 And (TrackingClass = "LIAB" Or TrackingClass = "COMMPD" Or TrackingClass = "LIABAU" Or TrackingClass = "AUTOPD") Then
Dim db2 As DAO.Database
Dim rs2 As DAO.Recordset
Set db2 = CurrentDb
'Set rs = New ADODB.Recordset
Set rs2 = db2.OpenRecordset("select * from Assurant_Load")
If rs2.Updatable Then
'It is possible that the record you want to update
'is locked by another user. If we don't check before
'updating, we will generate an error
rs2.AddNew
'Must start an update with the edit statement
rs2.Fields("[Contract]") = Contract
rs2.Fields("[Company_Name]") = CompanyName
rs2.Fields("[Policy_ID]") = PolicyID
rs2.Fields("[Equipment_Value]") = EquipmentValue
rs2.Fields("[Insurance_Company]") = InsuranceCompany
If Orig_Date <> "" Then
rs2.Fields("[Orig_Date]") = OriginationDate
End If
rs2.Fields("[Collateral_Class]") = CollateralClass
If Eff_Date <> "" Then
rs2.Fields("[Eff_Date]") = EffDate
End If
rs2.Fields("[Equipment_Code]") = EquipmentCode
rs2.Fields("[Tracking_Class]") = TrackingClass
rs2.Fields("[Collateral_Description]") = CollateralDescription
If Exp_Date <> "" Then
rs2.Fields("[Exp_Date]") = ExpDate
End If
If Cancel_Date <> "" Then
rs2.Fields("[Cancel_Date]") = CancelDate
End If
rs2.Fields("[Load_Date]") = Now
'Another way of accessing the fields would be to use
'.fields("FirstName") = z" & .fields("FirstName")
rs2.Update
End If
Contract = ""
CompanyName = ""
PolicyID = ""
EquipmentValue = ""
InsuranceCompany = ""
OriginationDate = ""
CollateralClass = ""
CollateralClass = ""
EffDate = ""
EquipmentCode = ""
TrackingClass = ""
CollateralDescription = ""
ExpDate = ""
CancelDate = ""
End If
.MoveNext
'We need to ensure that we use .MoveNext,
'otherwise we will be stuck in a loop forever…
'(or at least until you press CTRL+Break)
Wend
End If
.Close
'Make sure you close the recordset...
End With
MsgBox "The load was completed succesfully"
End Function
Page updated
Google Sites
Report abuse