Option Explicit

Private VBADS As Display
Private ConnString As String

Private Sub VBAStartup()
    On Error GoTo lblError
    Set VBADS = LoadedDisplays.Item("VBAGlobal")
    VBADS.StartUpActions Me.Name
    Exit Sub
lblError:
    LogDiagnosticsMessage "VBAStartup():" & Err.Description, ftDiagSeverityError
End Sub

Private Sub Display_Load()
    On Error GoTo lblError
    Call VBAStartup
    
    'BatchHistoryEX
    'Set the SQL Server connection string
    ConnString = "Provider=sqloledb;" & _
                 "Data Source=APISRBTH01\SQLEXPRESS;" & _
                 "Initial Catalogy=BatchHistoryEX;" & _
                 "User ID=sa;" & _
                 "Password=*API.ft!DCS2018;"
    
    Call LoadBatchIDCount
    Call LoadBatchIDs(10)
    Call ClearReport
        
lblShutdown:
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "Display_Load():" & Err.Description, ftDiagSeverityError
End Sub

Private Sub LoadBatchIDCount()
    On Error GoTo lblError
    
    With BatchIDCount
        .Clear
        
        .AddItem "Most Recent 10"
        .AddItem "Most Recent 25"
        .AddItem "Most Recent 50"
        .AddItem "Most Recent 100"
        .AddItem "Most Recent 250"
        .AddItem "Most Recent 1000"
        .AddItem "All"
        
        .ListIndex = 0
    End With
        
lblShutdown:
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "LoadBatchIDCount():" & Err.Description, ftDiagSeverityError
End Sub

Private Sub LoadBatchIDs(ByVal RecordCount As Integer)
    On Error GoTo lblError
    
    'Start the process
    BatchIDList.Clear
    BatchIDList.AddItem "Loading..."
    BatchIDList.ListIndex = 0
    DoEvents
            
    'create the connection string to sql server
    Dim ConnString As String
    ConnString = "Provider=sqloledb;" & _
                 "Data Source=APISRBTH01\SQLEXPRESS;" & _
                 "Initial Catalog=BatchHistoryEX;" & _
                 "User ID=sa;" & _
                 "Password=*API.ft!DCS2018;"
            
    'connect to the database
    Dim rs As New ADODB.Recordset
    rs.Open "EXEC [GetUniqueBatchIDs] @RecordCount=" & CStr(RecordCount) & ", @Unit='V104'", _
            ConnString, adOpenStatic, adLockReadOnly
    
    'load the IDs
    BatchIDList.Clear
    
    Do While Not rs.EOF
        'Record Order: [lclTime],[UniqueID],[BatchID], [RecordNo]
        BatchIDList.AddItem FormatBIDLine(rs(0), rs(2))
        rs.MoveNext
    Loop
    
    
lblShutdown:
    'close the database connection
    If Not rs Is Nothing Then
        If rs.State = ObjectStateEnum.adStateOpen Then
            rs.Close
        End If
        Set rs = Nothing
    End If
    
    BatchIDList.ListIndex = 0
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "LoadBatchIDs():" & Err.Description, ftDiagSeverityError
    GoTo lblShutdown
End Sub

Private Function FormatBIDLine(ByVal lclTime As String, ByVal BatchID As String)
    On Error GoTo lblError
    
    FormatBIDLine = BatchID
    
    Dim line As String
    line = BatchID
    
    Do While Len(line) < 16
        line = line & " "
    Loop
    
    line = line & " " & lclTime
    
lblShutdown:
    FormatBIDLine = line
    Exit Function
    
lblError:
    LogDiagnosticsMessage "FormatBIDLine():" & Err.Description, ftDiagSeverityError
    GoTo lblShutdown
End Function

Private Sub GenerateReportBtn_Released()
    On Error GoTo lblError
    
    CreateReportMsg.Visible = True
    DoEvents
    
    Dim BatchID As String
    BatchID = BatchIDList.Text
    BatchID = Mid(BatchID, 1, InStr(BatchID, " ") - 1)
    
    Call GenerateReport(BatchID)
    
lblShutdown:
    CreateReportMsg.Visible = False
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "GenerateReportBtn_Released():" & Err.Description, ftDiagSeverityError
    GoTo lblShutdown
End Sub

Private Sub ClearReport()
    ReportDate.Caption = ""
    BatchNumber.Caption = ""
    StartOperator.Caption = ""
    DMFCharge.Caption = ""
    AACharge.Caption = ""
    IQCharge.Caption = ""
    TPPCharge.Caption = ""
    ChargeTotal.Caption = ""
    AgitationStart.Caption = ""
    AgitationStop.Caption = ""
    TransferStart.Caption = ""
    TransferStop.Caption = ""
    IQSupplier.Caption = ""
    IQLotNum.Caption = ""
    DoEvents
