Hi there,
I have a query (QueryB) that is based on the results of another query
(QueryA).
When I open QueryB itself, it displays the exact records that I want. I
have a report based on QueryB, and when I open it directly, it displays
what I want. When I open the report from a command button on a form
using this code:
DoCmd.OpenRepor t "rpt_QueryB ", acViewPreview, "QueryB"
...I get prompted for parameters.
Since I can open the query and the report itself without getting
prompted for parameters, why do I get prompted for them when I use
docmd.openrepor t? I don't want it to do this.
Thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it! 8  3247 
Try just this
DoCmd.OpenRepor t "rpt_QueryB ", acViewPreview
--
Wayne Morgan
Microsoft Access MVP
"Steven Stewart" <o6***@unb.ca > wrote in message
news:3f******** *************@n ews.frii.net... Hi there,
I have a query (QueryB) that is based on the results of another query
(QueryA).
When I open QueryB itself, it displays the exact records that I want. I
have a report based on QueryB, and when I open it directly, it displays
what I want. When I open the report from a command button on a form
using this code:
DoCmd.OpenRepor t "rpt_QueryB ", acViewPreview, "QueryB"
..I get prompted for parameters.
Since I can open the query and the report itself without getting
prompted for parameters, why do I get prompted for them when I use
docmd.openrepor t? I don't want it to do this.
Thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Hi there,
I tried "DoCmd.OpenRepo rt "rpt_QueryB ", acViewPreview" and this did work
(it didn't ask me for parameters), but now the problem has come back.
In actuality, I need to specify the where clause in this
docmd.openrepor t....so I have this:
"DoCmd.OpenRepo rt "rpt_QueryB ", acViewPreview,, strSQL"
...and yet again it is prompting me for parameters. Any thoughts?
Without the strSQL it won't prompt me for parameters, but I do need that
there. Here is strSQL:
strSQL = "((([subquery_YTD_Pr ov_A]![Province])='" & lstProvince.Val ue &
"') AND (([subquery_YTD_Pr ov_A]![Month])>= " & getMonth(lstMon th2.Value)
& " Or ([subquery_YTD_Pr ov_A]![Month])<=" & getMonth(lstMon ths.Value) &
") AND (([subquery_YTD_Pr ov_A]![Year])>= " & txtStartYear & " Or
([subquery_YTD_Pr ov_A]![Year])<= " & txtYear & " ))"
Basically, I want the query to be based on values from the form. I want
the query to return values in a certain month/year range (i.e. 2/2002 to
4/2003) and I want the province specified.
Thansk for any help!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Steven Stewart <o6***@unb.ca > wrote in
news:3f******** *************@n ews.frii.net: Hi there,
I tried "DoCmd.OpenRepo rt "rpt_QueryB ", acViewPreview" and
this did work (it didn't ask me for parameters), but now the
problem has come back. In actuality, I need to specify the
where clause in this docmd.openrepor t....so I have this:
"DoCmd.OpenRepo rt "rpt_QueryB ", acViewPreview,, strSQL"
..and yet again it is prompting me for parameters. Any
thoughts? Without the strSQL it won't prompt me for
parameters, but I do need that there. Here is strSQL:
strSQL = "((([subquery_YTD_Pr ov_A]![Province])='" &
lstProvince.Val ue & "') AND (([subquery_YTD_Pr ov_A]![Month])>=
" & getMonth(lstMon th2.Value) & " Or
([subquery_YTD_Pr ov_A]![Month])<=" & getMonth(lstMon ths.Value)
& ") AND (([subquery_YTD_Pr ov_A]![Year])>= " & txtStartYear &
" Or ([subquery_YTD_Pr ov_A]![Year])<= " & txtYear & " ))"
You have a bunch of parameters in strSQL that the jet engine cannot
resolve, because your where clause is all messed up.
strSQL =
"[subquery_YTD_Pr ov_A].[Province]='" _
& me.lstProvince. Value & _
& "' " _
& "AND [subquery_YTD_Pr ov_A].[Month] >= " _
& getMonth(me.lst Month2.Value) _
& " AND [subquery_YTD_Pr ov_A].[Month] <= "
& getMonth(me.lst Months.Value) _
& " AND [subquery_YTD_Pr ov_A].[Year] >= " _
& me.txtStartYear _
& " AND [subquery_YTD_Pr ov_A].[Year] <= "
& me.txtYear
should work a lot better.
You had ! separators where . is required and no proper form
identifiers (me.), plus a whole lot of useless parentheses.
You also had some OR statements that really want to be AND
statements.
Bob Q.
Basically, I want the query to be based on values from the
form. I want the query to return values in a certain
month/year range (i.e. 2/2002 to 4/2003) and I want the
province specified.
Thansk for any help!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Thanks for the syntax lesson!..yes that, indeed, solves it, and I have
learned! This is, obviously, new stuff for me. Thanks again..
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Steven Stewart <o6***@unb.ca > wrote in message news:<3f******* **************@ news.frii.net>. .. Hi there,
I tried "DoCmd.OpenRepo rt "rpt_QueryB ", acViewPreview" and this did work
(it didn't ask me for parameters), but now the problem has come back.
In actuality, I need to specify the where clause in this
docmd.openrepor t....so I have this:
"DoCmd.OpenRepo rt "rpt_QueryB ", acViewPreview,, strSQL"
..and yet again it is prompting me for parameters. Any thoughts?
Without the strSQL it won't prompt me for parameters, but I do need that
there. Here is strSQL:
strSQL = "((([subquery_YTD_Pr ov_A]![Province])='" & lstProvince.Val ue &
"') AND (([subquery_YTD_Pr ov_A]![Month])>= " & getMonth(lstMon th2.Value)
& " Or ([subquery_YTD_Pr ov_A]![Month])<=" & getMonth(lstMon ths.Value) &
") AND (([subquery_YTD_Pr ov_A]![Year])>= " & txtStartYear & " Or
([subquery_YTD_Pr ov_A]![Year])<= " & txtYear & " ))"
Basically, I want the query to be based on values from the form.
you have to reference the WHOLE "path" to the control you're
referencing, not just the control itself. the Control is on a form (in
that form's controls collection), which is in the forms collection...
Forms![MyForm]![MyControlOnMyFo rm]
strSQL = "((([subquery_YTD_Pr ov_A]![Province])='" & [FORMS]![MYFORM]!lstProvince.Va lue &
"') AND (([subquery_YTD_Pr ov_A]![Month])>= " & getMonth([FORMS]![MYFORM]!lstMonth2.Valu e)
& " Or ([subquery_YTD_Pr ov_A]![Month])<=" & getMonth([FORMS]![MYFORM]!lstMonths.Valu e) &
") AND (([subquery_YTD_Pr ov_A]![Year])>= " & txtStartYear & " Or
([subquery_YTD_Pr ov_A]![Year])<= " & txtYear & " ))"
Hi there,
Although I said it worked, I was mistaken. I had something REMed out
that shouldn't have been, making me think it had worked. So I tried
what was suggested:
strSQL =
"[subquery_YTD_Pr ov_A].[Province]='" _
& me.lstProvince. Value & _
& "' " _
& "AND [subquery_YTD_Pr ov_A].[Month] >= " _
& getMonth(me.lst Month2.Value) _
& " AND [subquery_YTD_Pr ov_A].[Month] <= "
& getMonth(me.lst Months.Value) _
& " AND [subquery_YTD_Pr ov_A].[Year] >= " _
& me.txtStartYear _
& " AND [subquery_YTD_Pr ov_A].[Year] <= "
& me.txtYear
DoCmd.OpenRepor t "rpt_YTD_By_Pro vince", acViewPreview,
"subquery_YTD_P rov_B", strSQL
...and I still get prompted for parameters (subquery_YTD_P rov_A.Month
and .Year).
Why would it prompt me for those values when it is getting those values
in the code? I tried Val(me.txtYear) but that makes no difference.
What I have is a query (subquery_YTD_P rov_A) that works fine. Every
record returned in that query has a Month, Year, and Province field.
For the query I am doing ON that query, I want to only return the
records of a specific date range (month/year to month/year) for a
specific province. It's worth noting that it does not prompt me for the
province parameter....on ly the Month and Year.
I have named this second query "subquery_YTD_P rov_B" and if I open the
object directly, it works fine, but with the docmd from code it is not
working fine.
That is what I am trying to do, but I don't understand why I am getting
prompted for parameters still. Thanks again for any assistance.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Have you tried "hard coding" numbers in as a test instead of using the
references to the form's controls?
--
Wayne Morgan
Microsoft Access MVP
"Steven Stewart" <o6***@unb.ca > wrote in message
news:3f******** *************@n ews.frii.net... Hi there,
Although I said it worked, I was mistaken. I had something REMed out
that shouldn't have been, making me think it had worked. So I tried
what was suggested:
strSQL =
"[subquery_YTD_Pr ov_A].[Province]='" _
& me.lstProvince. Value & _
& "' " _
& "AND [subquery_YTD_Pr ov_A].[Month] >= " _
& getMonth(me.lst Month2.Value) _
& " AND [subquery_YTD_Pr ov_A].[Month] <= "
& getMonth(me.lst Months.Value) _
& " AND [subquery_YTD_Pr ov_A].[Year] >= " _
& me.txtStartYear _
& " AND [subquery_YTD_Pr ov_A].[Year] <= "
& me.txtYear
DoCmd.OpenRepor t "rpt_YTD_By_Pro vince", acViewPreview,
"subquery_YTD_P rov_B", strSQL
..and I still get prompted for parameters (subquery_YTD_P rov_A.Month
and .Year).
Why would it prompt me for those values when it is getting those values
in the code? I tried Val(me.txtYear) but that makes no difference.
What I have is a query (subquery_YTD_P rov_A) that works fine. Every
record returned in that query has a Month, Year, and Province field.
For the query I am doing ON that query, I want to only return the
records of a specific date range (month/year to month/year) for a
specific province. It's worth noting that it does not prompt me for the
province parameter....on ly the Month and Year.
I have named this second query "subquery_YTD_P rov_B" and if I open the
object directly, it works fine, but with the docmd from code it is not
working fine.
That is what I am trying to do, but I don't understand why I am getting
prompted for parameters still. Thanks again for any assistance.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Hard-coding in the numbers made no difference. It still prompted me for
parameters.
For testing purposes, I simplified it down to setting the criteria for
just one field: Province...
------
strSQL = "[subquery_Prov_A]![Province] = '" & lstProvince.Val ue & "'"
DoCmd.OpenRepor t "rpt_YTD_By_Pro vince", acViewPreview, subquery_Prov_B ,
strSQL
-------
When I run this, I always get prompted for the parameter
"subquery_Prov_ A!Province".
If I go directly to the queries design view and "hard-code" the criteria
into subquery_Prov_A and then open up subquery_Prov_B , it works
perfectly; however, I need to set the criteria based on form controls
before opening the report from code.
Now, even if I try...
strSQL = "[subquery_Prov_A]![Province] = 'British Columbia'"
...I still get prompted for that parameter.
So, this simply may not be possible to do.
Alas, I just realized that I could refer to the form controls directly
from the field's criteria in design view. Problem solved!
Thanks for all the help out there.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: John Macon |
last post by:
------=_NextPart_000_04B4_01C36308.415CE100
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi all,
Long time reader, first time poster, I hope that I get this right.
I am setting up a database that reflects a relationship between two =
|
by: sci |
last post by:
Could someone help me by answering the questions below?
What's a cursor?
What's difference between Query and View?
Is a RecordSet just part of a table? Can it be part of a query of view?
If the content in a table changed, is it necessary for a old recordset to
renew itself by do "Requery()"?
Thanks for your help!
|
by: seansan |
last post by:
Hi,
suppose I have one report that reports some data to me based on a
functional area that I define. We have about 20 func_areas and every
time the report is the same.
Is there some way to have the report ask me (based on a query or
something) what func_area I would like the report to be based on?
ps. the report is based on a query so ultimately the selection has to
be passed to the query.
|
by: neptune |
last post by:
I have a query where each customer has an or . Sometimes
both fields for a customer are populated, but if is null, then
will be populated and vice versa. I have a form,
, where I select a value for from a combo
box. In my query I set the criteria for to
... My query finds the proper values
for .
Now I also want to find the values if I select a value for in
a separate combo box. In both controls, OnChange, I set the value of
|
by: Robert |
last post by:
I am trying to create a db for service providers by county. I'm relatively
new to db programming, but I have done quite a bit of programming ranging
from the old basic days up to doing some programming in the HotDocs
software. I've kind of accomplished my goal in access, but I'm not quite
there yet and figure I've really screwed something up.
The db consists of 5 tables I believe. T1 is the main contact info, with an
autonumber ID...
| | |
by: dskillingstad |
last post by:
I'm trying to set up a parameter query based on an unbound form. This
search form has about 5 text boxes where the user can type in values
within each box and search a specific table based on the values of
those 5 text boxes. One of two of these boxes are based on date. The
obvious part (being the novice that I am) I'm stuck on is having the
query search based on 1 to 5 search values? In other words, I need the
query to search based on...
|
by: Marc DVer |
last post by:
As a simple example, say there is table 'namelist' with column 'names'
as char(20). I would like to do something akin to:
select namelist.names as mynames, left(mynames,2) as initials;
In this example, I could just do left(namelist.names,2), but in more
complex cases a value retrieved may have had a more complex logic behind
it, e.g., if a bunch of nested if() statements. It would seem logical
that if a value is already retrieved...
|
by: tizmagik |
last post by:
I am having a lot of difficulty generating a CrossTab Query based
report. I have looked online for several tutorials and whatnot but I
have not been able to really find what I'm looking for, nor have I been
able to adapt other people's solutions/tips to fit what I need. If
anyone could please help me with the following it would be really
appreciated, thank you!
I need to generate a Report (say: repCrossTab) that grabs it's data
from the...
|
by: john |
last post by:
I created a form via the wizard with 1 main table and 2 one-on-one tables.
As i couldn't add a field to the form (a field that I added to the table
after creating the form), I googled out that the form is based on a Select
Query. I managed to alter that query so that the field shows up in the field
list.
My question is, why exactly does access base this form on a query? What is
the difference and the benefit?
Thanks,
john
|
by: Vince |
last post by:
Hello all,
I am using Visual Basic to open a saved query and then save
information in the query to an array for later use. The problem is
that the same query shows different results when opened directly vs.
when opened by Visual Basic. It is as if Visual Basic is not letting
the query fully evaluate before processing records.
The query is a subtotal query that contains several criteria set up as
"where" in the group-by box. Most of...
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
