Query Parameters passed from Form

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?

LiteralLightAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MrRobinCommented:
I think you can specify the values of parameters in the parameters collection of the query definition...

  CurrentDb.QueryDefs("QueryName").Parameters("PeriodBegin") = x
0
Mike EghtebasDatabase and Application DeveloperCommented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LiteralLightAuthor Commented:
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
LiteralLightAuthor Commented:
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
MrRobinCommented:
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
MrRobinCommented:
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
LiteralLightAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MrRobinCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.