End Sub

Private Sub GenerateReport(ByVal BatchID As String)
    On Error GoTo lblError
    
    'Start the process
    Call ClearReport
    
    'create the connection string to sql server
    Dim ConnString As String
    ConnString = "Provider=sqloledb;" & _
                 "Data Source=APISRBTH01\SQLEXPRESS;" & _
                 "Initial Catalog=BatchHistoryEX;" & _
                 "User ID=sa;" & _
                 "Password=*API.ft!DCS2018;"
    
    'connect to the database
    Dim conn As New ADODB.Connection
    conn.CommandTimeout = 60
    conn.Open ConnString
    
    Dim rs As New ADODB.Recordset
    rs.Open "EXEC [CCABatchReport] @BatchID='" & BatchID & "'", conn, adOpenStatic, adLockReadOnly
    
    
    'load the IDs
    'Field Order: [BatchId], [Desc], [Value]
    Dim Desc As String
    Dim Value As String
    
    Dim CHGTotal As Double
    CHGTotal = 0
    
    Do While Not rs.EOF
        'Record Order: [lclTime],[UniqueID],[BatchID], [RecordNo]
        Desc = UCase(CStr(rs(1)))
        Value = CStr(rs(2))
        
        Select Case Desc
            Case "DATE"
                ReportDate.Caption = Value
                
            Case "BATCHID"
                BatchNumber.Caption = Value
            
            Case "OPERATOR"
                StartOperator.Caption = Value
            
            Case "DD_CHG"
                DMFCharge.Caption = FormatNumber(Value, 1, vbTrue, vbFalse, vbTrue)
                CHGTotal = CHGTotal + CDbl(FormatNumber(Value, 1, vbTrue, vbFalse, vbTrue))
                
            Case "AA_CHG"
                AACharge.Caption = FormatNumber(Value, 1, vbTrue, vbFalse, vbTrue)
                CHGTotal = CHGTotal + CDbl(FormatNumber(Value, 1, vbTrue, vbFalse, vbTrue))
                
            Case "IQ_CHG"
                IQCharge.Caption = FormatNumber(Value, 1, vbTrue, vbFalse, vbTrue)
                CHGTotal = CHGTotal + CDbl(FormatNumber(Value, 1, vbTrue, vbFalse, vbTrue))
                
            Case "TPP_CHG"
                TPPCharge.Caption = FormatNumber(Value, 1, vbTrue, vbFalse, vbTrue)
                CHGTotal = CHGTotal + CDbl(FormatNumber(Value, 1, vbTrue, vbFalse, vbTrue))
                
            Case "TPP_LOTNUM"
                TPPLotNum.Caption = Value
            
            Case "CCA_TOTAL"
                'ChargeTotal.Caption = FormatNumber(Value, 1, vbTrue, vbFalse, vbTrue)
            
            Case "AGITATE_START"
                AgitationStart.Caption = Value
            
            Case "AGITATE_END"
                AgitationStop.Caption = Value
            
            Case "TRANSFER_START"
                TransferStart.Caption = Value
            
            Case "TRANSFER_END"
                TransferStop.Caption = Value
            
            Case "IQ_LOTNUM"
                IQLotNum.Caption = Value
            
            Case "IQ_SUPPLIER"
                IQSupplier.Caption = Value
            
        End Select
        
        rs.MoveNext
    Loop
    
    ChargeTotal.Caption = CStr(CHGTotal)
    
lblShutdown:
    'close the database connection
    If Not rs Is Nothing Then
        If rs.State = ObjectStateEnum.adStateOpen Then
            rs.Close
        End If
        Set rs = Nothing
    End If
    
    If Not conn Is Nothing Then
        If conn.State = ObjectStateEnum.adStateOpen Then
            conn.Close
        End If
        Set conn = Nothing
    End If
    
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "GenerateReport():" & Err.Description, ftDiagSeverityError
    GoTo lblShutdown
End Sub

Private Sub GetBatchIDs_Released()
    On Error GoTo lblError

    Dim CBCount As String
    CBCount = Me.BatchIDCount.Text
    
    Dim RecordCount As Integer
    RecordCount = 10
    
    Select Case CBCount
        Case "Most Recent 10"
            RecordCount = 10
        Case "Most Recent 25"
            RecordCount = 25
        Case "Most Recent 50"
            RecordCount = 50
        Case "Most Recent 100"
            RecordCount = 100
        Case "Most Recent 250"
            RecordCount = 250
        Case "Most Recent 1000"
            RecordCount = 1000
        Case "All"
            RecordCount = -1
    End Select
    
    Call LoadBatchIDs(RecordCount)
    
lblShutdown:
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "GetBatchIDs_Released():" & Err.Description, ftDiagSeverityError
End Sub

Option Explicit

Private VBADS As Display
Private ConnString As String

