Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Query Parameters passed from Form

Posted on 2003-10-29
10
422 Views
Last Modified: 2012-06-30
In my database (mdb, not adp) I have a parameter query similar to the following:

PARAMETERS PeriodBegin DateTime, PeriodEnd DateTime;
SELECT EmployeeName, TimeEntered
FROM TimeEntry
WHERE EntryDate Between [PeriodBegin] and [PeriodEnd]

This query supplies data to a chart in Form A. I would like to create a Form B to supply the parameters to the query. I do not want to specify the form name in the query because I would like the query to be reused with different forms.

How can I pass the parameters to the query from any form?

0
Comment
Question by:LiteralLight
  • 4
  • 3
  • 2
10 Comments
 
LVL 8

Expert Comment

by:MrRobin
ID: 9644273
I think you can specify the values of parameters in the parameters collection of the query definition...

  CurrentDb.QueryDefs("QueryName").Parameters("PeriodBegin") = x
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9644793
Re:> I would like to create a Form B to supply the parameters to the query.

You don't need to have  Form B to supply criteria to chart in Form A.  Just have following on your Form A:

txtFromDate, unbound, with date mask
txtToDate, unbound, with date mask

In the AfterUpdate events of both of these new test boxes, include:

RefreshChartOnFormA
'--------------
In the code window of Form A, include:

Sub RefreshChartOnFormA()
  Me!Chart.RowSource=Me!Chart.RowSource    'I need to check this line for accuracy
End Sub
'------
Now, change the SQL for the chart to:


I do not want to specify the form name in the query because I would like the query to be reused with different forms.

How can I pass the parameters to the query from any form?

SELECT EmployeeName, TimeEntered FROM TimeEntry
WHERE EntryDate >= IIF(IsDate(Forms![Form A]![txtFromDate]),Forms![Form A]![txtFromDate],[EntryDate]) And IIF(IsDate(Forms![Form A]![txtToDate]),Forms![Form A]![txtToDate],[EntryDate])

Mike
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9644825
BTW, if you leave txtToDate blank, it will ignor that date.  It will take txtFrom to what ever dates exits in the database.  Same is true with txtToDate.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:LiteralLight
ID: 9651953
Thanks MrRobin.

This is the type of solution I am looking for because it leaves the query available for other uses. I found that the syntax is:

set qdf = CurrentDb.QueryDefs("QueryName")
qdf("PeriodBegin") = x

I validated this in FormA by creating a record set, which returned the correct records.

Now the problem is that when the query is called from either FormB or the Chart object, neither recognize the parameters that were supplied from FormA.

How can I make the query when called from the Chart see the parameters supplied by FormA (or FormB, since I know I can make FormB see them. The chart object doen't have an Open/Load/Current event where I can but that same code.)

 
0
 

Author Comment

by:LiteralLight
ID: 9651971
Sorry, I got my As & Bs mixed up. The last three paragraphs should read:

I validated this in FormB by creating a record set, which returned the correct records.

Now the problem is that when the query is called from either FormA or the Chart object, neither recognize the parameters that were supplied from FormB.

How can I make the query when called from the Chart see the parameters supplied by FormB (or FormA, since I know I can make FormA see them. The chart object doen't have an Open/Load/Current event where I can but that same code.)
0
 
LVL 8

Expert Comment

by:MrRobin
ID: 9653390
What you could try is using the Relpace function on the SQL of the query...

Sub PlotChart(PeriodBegin As Date, PeriodEnd As Date)
    Dim strSQL As String
   
    strSQL = CurrentDb.QueryDefs("QueryName").SQL
    strSQL = Replace(strSQL, "PeriodBegin", "#" & PeriodBegin & "#")
    strSQL = Replace(strSQL, "PeriodEnd", "#" & PeriodEnd & "#")

    Chart.RowSource = strSQL
End Sub
0
 
LVL 8

Expert Comment

by:MrRobin
ID: 9653425
Replace is only available in Access 2000 and later - it may not be exactly what you're after but it's the only way I can think of using of the query for a chart - it does only use the query's SQL rather than the precompiled query itself but it should be OK... maybe!
0
 

Accepted Solution

by:
LiteralLight earned 0 total points
ID: 9656907
MrRobin, Thanks for you help and the ideas.

I have stumbled upon a soultion that is much easier to implement. I declared public variables in FormA with the same name as the query parameters and in the onOpen event for FormA I assign them the value of the input controls in FormB. The query now sees these, I guess because of inheritance. It seems to work fine.
0
 
LVL 8

Expert Comment

by:MrRobin
ID: 9657754
Not quite sure how that's working!  I've tried what you have described and I still get asked for the parameter - all sounds very interesting though - is there anything else you've done?  Whatever it is I'm pretty sure it doesn't have anything to do with inheritance.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question