Loop through columns on a access table 1

[don2241]

Hi!

Problem:
I am trying to loop through columns on each row of a database table using VB-code in access

Table format
P_ID 1 2 3 4 5
------------------------
item1 A B C D
item2 A B
item3 A B C
item4 A B C D E
item5
item6 A

How do I loop through colums 1, 2, 3, 4, and 5

My sample code:
rst, count and pxID has been defined

If (rst.RecordCount > 1) Then
Do While (rst.EOF <> True)
count = 0
If (rst![1] <> "") Then
Do Until (rst![count + 1] Is Null) 'my error is here 'count +1' cannot be reconized as a column name
pxID = rst!P_ID
count = count + 1
Loop
End If
msgbox(pxID & " " & count
rst.MoveNext
Loop
else
msgbox("Error")
end if

How do I get around this problem

THX
/Martin

 

[PHV]

Something like this ?
If (rst.RecordCount > 1) Then
Do While (rst.EOF <> True)
pxID = rst!P_ID
For count = 0 To 4
If IsNull(rst(CStr(count + 1))) Then Exit For
Next count
MsgBox pxID & " " & count
rst.MoveNext
Loop
Else
MsgBox("Error")
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[don2241]

Thanks PHV
Funny how it works. All I had to do in my code has to delete the "!" after rst in my 'Do While' statment and my code works to.

Thanks