Private Sub VBAStartup()
    On Error GoTo lblError
    Set VBADS = LoadedDisplays.Item("VBAGlobal")
    VBADS.StartUpActions Me.Name
    Exit Sub
lblError:
    LogDiagnosticsMessage "VBAStartup():" & Err.Description, ftDiagSeverityError
End Sub

Private Sub Display_Load()
    On Error GoTo lblError
    Call VBAStartup
    
    'Set the SQL Server connection string
    ConnString = "Provider=sqloledb;" & _
                 "Data Source=APISRBTH01\SQLEXPRESS;" & _
                 "Initial Catalog=BatchHistoryEX;" & _
                 "User ID=sa;" & _
                 "Password=*API.ft!DCS2018;"
    
    Call LoadBatchIDCount
    Call LoadBatchIDs(10)
        
lblShutdown:
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "Display_Load():" & Err.Description, ftDiagSeverityError
End Sub

Private Sub LoadBatchIDCount()
    On Error GoTo lblError
    
    With BatchIDCount
        .Clear
        
        .AddItem "Most Recent 10"
        .AddItem "Most Recent 25"
        .AddItem "Most Recent 50"
        .AddItem "Most Recent 100"
        .AddItem "Most Recent 250"
        .AddItem "Most Recent 1000"
        .AddItem "All"
        
        .ListIndex = 0
    End With
        
lblShutdown:
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "LoadBatchIDCount():" & Err.Description, ftDiagSeverityError
End Sub

Private Sub GenerateReportBtn_Released()
    On Error GoTo lblError
    
    CreateReportMsg.Visible = True
    DoEvents
    
    Dim BatchID As String
    BatchID = BatchIDList.Text
    BatchID = Mid(BatchID, 1, InStr(BatchID, " ") - 1)
    
    Call GenerateReport(BatchID)
    
lblShutdown:
    CreateReportMsg.Visible = False
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "GenerateReportBtn_Released():" & Err.Description, ftDiagSeverityError
    GoTo lblShutdown
End Sub

Private Sub GenerateReport(ByVal BatchID As String)
    On Error GoTo lblError
    
    'Start the process
    ReportDate.Caption = ""
    ReportBatchNumber.Caption = ""
    ReportStartOperator.Caption = ""
    ReportVesselInv.Caption = ""
    ReportDMFCharge.Caption = ""
    ReportAACharge.Caption = ""
    ReportTotal.Caption = ""
    ReportAgStart.Caption = ""
    ReportAgStop.Caption = ""
    ReportPMDALot.Caption = ""
    ReportPMDASupplier.Caption = ""
    DoEvents
    
    'Set the SQL Server connection string
    ConnString = "Provider=sqloledb;" & _
                 "Data Source=APISRBTH01\SQLEXPRESS;" & _
                 "Initial Catalog=BatchHistoryEX;" & _
                 "User ID=sa;" & _
                 "Password=*API.ft!DCS2018;"
    
    
    'connect to the database
    Dim conn As New ADODB.Connection
    conn.CommandTimeout = 60
    conn.Open ConnString
    
    Dim rs As New ADODB.Recordset
    rs.Open "EXEC [LMMBatchReport] @BatchID='" & BatchID & "'", conn, adOpenStatic, adLockReadOnly
    
    'load the IDs
    'Field Order: [BatchId], [Desc], [Value]
    Dim Desc As String
    Dim Value As String
    
    Dim CHGTotal As Double
    CHGTotal = 0
    
    Do While Not rs.EOF
        'Record Order: [lclTime],[UniqueID],[BatchID], [RecordNo]
        Desc = UCase(CStr(rs(1)))
        Value = CStr(rs(2))
        
        Select Case Desc
            Case "DATE"
                ReportDate.Caption = Value
                
            Case "BATCHID"
                ReportBatchNumber.Caption = Value
            
            Case "OPERATOR"
                ReportStartOperator.Caption = Value
            
            Case "VESSEL_INV"
                ReportVesselInv.Caption = Value
                
            Case "DD_CHG"
                If IsNumeric(Value) Then
                    CHGTotal = CHGTotal + CDbl(Value)
                    ReportDMFCharge.Caption = FormatNumber(Value, 1, vbTrue, vbFalse, vbTrue)
                Else
                    ReportDMFCharge.Caption = Value
                End If
            
            Case "PMDA_CHG"
                If IsNumeric(Value) Then
                    CHGTotal = CHGTotal + CDbl(Value)
                    ReportAACharge.Caption = FormatNumber(Value, 1, vbTrue, vbFalse, vbTrue)
                Else
                    ReportAACharge.Caption = Value
                End If
            
            Case "AGITATE_START"
                ReportAgStart.Caption = Value
            
            Case "AGITATE_END"
                ReportAgStop.Caption = Value
            
            Case "PMDA_LOTNUM"
                ReportPMDALot.Caption = Value
            
            Case "PMDA_SUPPLIER"
                ReportPMDASupplier.Caption = Value
            
        End Select
        
        rs.MoveNext
    Loop
    
    ReportTotal.Caption = FormatNumber(CHGTotal, 1, vbTrue, vbFalse, vbTrue)
    
    
