422,767 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,767 IT Pros & Developers. It's quick & easy.

vba to loop thru access table and query each record

P: 1

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


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.


Private Sub showTableData()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("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.CreateQueryDef("tempquery", sSQL)

DoCmd.OpenQuery ("tempquery")

rs.MoveNext 'press Ctrl+G to see debuG window beneath

' MsgBox ("End of Table")

End Sub
Sep 11 '17 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.