Solved

Query Parameters passed from Form

Posted on 2003-10-29
10
428 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
[X]
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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

690 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