Going through recordsets in VBA 1

[perrymans]

I have grown accustomed to writing Excel manipulation to go through an prepare the data for import in Excel columns.

I am now thinking that I might save myself some formatting issues by putting the txt file into an Access table first, then working from there.

I need to know two things to get me started:

1. How do I sequence through a recordset looking for values?

2. How do I write that value into a) the next column and b)another table as I sequence?

I have it for Excel, as shown below (simplified), but I don't know the best way from within Access.

For one line records

    appExcel.Range("A1").Select
    
    'Start search in row 1
    LSearchRow = 1

    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 1
    
    While Len(appExcel.Range("A" & CStr(LSearchRow)).Value) > 0
    
            'Capture the Cell data
            strRowValue = (appExcel.Range("A" & CStr(LSearchRow)).Value)
            'strIdentifyRow = Left(Trim(strRowValue), 10)
            
            'Check for the VMSKey row to write the primary key
            If InStr(1, strRowValue, "STIG ID") >= 1 And InStr(1, strRowValue, ":") Then
                'If value in column A = criteria, copy Value to Column 2
                strPOLinePO = Trim(Mid([strRowValue], (InStr([strRowValue], ":") + 1)))
                strPOLine = strPOLinePO
                appExcel.Range("C" & CStr(LSearchRow)).Select
                appExcel.Range("C" & CStr(LSearchRow)).Value = strPOLine
            Else
                GoTo SkipRow
            End If

SkipRow:
    LSearchRow = LSearchRow + 1
    LCopyToRow = LCopyToRow + 1
    
    Wend

For multiple line records

    appExcel.Range("A1").Select
    
    'Start search in row 1
    LSearchRow = 1

    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 1
    
     While Len(appExcel.Range("A" & CStr(LSearchRow)).Value) > 0
    
            'Capture the Cell data
            strRowValue = (appExcel.Range("A" & CStr(LSearchRow)).Value)
'            strIdentifyRow = Left(Trim(strRowValue), 10)
            
            'Check for the VMSKey row to write the primary key
            If strRowValue Like sDoText Then
                'If value in column A = criteria, copy Value to Column 2
                'Uncomment if it starts on the next row
                'LSearchRow = LSearchRow + 1
                'LCopyToRow = LCopyToRow + 1
                strRowValue = Trim(Mid([strRowValue], (InStr([strRowValue], ":") + 1))) '(appExcel.Range("A" & CStr(LCopyToRow)).Value)
                'Advance one more row for advance look into end of section
                LSearchRow = LSearchRow + 1
                strPOLine = (appExcel.Range("A" & CStr(LSearchRow)).Value)
                'Do until the next results, prevents writing previous key on *** line.
                Do Until strPOLine Like sUntilText Or Len(appExcel.Range("A" & CStr(LSearchRow)).Value) = 0
                    LSearchRow = LSearchRow + 1
                    LCopyToRow = LCopyToRow + 1
                    strRowValue = Trim(strRowValue) & " " & Trim((appExcel.Range("A" & CStr(LCopyToRow)).Value))
                    strPOLine = (appExcel.Range("A" & CStr(LSearchRow)).Value)
                Loop
                    appExcel.Range(sRow & CStr(LCopyToRow)).Select
                    appExcel.Range(sRow & CStr(LCopyToRow)).Value = strRowValue
                    LSearchRow = LSearchRow - 1
            Else
                GoTo SkipRow
            End If
            
SkipRow:
    LSearchRow = LSearchRow + 1
    LCopyToRow = LCopyToRow + 1
    
    Wend

Thanks. Sean.

 

[Remou]

[-i]1. How do I sequence through a recordset looking for values?[/i]

In general, you don't:

Dim rs As DAO.Recordset

strSQL="SELECT * FROM SomeTable WHERE SomeNumber=1 " _
& " AND SomeText='Abc'"

'All the records with the relevant values
Set rs=OpenRecordset(strSQL)

'You can find
rs.FindFirst "SomeDate=#" & Format(date,"yyyy/mm/dd) & "#"

'You can move
Do While Not rs.EOF
   SomeValue=rs!SomeField
   rs.MoveNext
Loop

2. How do I write that value into a) the next column and b)another table as I sequence?

rs.Edit
rs!Field(1)=SomeValue
rs!SomeField="abc"
rs.Fields(txtText)=1

strSQL="Update SomeOtherTable Set FieldX='" & SomeText & "'"
CurrentDB.Execute strSQL

And so on.

 

[perrymans]

I will give it a shot.

Thanks. Sean.