Solved

Query Parameters passed from Form

Posted on 2003-10-29
10
414 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 33

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 33

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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now