Cannot get values from Forms - Access 2007 adp Project with SQL

[Ian Bateman]

When I construct a query using Access, I can get data from a form by doing
something like ExchRate:Forms!frm_Menu.TodaysRate

Now I am trying to do this with a Query (View) in an Access adp project and
I cannot see how to do it. I am trying to produce a stock valuation report
with the values converted from Euros to UK£ from a figure entered on the form.

Many Thanks
Ian

 

[Maurice]

Hi Ian,

I can't direct you to the answer in the view you are creating, but maybe
this is another aproach you can use. If you put your parameters in the
properties of the form (see input parameters at the bottom of the properties)
you can refer to them via a SQL-statement you use in the code of the form.
That's how I worked around the initial problem. I know it's not a solution
you asked for but maybe it's an alternative you can use...

 

[Gary Walter]

FYI to see an illustration of the method
proposed by Maurice, just look at sample
NorthWindCS.adp

rptEmployeeSalesByCountryFormFilter

has *stored procedure* record source of

"Employee Sales by Country"

SELECT
dbo.Employees.Country,
dbo.Employees.LastName,
dbo.Employees.FirstName,
dbrders.ShippedDate,
dbrders.OrderID,
dbo.[Order Subtotals].Subtotal AS SaleAmount
FROM
dbo.Employees
INNER JOIN
dbrders
INNER JOIN
dbo.[Order Subtotals]
ON
dbrders.OrderID = dbo.[Order Subtotals].OrderID
ON
dbo.Employees.EmployeeID = dbrders.EmployeeID
WHERE
(dbrders.ShippedDate
BETWEEN
@Beginning_Date
AND
@Ending_Date)

sure, the parameters are used in WHERE clause
to filter, but they just as easily could be in SELECT
clause...

the "Input Parameters" property of the report is

@Beginning_date datetime = Forms!frmDateRange!txtBeginDate,
@Ending_date datetime = Forms!frmDateRange!txtEndDate

and, when you open frmDateRange,
enter values for dates,
and click on command button to preview report,
the parameters are "automatically taken care of "
when the report is opened...

good luck,

gary