lblShutdown:
    'close the database connection
    If Not rs Is Nothing Then
        If rs.State = ObjectStateEnum.adStateOpen Then
            rs.Close
        End If
        Set rs = Nothing
    End If
    
    If Not conn Is Nothing Then
        If conn.State = ObjectStateEnum.adStateOpen Then
            conn.Close
        End If
        Set conn = Nothing
    End If
    
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "GenerateReport():" & Err.Description, ftDiagSeverityError
    GoTo lblShutdown
End Sub

Private Sub LoadBatchIDs(ByVal RecordCount As Integer)
    On Error GoTo lblError
    
    'Start the process
    BatchIDList.Clear
    BatchIDList.AddItem "Loading..."
    BatchIDList.ListIndex = 0
    DoEvents
    
    'connect to the database
    Dim rs As New ADODB.Recordset
    rs.Open "EXEC [GetUniqueBatchIDs] @RecordCount=" & CStr(RecordCount) & ", @Unit='V111'", _
            ConnString, adOpenStatic, adLockReadOnly
    
    'load the IDs
    BatchIDList.Clear
    
    Do While Not rs.EOF
        'Record Order: [lclTime],[UniqueID],[BatchID], [RecordNo], [Recipe]
        If InStr(CStr(rs(4)), "V111_CHG_") < 1 Then _
            BatchIDList.AddItem FormatBIDLine(rs(0), rs(2))
        rs.MoveNext
    Loop
    
    
lblShutdown:
    'close the database connection
    If Not rs Is Nothing Then
        If rs.State = ObjectStateEnum.adStateOpen Then
            rs.Close
        End If
        Set rs = Nothing
    End If
    
    BatchIDList.ListIndex = 0
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "LoadBatchIDs():" & Err.Description, ftDiagSeverityError
    GoTo lblShutdown
End Sub

Private Sub GetBatchIDs_Released()
    On Error GoTo lblError

    Dim CBCount As String
    CBCount = Me.BatchIDCount.Text
    
    Dim RecordCount As Integer
    RecordCount = 10
    
    Select Case CBCount
        Case "Most Recent 10"
            RecordCount = 10
        Case "Most Recent 25"
            RecordCount = 25
        Case "Most Recent 50"
            RecordCount = 50
        Case "Most Recent 100"
            RecordCount = 100
        Case "Most Recent 250"
            RecordCount = 250
        Case "Most Recent 1000"
            RecordCount = 1000
        Case "All"
            RecordCount = -1
    End Select
    
    Call LoadBatchIDs(RecordCount)
    
lblShutdown:
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "GetBatchIDs_Released():" & Err.Description, ftDiagSeverityError
End Sub

Private Function FormatBIDLine(ByVal lclTime As String, ByVal BatchID As String)
    On Error GoTo lblError
    
    FormatBIDLine = BatchID
    
    Dim line As String
    line = BatchID
    
    Do While Len(line) < 16
        line = line & " "
    Loop
    
    line = line & " " & lclTime
    
lblShutdown:
    FormatBIDLine = line
    Exit Function
    
lblError:
    LogDiagnosticsMessage "FormatBIDLine():" & Err.Description, ftDiagSeverityError
    GoTo lblShutdown
End Function


Option Explicit

Private VBADS As Display
Private ConnString As String

Private FieldVal(115, 2) As String

Private Sub VBAStartup()
    On Error GoTo lblError
    Set VBADS = LoadedDisplays.Item("VBAGlobal")
    VBADS.StartUpActions Me.Name
    Exit Sub
lblError:
    LogDiagnosticsMessage "VBAStartup():" & Err.Description, ftDiagSeverityError
End Sub

Private Sub BatchIDList_Change()

End Sub

Private Sub Display_Load()
    On Error GoTo lblError
    Call VBAStartup
    
    'Set the SQL Server connection string
    ConnString = "Provider=sqloledb;" & _
                 "Data Source=APISRBTH01\SQLEXPRESS;" & _
                 "Initial Catalog=BatchHistoryEX;" & _
                 "User ID=sa;" & _
                 "Password=*API.ft!DCS2018;"
                 
    Call LoadFieldVals
    Call LoadBatchIDCount
    Call LoadBatchIDs(10)
    Call ClearReport
        
lblShutdown:
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "Display_Load():" & Err.Description, ftDiagSeverityError
End Sub

