469,600 Members | 2,444 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,600 developers. It's quick & easy.

Using the results of an SQL select statement in VBA

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.
Jul 18 '07 #1
1 4841
JConsulting
603 Expert 512MB
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:

Expand|Select|Wrap|Line Numbers
  1. Sub On_Click()   'Guessing it's a click
  2. Dim rs as DAO.Recordset
  3. Dim sSQL as string
  4. sSQL = "SELECT field1, field2, field3 FROM table1 WHERE field4 = '1';"
  5. If rs.EOF then Exit Sub
  6. rs.movefirst
  7. do until rs.EOF
  8. DoCmd.OpenReport "yourreport", acViewPreview, , "ID=" & rs!Field1
  9. rs.movenext
  10. loop
  11.  
  12. rs.close
  13. set rs = nothing
  14. end sub
  15.  
Luck!
J
Aug 1 '07 #2

Post your reply

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

Similar topics

reply views Thread by Julie Paten | last post: by
28 posts views Thread by kiqyou_vf | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.