By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Using the results of an SQL select statement in VBA

P: 1
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
Share this Question
Share on Google+
1 Reply


JConsulting
Expert 100+
P: 603
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.