Private Sub LoadBatchIDCount()
    On Error GoTo lblError
    
    With BatchIDCount
        .Clear
        
        .AddItem "Most Recent 10"
        .AddItem "Most Recent 25"
        .AddItem "Most Recent 50"
        .AddItem "Most Recent 100"
        .AddItem "Most Recent 250"
        .AddItem "Most Recent 1000"
        .AddItem "All"
        
        .ListIndex = 0
    End With
        
lblShutdown:
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "LoadBatchIDCount():" & Err.Description, ftDiagSeverityError
End Sub

Private Sub GetBatchIDs_Released()
    On Error GoTo lblError

    Dim CBCount As String
    CBCount = Me.BatchIDCount.Text
    
    Dim RecordCount As Integer
    RecordCount = 10
    
    Select Case CBCount
        Case "Most Recent 10"
            RecordCount = 10
        Case "Most Recent 25"
            RecordCount = 25
        Case "Most Recent 50"
            RecordCount = 50
        Case "Most Recent 100"
            RecordCount = 100
        Case "Most Recent 250"
            RecordCount = 250
        Case "Most Recent 1000"
            RecordCount = 1000
        Case "All"
            RecordCount = -1
    End Select
    
    Call LoadBatchIDs(RecordCount)
    
lblShutdown:
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "GetBatchIDs_Released():" & Err.Description, ftDiagSeverityError
End Sub

Private Sub LoadBatchIDs(ByVal RecordCount As Integer)
    On Error GoTo lblError
    
    'Start the process
    BatchIDList.Clear
    BatchIDList.AddItem "Loading..."
    BatchIDList.ListIndex = 0
    DoEvents
            
    'connect to the database
    Dim rs As New ADODB.Recordset
    rs.Open "EXEC [GetUniqueBatchIDs] @RecordCount=" & CStr(RecordCount) & ", @Unit='R101'", _
            ConnString, adOpenStatic, adLockReadOnly
    
    'load the IDs
    BatchIDList.Clear
    
    Do While Not rs.EOF
        'Record Order: [lclTime],[UniqueID],[BatchID], [RecordNo]
        BatchIDList.AddItem FormatBIDLine(rs(0), rs(2))
        rs.MoveNext
    Loop
    
    
lblShutdown:
    'close the database connection
    If Not rs Is Nothing Then
        If rs.State = ObjectStateEnum.adStateOpen Then
            rs.Close
        End If
        Set rs = Nothing
    End If
    
    BatchIDList.ListIndex = 0
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "LoadBatchIDs():" & Err.Description, ftDiagSeverityError
    GoTo lblShutdown
End Sub

Private Function FormatBIDLine(ByVal lclTime As String, ByVal BatchID As String)
    On Error GoTo lblError
    
    FormatBIDLine = BatchID
    
    Dim line As String
    line = BatchID
    
    Do While Len(line) < 16
        line = line & " "
    Loop
    
    line = line & " " & lclTime
    
lblShutdown:
    FormatBIDLine = line
    Exit Function
    
lblError:
    LogDiagnosticsMessage "FormatBIDLine():" & Err.Description, ftDiagSeverityError
    GoTo lblShutdown
End Function

Private Sub GenerateReportBtn_Released()
    On Error GoTo lblError
    
    If (BatchIDList.Text = "Loading...") Or CreateReportMsg.Visible Then _
        Exit Sub
    
    CreateReportMsg.Caption = "Creating Report..."
    CreateReportMsg.Visible = True
    
    Dim BatchID As String
    BatchID = BatchIDList.Text
    BatchID = Mid(BatchID, 1, InStr(BatchID, " ") - 1)
    DoEvents
    
    Call GenerateReport(BatchID)
    
lblShutdown:
    CreateReportMsg.Visible = False
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "GenerateReportBtn_Released():" & Err.Description, ftDiagSeverityError
    GoTo lblShutdown
End Sub

Private Sub ClearReport()
    On Error GoTo lblError
    
    Dim x As Integer
    
    'LogDiagnosticsMessage "Start: " & CStr(Now), ftDiagSeverityInfo
        
    For x = 1 To 115
        If Len(FieldVal(x, 1)) > 0 Then
            Me.Elements.Item(FieldVal(x, 1)).Caption = ""
        End If
    Next
    
    'LogDiagnosticsMessage "Finish: " & CStr(Now), ftDiagSeverityInfo
    
    'DoEvents
    Exit Sub
    
lblError:
    LogDiagnosticsMessage "ClearReport():" & Err.Description, ftDiagSeverityWarning
    Resume Next
End Sub

