Hello,
I am trying to do something that sounds simple, but it seems impossible to me after trying for many hours.
I want to iterate thru a table, grab the ID field, and run a series of queries based on the ID.
Desired steps:
Grab the ID from record 1
Run query 1
run an add-in to do percentile analysis (this works just fine)
insert into the "results" table (prior versions of this table can be deleted)
Grab the ID from record 2
run query 1
run an add-in to do percentile analysis
append to the results table
repeat...
So far, I have failed miserably, can get it to run thru the IDs table and display the contents of each record. I even got it to run the query one time, but am stuck. Any help is gratefully appreciated.
[CODE]
Private Sub showTableData()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordse t("ids") 'ids is a unique ID of all respondents
Dim sSQL As String
Dim sValue As String
Do While Not rs.EOF
' Debug.Print (rs!company) 'myField is a field name in table myTable
sValue = rs("id")
MsgBox sValue
sSQL = "select * from realcd where id=" & sValue
MsgBox sSQL
Dim qdf As QueryDef
Set qdf = CurrentDb.Creat eQueryDef("temp query", sSQL)
db.QueryDefs.Re fresh
DoCmd.OpenQuery ("tempquery" )
rs.MoveNext 'press Ctrl+G to see debuG window beneath
Loop
' MsgBox ("End of Table")
End Sub
{/CODE]
I am trying to do something that sounds simple, but it seems impossible to me after trying for many hours.
I want to iterate thru a table, grab the ID field, and run a series of queries based on the ID.
Desired steps:
Grab the ID from record 1
Run query 1
run an add-in to do percentile analysis (this works just fine)
insert into the "results" table (prior versions of this table can be deleted)
Grab the ID from record 2
run query 1
run an add-in to do percentile analysis
append to the results table
repeat...
So far, I have failed miserably, can get it to run thru the IDs table and display the contents of each record. I even got it to run the query one time, but am stuck. Any help is gratefully appreciated.
[CODE]
Private Sub showTableData()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordse t("ids") 'ids is a unique ID of all respondents
Dim sSQL As String
Dim sValue As String
Do While Not rs.EOF
' Debug.Print (rs!company) 'myField is a field name in table myTable
sValue = rs("id")
MsgBox sValue
sSQL = "select * from realcd where id=" & sValue
MsgBox sSQL
Dim qdf As QueryDef
Set qdf = CurrentDb.Creat eQueryDef("temp query", sSQL)
db.QueryDefs.Re fresh
DoCmd.OpenQuery ("tempquery" )
rs.MoveNext 'press Ctrl+G to see debuG window beneath
Loop
' MsgBox ("End of Table")
End Sub
{/CODE]