View Single Post
  #5 (permalink)  
Old February 15th, 2005, 10:29 PM
Mitch Mitch is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

99% there! :)

I wrote the code below and it works great, except for when the data sets are in reverse order; i.e. Transformer 2 comes before Transformer 1. Not sure how to handle that one. I thought about checking that the two substation fields were the same (at least maybe the first 4 or 5 characters, since the ending numbers--suffix--probably won't match) but don't know how to code for that.

Code:
Private Sub btnImportData_Click()
On Error GoTo Err_btnImportData_Click

DoCmd.Hourglass True

'**Reference DAO 3.5
'**create two tables

Dim recordset1_AllData As Recordset 'recordset1  Sheet1
Dim recordset2_Parent As Recordset 'recordset2  T_Parent
Dim recordset3_Child As Recordset 'recordset3   T_Child
Dim recordset4_Location As Recordset
Dim lID As Long
Dim lLocationGroup As Long
Dim strLocation As String
Dim lLocationID As Long
Dim strCurrentSubstation As String
Dim strComonSubstation As String

'**Iterate through the dataset

'**Set recordset1_AllData = CurrentDb.OpenRecordset("Sheet1")
Set recordset1_AllData = CurrentDb.OpenRecordset("DATA")
Set recordset2_Parent = CurrentDb.OpenRecordset("T_Parent")
Set recordset3_Child = CurrentDb.OpenRecordset("T_Child")
Set recordset4_Location = CurrentDb.OpenRecordset("T_Locations")    'with an S

Do Until recordset1_AllData.EOF
    If IsNull(recordset1_AllData.Fields("RECLOSER #")) Then
        DoCmd.Hourglass False
        MsgBox "End of File"   'Null Found
        Exit Sub
        'recordset1_AllData.MoveNext
        'Debug.Print recordset1_AllData.Fields("RECLOSER #")
    End If
    If Not IsNumeric(recordset1_AllData.Fields("RECLOSER #")) Then
        sRecloser = recordset1_AllData.Fields("RECLOSER #")
        lID = recordset1_AllData.Fields("SORT AID")
 '**Need to figure out how to handle if these are in reverse order probaly around here.   
        If InStr(sRecloser, "Transformer DATA") > 0 Then  'This is to get the pair of transformers the same ID to link with.
            lLocationGroup = lID
        ElseIf InStr(sRecloser, "Transformer 1 DATA") > 0 Then
            lLocationGroup = lID
        End If

'**Begin Find location.
strCurrentSubstation = recordset1_AllData.Fields("SUBSTATION")
Do Until recordset4_Location.EOF
    strComonSubstation = recordset4_Location.Fields("SUBSTATION")
    If InStr(strCurrentSubstation, strComonSubstation) > 0 Then  'This is to get the location to link with.
        lLocationID = recordset4_Location.Fields("LocationID")
        recordset4_Location.MoveFirst   'Otherwise we stop at the end and all the rest will get this last number
        Exit Do
    End If
recordset4_Location.MoveNext
Loop
'**End Find Location

'**Begin fix CRLF
x = recordset1_AllData.Fields("TYPE_RATING")
If IsNull(x) Then
    t = "None Specified"
Else
    t = IIf(InStr(x, Chr(10)), Replace(x, Chr(10), " "), x)
End If
'**End fix CRLF

        With recordset2_Parent
            .AddNew
            .[ID] = lID
            .[SORT AID] = lLocationGroup     'recordset1_AllData.Fields("SORT AID")
            .[SUBSTATION] = lLocationID     'recordset1_AllData.Fields("SUBSTATION")
            .[RECLOSER #] = Trim(recordset1_AllData.Fields("RECLOSER #"))   'trimmed to take away the leading space in some cases
            .[TYPE_RATING] = t 'Change In Imported Data
            .[Date] = recordset1_AllData.Fields("DATE")
            .[OUTSIDE TEMP] = recordset1_AllData.Fields("OUTSIDE TEMP") 'Change In Imported Data
            .[TRANSFORMER OIL TEMP] = recordset1_AllData.Fields("TRANSFORMER OIL TEMP")
            .[TRANSFORMER WINDING TEMP] = recordset1_AllData.Fields("TRANSFORMER WINDING TEMP")
            .[TRANSFORMER PRESSURE] = recordset1_AllData.Fields("TRANSFORMER PRESSURE")
            .[REGULATOR 1  ACTUAL] = recordset1_AllData.Fields("REGULATOR 1  ACTUAL")
            .[REGULATOR 1  RAISE] = recordset1_AllData.Fields("REGULATOR 1  RAISE")
            .[REGULATOR 1  LOWER] = recordset1_AllData.Fields("REGULATOR 1  LOWER")
            .[REGULATOR 1  COUNTER] = recordset1_AllData.Fields("REGULATOR 1  COUNTER")
            .[REG 1 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 1 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION] = recordset1_AllData.Fields("DESIGN OPERATION")
            .[PERCENT REMAINING] = recordset1_AllData.Fields("PERCENT REMAINING")
            .[REGULATOR 2  ACTUAL] = recordset1_AllData.Fields("REGULATOR 2  ACTUAL")
            .[REGULATOR 2  RAISE] = recordset1_AllData.Fields("REGULATOR 2  RAISE")
            .[REGULATOR 2  LOWER] = recordset1_AllData.Fields("REGULATOR 2  LOWER")
            .[REGULATOR 2  COUNTER] = recordset1_AllData.Fields("REGULATOR 2  COUNTER")
            .[REG 2 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 2 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION1] = recordset1_AllData.Fields("DESIGN OPERATION1")   'Change In Imported Data
            .[PERCENT REMAINING1] = recordset1_AllData.Fields("PERCENT REMAINING1") 'Change In Imported Data
            .[REGULATOR 3  ACTUAL] = recordset1_AllData.Fields("REGULATOR 3  ACTUAL")
            .[REGULATOR 3  RAISE] = recordset1_AllData.Fields("REGULATOR 3  RAISE")
            .[REGULATOR 3  LOWER] = recordset1_AllData.Fields("REGULATOR 3  LOWER")
            .[REGULATOR 3  COUNTER] = recordset1_AllData.Fields("REGULATOR 3  COUNTER")
            .[REG 3 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 3 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION2] = recordset1_AllData.Fields("DESIGN OPERATION2")   'Change In Imported Data
            .[PERCENT REMAINING2] = recordset1_AllData.Fields("PERCENT REMAINING2") 'Change In Imported Data
            .[As Found AMPS PHASE A] = recordset1_AllData.Fields("As Found AMPS PHASE A")
            .[PEAK AMPS PHASE A] = recordset1_AllData.Fields("PEAK AMPS PHASE A")
            .[As Found AMPS PHASE B] = recordset1_AllData.Fields("As Found AMPS PHASE B")
            .[PEAK AMPS PHASE B] = recordset1_AllData.Fields("PEAK AMPS PHASE B")
            .[As Found AMPS PHASE C] = recordset1_AllData.Fields("As Found AMPS PHASE C")
            .[PEAK AMPS PHASE C] = recordset1_AllData.Fields("PEAK AMPS PHASE C")
            .[TRIP COUNTER] = recordset1_AllData.Fields("TRIP COUNTER")
            .[Neutral 'As Found' Current (calc)] = recordset1_AllData.Fields("Neutral 'As Found' Current (calc)") 'Change In Imported Data
            .[Average Amps (actual)] = recordset1_AllData.Fields("Average Amps (actual)")
            .[Average Amps (demand)] = recordset1_AllData.Fields("Average Amps (demand)")
            .[MVA] = recordset1_AllData.Fields("MVA")
            .[% of  Max Trans MVA] = recordset1_AllData.Fields("% of  Max Trans MVA")
            .[MVA (demand)] = recordset1_AllData.Fields("MVA (demand)")
            .[% of  Max Trans MVA (demand)] = recordset1_AllData.Fields("% of  Max Trans MVA (demand)")
            .[MU#] = recordset1_AllData.Fields("MU#")
            .[SERIAL #] = recordset1_AllData.Fields("SERIAL #")
            .[Capacitors Locations] = recordset1_AllData.Fields("Capacitors Locations")
            '
      End With
        recordset2_Parent.Update
    Else
        '**need to write to table two
        With recordset3_Child
            .AddNew
            .[ID] = lID
            '.[SORT AID] = lLocationGroup     'recordset1_AllData.Fields("SORT AID")
            .[SUBSTATION] = lLocationID     'recordset1_AllData.Fields("SUBSTATION")
            .[RECLOSER #] = Trim(recordset1_AllData.Fields("RECLOSER #"))
            .[TYPE_RATING] = recordset1_AllData.Fields("TYPE_RATING") 'Change In Imported Data
            .[Date] = recordset1_AllData.Fields("DATE")
            .[OUTSIDE TEMP] = recordset1_AllData.Fields("OUTSIDE TEMP") 'Change In Imported Data
            .[TRANSFORMER OIL TEMP] = recordset1_AllData.Fields("TRANSFORMER OIL TEMP")
            .[TRANSFORMER WINDING TEMP] = recordset1_AllData.Fields("TRANSFORMER WINDING TEMP")
            .[TRANSFORMER PRESSURE] = recordset1_AllData.Fields("TRANSFORMER PRESSURE")
            .[REGULATOR 1  ACTUAL] = recordset1_AllData.Fields("REGULATOR 1  ACTUAL")
            .[REGULATOR 1  RAISE] = recordset1_AllData.Fields("REGULATOR 1  RAISE")
            .[REGULATOR 1  LOWER] = recordset1_AllData.Fields("REGULATOR 1  LOWER")
            .[REGULATOR 1  COUNTER] = recordset1_AllData.Fields("REGULATOR 1  COUNTER")
            .[REG 1 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 1 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION] = recordset1_AllData.Fields("DESIGN OPERATION")
            .[PERCENT REMAINING] = recordset1_AllData.Fields("PERCENT REMAINING")
            .[REGULATOR 2  ACTUAL] = recordset1_AllData.Fields("REGULATOR 2  ACTUAL")
            .[REGULATOR 2  RAISE] = recordset1_AllData.Fields("REGULATOR 2  RAISE")
            .[REGULATOR 2  LOWER] = recordset1_AllData.Fields("REGULATOR 2  LOWER")
            .[REGULATOR 2  COUNTER] = recordset1_AllData.Fields("REGULATOR 2  COUNTER")
            .[REG 2 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 2 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION1] = recordset1_AllData.Fields("DESIGN OPERATION1")
            .[PERCENT REMAINING1] = recordset1_AllData.Fields("PERCENT REMAINING1")
            .[REGULATOR 3  ACTUAL] = recordset1_AllData.Fields("REGULATOR 3  ACTUAL")
            .[REGULATOR 3  RAISE] = recordset1_AllData.Fields("REGULATOR 3  RAISE")
            .[REGULATOR 3  LOWER] = recordset1_AllData.Fields("REGULATOR 3  LOWER")
            .[REGULATOR 3  COUNTER] = recordset1_AllData.Fields("REGULATOR 3  COUNTER")
            .[REG 3 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 3 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION2] = recordset1_AllData.Fields("DESIGN OPERATION2")
            .[PERCENT REMAINING2] = recordset1_AllData.Fields("PERCENT REMAINING2")
            .[As Found AMPS PHASE A] = recordset1_AllData.Fields("As Found AMPS PHASE A")
            .[PEAK AMPS PHASE A] = recordset1_AllData.Fields("PEAK AMPS PHASE A")
            .[As Found AMPS PHASE B] = recordset1_AllData.Fields("As Found AMPS PHASE B")
            .[PEAK AMPS PHASE B] = recordset1_AllData.Fields("PEAK AMPS PHASE B")
            .[As Found AMPS PHASE C] = recordset1_AllData.Fields("As Found AMPS PHASE C")
            .[PEAK AMPS PHASE C] = recordset1_AllData.Fields("PEAK AMPS PHASE C")
            .[TRIP COUNTER] = recordset1_AllData.Fields("TRIP COUNTER")
            .[Neutral 'As Found' Current (calc)] = recordset1_AllData.Fields("Neutral 'As Found' Current (calc)")  'Change In Imported Data
            .[Average Amps (actual)] = recordset1_AllData.Fields("Average Amps (actual)")
            .[Average Amps (demand)] = recordset1_AllData.Fields("Average Amps (demand)")
            .[MVA] = recordset1_AllData.Fields("MVA")
            .[% of  Max Trans MVA] = recordset1_AllData.Fields("% of  Max Trans MVA")
            .[MVA (demand)] = recordset1_AllData.Fields("MVA (demand)")
            .[% of  Max Trans MVA (demand)] = recordset1_AllData.Fields("% of  Max Trans MVA (demand)")
            .[MU#] = recordset1_AllData.Fields("MU#")
            .[SERIAL #] = recordset1_AllData.Fields("SERIAL #")
            .[Capacitors Locations] = recordset1_AllData.Fields("Capacitors Locations")

       End With
        recordset3_Child.Update
    End If
    recordset1_AllData.MoveNext
Loop
'**Error trapping

Exit_btnImportData_Click:
    Exit Sub
    DoCmd.Hourglass False
Err_btnImportData_Click:
    DoCmd.Hourglass False
    MsgBox Err.Description

    Resume Exit_btnImportData_Click   
End Sub

I'm almost there, i can't beleive that I even got this far.