Private Sub GenerateReport(ByVal BatchID As String)
    On Error GoTo lblError
        
    Dim x As Integer
    Dim ErrorMsg As String
    Dim e As Integer
            
    'Start the process
    Call ClearReport
        
    'connect to the database
    Dim conn As New ADODB.Connection
    conn.CommandTimeout = 90
    conn.Open ConnString
    
    Dim rs As New ADODB.Recordset
    rs.Open "EXEC [PolyReportQuick2] @BatchID='" & BatchID & "'", conn, adOpenStatic, adLockReadOnly
    
    'load the IDs
    'Field Order: [BatchId], [Desc], [Value]
    Dim Desc As String
    Dim Value As String
    Dim FieldEl As Element
    Dim FieldName As String
    
    Dim DecPrecision As Long
    
    Do While Not rs.EOF
        'Field Order: [BatchId], [Desc], [Value]
        Desc = UCase(CStr(rs(1)))
        If Len(rs(2)) > 0 Then
            Value = CStr(rs(2))
        Else
            Value = ""
        End If
        
        For x = 1 To 115
            If Desc = UCase(FieldVal(x, 2)) Then
                FieldName = FieldVal(x, 1)
            
                If Len(Value) = 0 Then
                    Me.Elements.Item(FieldName).Caption = ""
                    
                ElseIf IsNumeric(Value) And InStr(Value, ".") > 0 Then
                    DecPrecision = 1
                    
                    'For Kilowatt values, we want 2 decimal places
                    If FieldName = "R101KW" Or _
                       FieldName = "FirstLMMChrgStartKW" Or _
                       FieldName = "FirstLMMChrgEndKW" Or _
                       FieldName = "SecondLMMChrgStartKW" Or _
                       FieldName = "SecondLMMChrgEndKW" Or _
                       FieldName = "ThirdLMMChrgStartKW" Or _
                       FieldName = "ThirdLMMChrgEndKW" Or _
                       FieldName = "VVSampleKW" Or _
                       FieldName = "FillerCaWeightTxt" Then
                        DecPrecision = 2
                    End If

                    Me.Elements.Item(FieldName).Caption = FormatNumber(Value, DecPrecision, vbTrue, vbFalse, vbTrue)
                    
                'If the value looks like a date and it's one of the date or time fields.
                ElseIf IsDate(Value) And (InStr(FieldName, "Date") > 0 Or InStr(FieldName, "Time") > 0) Then
                    Me.Elements.Item(FieldName).Caption = Format(Value, "Short Date") & " " & Format(Value, "Short Time")
                    
                Else
                    Me.Elements.Item(FieldName).Caption = Value
                    
                End If
                
                Exit For
            End If
        Next
        
        rs.MoveNext
    Loop
    
    
lblShutdown:
    'close the database connection
    If Not rs Is Nothing Then
        If rs.State = ObjectStateEnum.adStateOpen Then
            rs.Close
        End If
        Set rs = Nothing
    End If
    
    If Not conn Is Nothing Then
        If conn.State = ObjectStateEnum.adStateOpen Then
            conn.Close
        End If
        Set conn = Nothing
    End If
    
    Exit Sub
    
lblError:
    ErrorMsg = Err.Description
    
    If conn.Errors.Count > 0 Then
        For e = 0 To conn.Errors.Count - 1
            ErrorMsg = ErrorMsg & " | " & conn.Errors.Item(e).Description
        Next
    End If
    
    LogDiagnosticsMessage "GenerateReport():" & ErrorMsg, ftDiagSeverityError
    GoTo lblShutdown
End Sub

Private Sub AddFieldValItem(ByVal Index As Integer, ByVal ObjectName As String, ByVal DBDesc As String)
    FieldVal(Index, 1) = ObjectName
    FieldVal(Index, 2) = DBDesc
End Sub

