[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

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?

0
LiteralLight
Asked:
LiteralLight
  • 4
  • 3
  • 2
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now