473,548 Members | 2,585 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using the results of an SQL select statement in VBA

1 New Member
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.OpenRepor t "LibraryCar d", 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 5025
JConsulting
603 Recognized Expert Contributor
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.OpenRepor t "LibraryCar d", 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.OpenRepor t 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
2839
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 to acheive. I want to get this result by using update to update my table. I have attempted to create the update sql statement, see below.
4
6448
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 define the servername / hostname in my Perl Progrem.. Here is the code:
28
2226
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 some looking myself and found something called GROUP BY? Is that what I need to use? Thanks in advance.
8
2791
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, I have a where clause which states : where PermitID like @WorkType order by WorkStart DESC
6
3030
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 equal nothing (if it is nothing, its returning all the values) now, when this query runs and I select a flower location, i.e. L3 I want it to return...
1
4264
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 another main and sub form. The requery on the sub form and refresh comands on the main form do not work when the form is first displayed and when...
1
1975
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 = 'yes' ORDER BY year DESC LIMIT 2, 1"; $result = mysql_query($sql); while($fetched = mysql_fetch_array($result)) { $ceiling = $fetched; }...
0
5694
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 result of the sub query is used by the main query (outer Query). You can place the sub query in a number of SQL clauses. WHERE clause
7
1280
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 aren't in the form... so far I have everything except how best to do the select statement and was wondering if any of you fanatstic people out there...
0
7512
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7951
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7466
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5362
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5082
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3475
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1926
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1051
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
751
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.