Private Sub LoadFieldVals()
    'Prepoly half - R101
    Call AddFieldValItem(1, "ODALotNum1", "ODA_LOT_01")
    Call AddFieldValItem(2, "ODASupplier1", "ODA_SUPPLIER_01")
    Call AddFieldValItem(3, "ODAAmount1", "ODA_AMOUNT_01")
    Call AddFieldValItem(4, "ODALotNum2", "ODA_LOT_02")
    Call AddFieldValItem(5, "ODASupplier2", "ODA_SUPPLIER_02")
    Call AddFieldValItem(6, "ODAAmount2", "ODA_AMOUNT_02")
    Call AddFieldValItem(10, "ODAAmountTotal", "ODA_AMOUNT_TOTAL")
    Call AddFieldValItem(37, "R101ODAStart", "R101_ODA_START")
    Call AddFieldValItem(38, "R101ODAStop", "R101_ODA_END")
    
    Call AddFieldValItem(11, "PMDALotNum1", "PMDA_LOT_01")
    Call AddFieldValItem(12, "PMDASupplier1", "PMDA_SUPPLIER_01")
    Call AddFieldValItem(13, "PMDAAmount1", "PMDA_AMOUNT_01")
    Call AddFieldValItem(14, "PMDALotNum2", "PMDA_LOT_02")
    Call AddFieldValItem(15, "PMDASupplier2", "PMDA_SUPPLIER_02")
    Call AddFieldValItem(16, "PMDAAmount2", "PMDA_AMOUNT_02")
    Call AddFieldValItem(20, "PMDAAmountTotal", "PMDA_AMOUNT_TOTAL")
    Call AddFieldValItem(39, "R101PMDAStart", "R101_PMDA_START")
    Call AddFieldValItem(40, "R101PMDAStop", "R101_PMDA_END")
    
    Call AddFieldValItem(7, "BPDALotNum1", "BPDA_LOT_01")
    Call AddFieldValItem(8, "BPDASupplier1", "BPDA_SUPPLIER_01")
    Call AddFieldValItem(9, "BPDAAmount1", "BPDA_AMOUNT_01")
    Call AddFieldValItem(17, "BPDALotNum2", "BPDA_LOT_02")
    Call AddFieldValItem(18, "BPDASupplier2", "BPDA_SUPPLIER_02")
    Call AddFieldValItem(19, "BPDAAmount2", "BPDA_AMOUNT_02")
    Call AddFieldValItem(89, "BPDAAmountTotal", "BPDA_AMOUNT_TOTAL")
    Call AddFieldValItem(104, "R101BPDAStart", "R101_BPDA_START")
    Call AddFieldValItem(105, "R101BPDAStop", "R101_BPDA_END")
    
    Call AddFieldValItem(90, "BTDALotNum1", "BTDA_LOT_01")
    Call AddFieldValItem(91, "BTDASupplier1", "BTDA_SUPPLIER_01")
    Call AddFieldValItem(92, "BTDAAmount1", "BTDA_AMOUNT_01")
    Call AddFieldValItem(93, "BTDALotNum2", "BTDA_LOT_02")
    Call AddFieldValItem(94, "BTDASupplier2", "BTDA_SUPPLIER_02")
    Call AddFieldValItem(95, "BTDAAmount2", "BTDA_AMOUNT_02")
    Call AddFieldValItem(96, "BTDAAmountTotal", "BTDA_AMOUNT_TOTAL")
    Call AddFieldValItem(106, "R101BTDAStart", "R101_BTDA_START")
    Call AddFieldValItem(107, "R101BTDAStop", "R101_BTDA_END")
    
    Call AddFieldValItem(97, "PDALotNum1", "PDA_LOT_01")
    Call AddFieldValItem(98, "PDASupplier1", "PDA_SUPPLIER_01")
    Call AddFieldValItem(99, "PDAAmount1", "PDA_AMOUNT_01")
    Call AddFieldValItem(100, "PDALotNum2", "PDA_LOT_02")
    Call AddFieldValItem(101, "PDASupplier2", "PDA_SUPPLIER_02")
    Call AddFieldValItem(102, "PDAAmount2", "PDA_AMOUNT_02")
    Call AddFieldValItem(103, "PDAAmountTotal", "PDA_AMOUNT_TOTAL")
    Call AddFieldValItem(108, "R101PDAStart", "R101_PDA_START")
    Call AddFieldValItem(109, "R101PDAStop", "R101_PDA_END")
    
    Call AddFieldValItem(21, "FillerBatchDateTxt", "FILLER_DATE")
    Call AddFieldValItem(22, "FillerBatchTxt", "FILLER_BATCH_NUMBER")
    Call AddFieldValItem(23, "FillerOperatorTxt", "FILLER_OPERATOR")
    Call AddFieldValItem(24, "FillerCaLotTxt", "FILLER_CAP04_LOT")
    Call AddFieldValItem(25, "FillerCaWeightTxt", "FILLER_CAP04_ADDED")
    Call AddFieldValItem(26, "FillerDMFAddTxt", "FILLER_DD_ADDED")
    Call AddFieldValItem(27, "FillerLFFTotalTxt", "FILLER_LFF_TOTAL")
    Call AddFieldValItem(28, "LMMLotNum", "LMM_BATCHID")
    Call AddFieldValItem(29, "R101Date", "R101_DATE")
    Call AddFieldValItem(30, "R101LotNum", "R101_LOT_NUMBER")
    Call AddFieldValItem(31, "R101BatchNum", "R101_BATCH_NUMBER")
    Call AddFieldValItem(32, "R101Operator", "R101_OPERATOR")
    Call AddFieldValItem(33, "R101DMFStart", "R101_DMF_START")
    Call AddFieldValItem(34, "R101DMFStop", "R101_DMF_END")
    Call AddFieldValItem(35, "R101DMFAdd", "R101_DMF_AMOUNT")
    Call AddFieldValItem(36, "R101Level", "R101_LEVEL")
    Call AddFieldValItem(41, "R101KW", "R101_SAMPLE_KW")
    Call AddFieldValItem(42, "PVVPoise", "R101_SAMPLE_POISE")
    Call AddFieldValItem(43, "PVVWaste", "R101_SAMPLE_WASTE")
    Call AddFieldValItem(44, "R101Viscosity", "R101_VISCOSITY")
    Call AddFieldValItem(45, "FilterInletPress", "R101_FILTER_IN_PRESS")
    Call AddFieldValItem(46, "FilterOutletPress", "R101_FILTER_OUT_PRESS")
    Call AddFieldValItem(47, "FilterDP", "R101_FILTER_DP")
    
    'Poly half - R102
    Call AddFieldValItem(48, "LFFDMFFlush", "LFFDMFFlush")
    Call AddFieldValItem(49, "LFFFilterPass", "LFFFilterPass")
    Call AddFieldValItem(50, "R102Level", "R102Level")
    Call AddFieldValItem(51, "FirstLMMChargeStart", "FirstLMMChargeStart")
    Call AddFieldValItem(52, "FirstLMMChrgStartKW", "FirstLMMChrgStartKW")
    Call AddFieldValItem(53, "FirstLMMChrgEndTime", "FirstLMMChrgEndTime")
    Call AddFieldValItem(54, "FirstLMMChrgEndKW", "FirstLMMChrgEndKW")
    Call AddFieldValItem(55, "FirstLMMChargePounds", "FirstLMMChargePounds")
    Call AddFieldValItem(56, "PreVVSample", "PreVVSample")
    Call AddFieldValItem(57, "SecondLMMChrgStart", "SecondLMMChrgStart")
    Call AddFieldValItem(58, "SecondLMMChrgStartKW", "SecondLMMChrgStartKW")
    Call AddFieldValItem(59, "SecondLMMChrgEndTime", "SecondLMMChrgEndTime")
    Call AddFieldValItem(60, "SecondLMMChrgEndKW", "SecondLMMChrgEndKW")
    Call AddFieldValItem(61, "SecondLMMChrgPounds", "SecondLMMChrgPounds")
    Call AddFieldValItem(62, "ThirdLMMChrgStartTime", "ThirdLMMChrgStartTime")
    Call AddFieldValItem(63, "ThirdLMMChrgStartKW", "ThirdLMMChrgStartKW")
    Call AddFieldValItem(64, "ThirdLMMChrgEndTime", "ThirdLMMChrgEndTime")
    Call AddFieldValItem(65, "ThirdLMMChrgEndKW", "ThirdLMMChrgEndKW")
    Call AddFieldValItem(66, "ThirdLMMChrgPounds", "ThirdLMMChrgPounds")
    Call AddFieldValItem(67, "ManualLMMChrg", "ManualLMMChrg")
    Call AddFieldValItem(68, "TotalLMMCharge", "TotalLMMCharge")
    Call AddFieldValItem(88, "TotalLMMPMDA", "TotalLMMPMDA")
    Call AddFieldValItem(69, "VVSampleKW", "VVSampleKW")
    Call AddFieldValItem(70, "VVSamplePoise", "VVSamplePoise")
    Call AddFieldValItem(71, "VVSampleWaste", "VVSampleWaste")
    Call AddFieldValItem(72, "DefaomStartTime", "DefaomStartTime")
    Call AddFieldValItem(73, "DefoamEndTime", "DefoamEndTime")
    Call AddFieldValItem(74, "FinishOperator", "FinishOperator")
    Call AddFieldValItem(75, "VVTransStart", "VVTransStart")
    Call AddFieldValItem(76, "VVTransFinish", "VVTransFinish")
    Call AddFieldValItem(77, "V7114StartLevel", "V7114StartLevel")
    Call AddFieldValItem(78, "V7114FinishLevel", "V7114FinishLevel")
    Call AddFieldValItem(79, "VVTransferDate", "VVTransferDate")
    Call AddFieldValItem(80, "PrepolymerTotal", "PrepolymerTotal")
    Call AddFieldValItem(81, "PVVWasteTotal", "PVVWasteTotal")
    Call AddFieldValItem(82, "VarnishTotal", "VarnishTotal")
    Call AddFieldValItem(83, "VVWasteTotal", "VVWasteTotal")
    Call AddFieldValItem(84, "TotalWasteTotal", "TotalWasteTotal")
    Call AddFieldValItem(85, "BatchTotal", "BatchTotal")
    Call AddFieldValItem(86, "LostVVBatch", "LostVVBatch")
    Call AddFieldValItem(87, "TotalDMF", "TotalDMF")
    
    
    Call AddFieldValItem(110, "", "")
    Call AddFieldValItem(111, "", "")
    Call AddFieldValItem(112, "", "")
    Call AddFieldValItem(113, "", "")
    Call AddFieldValItem(114, "", "")
    Call AddFieldValItem(115, "", "")
End Sub