I have multiple queries that will run on my onopen event of my report. I use the docmd.openquery for the select queries the problem is I can see all these popup in the background. I do use the close query command also and the setwarnings to false and true. Can anyone give me some sample code that uses like 3 generic select queries using any sample database and then uses that data to create a temp table for my report all in vba code.I can use the temp table as my record source of the report..I do use the strSQL for my action queries.it's the select queries that I need help on. I know this can be done by using qrydef and recordsets I just don't understand how to put it together. please help me. I have many reports that have those little popups and if I can get help on one I should be able to fix my other reports. Thnks so much for any help you can give. Desperate!
11 5867
You can use eithe DAO or ADO in VBA code to SELECT data, UPDATE data or INSERT data.
I will give you an example of the older DAO because it might be a bit simpler.
1) From the code page of your form goto tools/references.
2) Tick the reference "Microsoft DAO 3.6 Object Library"
It may already be ticked.
Now you can use DAO to interract directly with your database objects.
First thing you will need is an instance of your database -
'Create an instance of the database for use in VBA
-
Dim db As DAO.Database
-
Set db = CurrentDb
-
you can run update and insert queries directly through
the database instance -
db.execute "INSERT INTO theTargetTable SELECT * FROM theSourceTable"
-
-
-
db.execute "UPDATE theTargetTable SET theField=theValue"
-
these are just sample queries, you can write something more realistic.
To query data and then work with that data you need a recordset object. -
Dim rst As DAO.Recordset
-
Set rst = db.OpenRecordset("SELECT * FROM theTable")
-
-
'Loop through the records and do something
-
While Not rst.EOF
-
MsgBox rst.Fields(0)
-
rst.MoveNext
-
Wend
-
Once finished with the DAO objects you should destroy them -
rst.close
-
set rst=nothing
-
db.close
-
set db=nothing
-
I hope that is enough to get you started
Haven't tested that so I hope I haven't given you any syntax errors
Thanks so much ..been swamped at work & had not had chance to reply..will this still work if my select queries uses info from the prev query example: I run qry1..now qry 2 uses info from qry1 & another table..qry 3 uses info from qry 2 and so on until my last query creates the table for my report..all these queries need to be run prior either in a form or as a report which is what I want..still confused?
I don't have a clear understanding of what you are doing so I can only give you general Ideas.
Take the code from query1
for example -
SELECT Field1,Field2,Field1+Filed2 as Field3
-
FROM Table1
-
Now your code for query2 might be -
SELECT Query1.Field1,Query1.Field2,Query1.Field3,Table2.Field4
-
FROM Query1
-
JOIN Table2 on Query1.Field1=Table2.Field1
-
To turn both those queries into a single query then -
-
SELECT a.Field1,a.Field2,a.Field3,Table2.Field4
-
FROM
-
( SELECT Field1,Field2,Field1+Filed2 as Field3
-
FROM Table1
-
) a
-
JOIN Table2 on a.Field1=Table2.Field1
-
its called a subquery and you can have a suquery within a subquery within a subquery ....
Now to turn that query into an insert query -
INSERT into Table3
-
SELECT a.Field1,a.Field2,a.Field3,Table2.Field4
-
FROM
-
( SELECT Field1,Field2,Field1+Filed2 as Field3
-
FROM Table1
-
) a
-
JOIN Table2 on a.Field1=Table2.Field1
-
wrap all that up into a string variable and execute it
like in my first post.
Does that help?
-
Dim db As DAO.Database, strSQL as string
-
Set db = CurrentDb
-
-
strSQL=""
-
strSQL=strSQL & "INSERT into Table3 "
-
strSQL=strSQL & "SELECT a.Field1,a.Field2,a.Field3,Table2.Field4 "
-
strSQL=strSQL & "FROM "
-
strSQL=strSQL & "( SELECT Field1,Field2,Field1+Filed2 as Field3 "
-
strSQL=strSQL & " FROM Table1 "
-
strSQL=strSQL & ") a "
-
strSQL=strSQL & "JOIN Table2 on a.Field1=Table2.Field1 "
-
-
db.execute strSQL
-
-
db.close
-
set db=nothing
-
Actually rereading your posts I am a little confused.
Your second post implies that one query is querying from another query which is querying another query and eventually one of the queries inserts records into a table.
In that case you can use subqueries to make one query out of two or more queries
Your first post states that you use multiple docmd.openquery and each query uses the results of the previous query that was run. This suggests to me that each of those queries either inserts records into a table, or updates records in a table with the final query inserting records from the records inserted/updated by the other queries into another table for your report.
The problem you are having with that is
the problem is I can see all these popup in the background
That being the case then the answer to your second question
will this still work if my select queries uses info from the prev query example
is yes.
This is the question that confuses me because that question should also apply to
docmd.openquery
Instead of using docmd.openquery
you are using db.execute QueryString.
The difference (in the context of your question) is docmd.openquery is executing a query definition which you can see popping up in the background.
db.execute QueryString is happening within the database object in VBA. Nothing will pop up in the background.
An answer to your orriginal question, How to stop these things from popping up in the back ground, is use DAO instead of DOCMD.
In this situation you should find that they are different methods of doing the same thing
NeoPa 32,556
Expert Mod 16PB
Welcome to Bytes Patty!
Maybe I can help to clarify things a little here. Your question shows a little confusion over how things fundamentally work in Access specifically, and possibly databases in general. Hence Delerna's difficulty in giving a matching response (He does, but as he's unsure of the question I suspect he doesn't realise which of his various good answers is appropriate).
What I think you're trying to do is build a temporary table of data that involves running multiple queries. Quite naturally, you want to run the queries in order. This doesn't make as much sense as you expect though. What needs to be done is that each level of query needs to be built on the previous level. The lowest level will use one or more tables as the source for its data. Other queries will use the results of the first queries to build upon. The way that is achieved is by using these saved queries (Technically called QueryDefs in Access) as the source for the new query. The MakeTable query would use one or more of these subqueries to do the work. Invoking this one query would cause all the lower level queries to execute automatically and fit together sensibly in the way you've designed it. Does that clarify things for you?
Tell me I'm wrong, but if a report is bound to a query, then won't that query will be run when the report is opened?
If so, then if qry1 is "SELECT A, B, C, ... FROM tbl1 .....
and qry2 is "SELECT B, C, ... FROM qry1 .....
and qry3 is "SELECT ..... FROM qry2 .....
and the report is bound to qry3, then Access will run all the queries when the report is opened.
NeoPa 32,556
Expert Mod 16PB
You're not wrong OB, but I suspect Patty is still pretty inexperienced at this and didn't appreciate that that's how things fit together.
I don't mean any disrespect to Patty but it just never occured to me that someone would think that. That certainly removes my confusion if that is the case.
NeoPa 32,556
Expert Mod 16PB
I know I've had to explain similar concepts to members in the past, so I wouldn't be as surprised. If you consider the concept from the point of no previous Access experience, it's not so strange really, but it's always hard to consider not knowing something you know - if that makes sense.
I think this may be what you need. You can create multiple queries, building the second one based on the first one, the third one based on the second one, etc. in the following way.
The two lines I have commented out are the ones I use to make the query visible--the popup you are seeing and a msg box as a debugging breakpoint. I only use this during development and debugging -- then I comment it out. If you comment out DoCmd.OpenQuery...., it won't show up, but will still run in the backgound. When you no longer need the query, DoCmd.Close acQuery, "nameofquery" will close it. You can also delete it as shown if you want. - Dim qdf, qdf2, qdf3, qdf4, qdf5 As QueryDef
-
-
sqlStr = "your select query here"
-
-
Set qdf = CurrentDb.CreateQueryDef("TA1", sqlStr)
-
-
'DoCmd.OpenQuery "TA1", acViewNormal, acReadOnly
-
'msgbox "Do you want to close view?", vbOKOnly, "Close view"
-
-
DoCmd.Close acQuery, "TA1"
-
DoCmd.DeleteObject acQuery, "TA1"
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mike MacSween |
last post by:
tblCourses one to many to tblEvents.
A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the
future.
At...
|
by: Bob Darlington |
last post by:
I have downloaded Stephen Leban's code to write an Access report to rtf, and
am running into trouble with the fCreateSnap procedure at the line:
Set specs = CreateClass
When I try to compile, I...
|
by: ellenh |
last post by:
I have read postings on the similar subject including the posting from
2003 shown below. This process works fine to display a single page
snapshot report in PowerPoint. I need to display...
|
by: Will |
last post by:
- I know enough ASP and Access to be dangerous :)
- I need to put up a data base on our web server with 3 related tables.
- They will be accessed by a limited number of people.
- Each user will...
|
by: lasithf |
last post by:
Hi,
I am looking for a way to get multiple sql retrievals from SQL*PLUS.
here is the scenario:
I am going to execute following sql query:
select * from table1;select* from table2;select* from...
|
by: akabir77 |
last post by:
Hi
I am using the following code to convert a access report into HTML format and then sending it via email.
DoCmd.SendObject _
acSendReport, _
...
|
by: Supermansteel |
last post by:
My team at work uses Cognos to run multiple queries to pull in data. Then they take that data and import into Access and then usually run an Append Query to run a RND function to pull out a few...
|
by: micarl |
last post by:
How would i print a report based on criteria selected from several
Combo Boxes as well as multiple Multi Select List Boxes, that are
located on the same form?
I can get one Multi List Box, just...
|
by: DanicaDear |
last post by:
I am trying my hand at my first ever Access report. It is based on a two parameter query (start date, end date). When I use the report wizard and click "view report" the report prompts me for the...
|
by: g diddy |
last post by:
Hi
I'm using a statement to call other Subs if a unit (or multiple units) in a multi-select list box has been selected (and if not - do nothing). I know that the part below in Bold and Underlined...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |