Loop through a Table and return the value

[hewstone999]

I have a table (ExportTables) with the following data

ExportTables
--------------------
MPI_CODE
MPI_IDS
MPI_REFF
REFFERALS
NOK_AD
--------------------

I want some VBA code that can loop through the table above one by one returning the value. The table data will change so i would like the code to handle change as well.

I want the returning value to be returned has a string i.e. TblValue = <table data value>

Because then i want to use the value to be put in this sql query- DoCmd.RunSQL "INSERT INTO TEST_DOC SELECT * FROM " & TblValue

then loop onto the next value.

Hope you understand what im after.

 

[gemma-the-husky]

yuo need a recordset

the code/pseudocode is something like

-----------
dim rs as recordset

set rs = currentdb.openrecordset "mytable"

while not rs.eof
{process the record}
rs.movenext
wend

rs.close
--------------------------
when you are in a record, you can examine fields with
thisvalue = rs!myfield

to set/update fields you need to add

rs.edit 'to edit it
then you can say
rs!myfield = "whatever"
rs.update 'to save the edit

various other commands exist for recordsets

 

[hewstone999]

Thanks, i used this code in the end:

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim sValue As String

sSQL = "SELECT * FROM ExportTables"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

DoCmd.SetWarnings False
rs.MoveFirst

Do Until rs.EOF
sValue = rs("ColumnName")
sSQL = "INSERT INTO TEST_DOC SELECT * FROM " & sValue
DoCmd.RunSQL sSQL

rs.MoveNext
Loop
DoCmd.SetWarnings True
rs.Close