Query Criteria

[Guest]

In an Access Database Project, Im having trouble getting a View to query data
based off of a value from a form. My ultimate goal is to run a report off of
this View. For example if Form1 FieldLocation = 256, I want the View to find
all items in Location 256. Below is the View I want to use for this(I
understand that some of the field names with # is not the best way to name a
field).

SELECT dbo.tbl_HoldOrderDetails_View.ITEM#,
dbo.tbl_HoldOrderDetails_View.[DESC], dbo.tbl_HoldOrderDetails_View.[CASE],
dbo.tbl_HoldOrderDetails_View.BTL,
dbo.tbl_HoldOrderDetails_View.LOCATION, dbo.tblInvCount.CurLoc,
dbo.tblInvCount.BoxId,
dbo.tbl_HoldOrderDetails_View.LINE#,
dbo.tbl_HoldOrderDetails_View.[P/UP] AS PU,
dbo.tbl_HoldOrderDetails_View.[P/ASIDE] AS PA,
dbo.tbl_HoldOrderHeader.ORDER#,
dbo.tbl_HoldOrderHeader.CustomerCode,
MSData.dbo.tblCUCustomer.CustomerLastName,
MSData.dbo.tblCUCustomer.CustomerFirstName
FROM dbo.tblInvCount LEFT OUTER JOIN
dbo.tbl_HoldOrderDetails_View ON dbo.tblInvCount.BoxId
= dbo.tbl_HoldOrderDetails_View.LINE# LEFT OUTER JOIN
MSData.dbo.tblCUCustomer RIGHT OUTER JOIN
dbo.tbl_HoldOrderHeader ON
MSData.dbo.tblCUCustomer.CustomerCode = dbo.tbl_HoldOrderHeader.CustomerCode
ON
dbo.tbl_HoldOrderDetails_View.ORDER# =
dbo.tbl_HoldOrderHeader.ORDER#

Is this something that needs to be done with VBA?

 

[Larry Linson]

You can create all the SQL in VBA, and in the Open event of the Report, set
it as the Record Source.

You could create the View in SQL Server without the criteria, store it, and
refer to that as Record Source, setting the criteria in the WhereCondition
of the DoCmd.OpenReport.

Unless you have _massive_ amounts of data, I suspect the performance
difference will not be obvious to the user sitting at the monitor -- my
guess is that, because of the criteria being different for each run, that
all or some of the query preparation will have to be redone, no matter which
approach you take.

Larry Linson
Microsoft Access MVP