Using the results of an SQL select statement in VBA

[anthonyjm]

I need to run a report for each record returned from a SQL Select statement. The report changes for each record, so they need to be run separately.

So something like this (I have no idea what the syntax would be):

strSelect = "SELECT field1, field2, field3 FROM table1 WHERE field4 = '1';"
results = DoCmd.RunSQL strInsert
foreach results AS result
{
DoCmd.OpenReport "LibraryCard", acNormal, "", ""
}

Somehow passing the field values for each record to the report as it opens. This may not be possible, I don't know. I'm much more comfortable working in PHP/MySQL and I haven't figured out how to do equivalent things in access yet.

If anyone has any ideas, even with just passing values to a report before it render or parsing the results of a select statement in VBA, I'd appreciate it.

[JConsulting]

I need to run a report for each record returned from a SQL Select statement. The report changes for each record, so they need to be run separately.

So something like this (I have no idea what the syntax would be):

strSelect = "SELECT field1, field2, field3 FROM table1 WHERE field4 = '1';"
results = DoCmd.RunSQL strInsert
foreach results AS result
{
DoCmd.OpenReport "LibraryCard", acNormal, "", ""
}

Somehow passing the field values for each record to the report as it opens. This may not be possible, I don't know. I'm much more comfortable working in PHP/MySQL and I haven't figured out how to do equivalent things in access yet.

If anyone has any ideas, even with just passing values to a report before it render or parsing the results of a select statement in VBA, I'd appreciate it.

Are you doing this in Access? If so, Do you have a different report for each result? Or are you simply trying to put one result into the same report with criteria?

If you use that SQL, or something similar, you can assign it as a recordset, where you can loop through it. You can gain values from specific fields using variables. Then you can use that variable(s) as criteria for the Docmd.OpenReport command.

Here's an example:

Sub On_Click()   'Guessing it's a click
Dim rs as DAO.Recordset
Dim sSQL as string
sSQL = "SELECT field1, field2, field3 FROM table1 WHERE field4 = '1';"
If rs.EOF then Exit Sub
rs.movefirst
do until rs.EOF
DoCmd.OpenReport "yourreport", acViewPreview, , "ID=" & rs!Field1
rs.movenext
loop
 
rs.close
set rs = nothing
end sub
 

Luck!
J