Loop through a Table and return the value


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.

Nope.

Can you post some sample data in the before state, in the after state, and what you want to achieve with the Insert statement?

Do you want to "loop" through rows or columns? How do you want to capture the values, etc? What values do you want to capture?

Also, what do you mean you want the code to handle change?



mmcdonal

Look it up at: http://wrox.books24x7.com

What i would like is something like this:

Start loop

TblValue = ExportTables.Value
DoCmd.RunSQL "INSERT INTO TEST_DOC SELECT * FROM " & TblValue

'get value in table

End loop


I want to loop through the rows has the table only has one column.

The data in the table i.e. MPI_CORE will change so i need some code that can handle this.

The insert statement will just insert the value from the table in another table has the data in the table contain table names.

'get value in table
 sorry i meant
'next value in table

I am still not exactly sure what you want to do. Here is what I would do using ADO:

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

Did that help?




mmcdonal

Look it up at: http://wrox.books24x7.com

Yes thank you. I changed the code a little to suit my purpose but the general idea was good.

Thank you :D