how to put a row into a variable

L

Lee Michon

Basically I have a query that comes up with 10 entries..each entry has both
SITECODE and IP... I need to know how to take the IP address from each of the
10 entrys and store them in a variable.

This is BAD code that will show you want I want to do:
DIM IPAdd(10)
DIM I
I=0

do while i<10
IPAdd(i).RowSource = "SELECT [Shipment#3].[IP] FROM [Shipment#3]" entry i
i+1
loop

I dont know how to query without using a specific argument..usually id say
take IP from SITECODE h001 and that be it, but I want to automate and I dont
know how to make the select statement get the IP from the first entry, then
the next, and the next till they have all been taken.

Please help.

Lee
 
K

Ken Snell [MVP]

What you do is open a recordset based on the query, and then walk through
the records to get the different values from the records.

Dim IPAdd(0 To 10) As Variant, I As Long
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT [Shipment#3].[IP] FROM [Shipment#3]",
dbOpenDynaset)
I = 0
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
IPAdd(I) = rst.Fields(0).Value
I = I + 1
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
 
Top