473,320 Members | 1,974 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 5016
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

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

Similar topics

0
by: Julie Paten | last post by:
**** Post for FREE via your newsreader at post.usenet.com **** Hello, I am using sql+ to try and update a table and am having some trouble. Below is a select statement with the result I want...
4
by: banz | last post by:
Hello I have a problem to resolve: I wrote a Perlscript which caches data from a server (local on my machine) I would like to have a other connection to a remote server but I don't know how to...
28
by: kiqyou_vf | last post by:
I'm trying to pull data from 2 different tables and do a loop to retrieve more than one row. I'm having problems with aligning the information. Can someone lead me in the right direction? I've done...
8
by: rbg | last post by:
I did use query plans to find out more. ( Please see the thread BELOW) I have a question on this, if someone can help me with that it will be great. In my SQL query that selects data from table,...
6
markmcgookin
by: markmcgookin | last post by:
Hi Folks, I am running a simple query using VB (This isnt a VB Question, dont worry!) on SQL Server Compact. I have the query below being created, and then added to if a flower location doesn't...
1
by: tomlebold | last post by:
Having problems displaying query results from combo boxes on a sub form, which is on the same form that is used to select criteria. This has always worked form me when displaying query results on...
1
by: digidave | last post by:
I am keenly aware that my coding skills are extremely noob but please indulge me a second.. Take a look at these queries.. $sql = "SELECT DISTINCT year FROM _current_floats_config WHERE active =...
0
debasisdas
by: debasisdas | last post by:
Using Subqueries ================== The sub query is often referred to as a nested SELECT, Sub - SELECT, or inner SELECT statement. The sub query executes once before the main query. The...
7
by: Paigey | last post by:
Hi all, Having a few problems with a select statement... I have a table containing values similar to results from a form and when submitted I want to get all results from the table that...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.