Looping Though Columns?

[CharlesWhiteman]

Hi, I have the following example of code I have previously used which loops through columns where a column is called "Page1", "Page2", Etc. Etc.

This is fine when the number of columns is fairly small. But in a new case I have 585 250 columns which may or may not contain some data which is the key.

Is there a better way say

If data exists in column do something and after that or of not move to the next column and do the same thing until there are no more columns?

Dim rst As DAO.Recordset ' recordset for category data
  
  ' open the recordset for categories
  Set rst = CurrentDb.QueryDefs!QryRMA.OpenRecordset
   
  ' loop through the rows in the recordset
  rst.MoveFirst
  Do Until rst.EOF
  
  If Not IsNull(rst!Page1) Then
    strClientID = (rst!ClientID)
    strClientName = (rst!ClientName)
    strDocDate = (rst!DocDate)
    strDocDescription = (rst!DocDescription)
    strInfoType = (rst!InfoType)
    strFilePath = (rst!Page1)
        
        funcP1
    Else
    End If
    
    rst.MoveNext
  Loop
  
  ' loop through the rows in the recordset
  rst.MoveFirst
  Do Until rst.EOF
  
  If Not IsNull(rst!Page2) Then
    strClientID = (rst!ClientID)
    strClientName = (rst!ClientName)
    strDocDate = (rst!DocDate)
    strDocDescription = (rst!DocDescription)
    strInfoType = (rst!InfoType)
    strFilePath = (rst!Page2)
        
        funcP2
    Else
    End If
    
    rst.MoveNext
  Loop

 

[stopher]

I think the solution you need has already been provided here.

Be aware that it is considered bad etiquette to double post the same question in these forums.

 

[CharlesWhiteman]

Thanks for the reply, however I think the title of each of my two posts clearly indicates two completely different questions which happen to relate to the same project.

This question is not related to importing data.

 

[CharlesWhiteman]

I found the following code but it only loops through the first column, not then also the subsequent columns/rows

Dim rs As DAO.Recordset
Set rs = CurrentDb.QueryDefs!qryForDirectories.OpenRecordset
'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True
       
        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If
MsgBox "Finished looping through records."
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up

 

[JHB]

Use the fields collection to cycle through all the fields (columns) in the recordset for each row.

 

[Chrafangau]

Taking your first post, I suggest adding a second loop around the first, to set the focus to the next column, so on and so for however many columns you have. This assumes you know how many there are in advance:

Dim rst As DAO.Recordset ' recordset for category data
Dim pageno As Int 'Page number, counts up as each row is done
Dim page As String
  
  ' open the recordset for categories
  Set rst = CurrentDb.QueryDefs!QryRMA.OpenRecordset
   pageno = 1
   page = "page" & pageno 'Appends pageno onto page for use in addressing
  ' loop through the rows in the recordset
  rst.MoveFirst
Do While pageno <= numberofcolumns
  Do Until rst.EOF
  
  If Not IsNull(rst!page) Then
    strClientID = (rst!ClientID)
    strClientName = (rst!ClientName)
    strDocDate = (rst!DocDate)
    strDocDescription = (rst!DocDescription)
    strInfoType = (rst!InfoType)
    strFilePath = (rst!Page1)
        
        funcP1
    Else
    End If
    
    rst.MoveNext
  Loop
  ' loop through the rows in the recordset
  rst.MoveFirst
  pageno = pageno + 1
Loop

If the IsNull(rst!page) doesn't work, try setting rst!page as the value of a variable, and check that for null.