control on a form as a criteria on views 1

[doraemon18]

how do i create a view wherein the criteria will be coming from my form's textbox?
anyone?
thanx in advance

 

[hmscott]

In SQL, views cannot accept parameters (unlike queries in MS Access). To accomplish what you are trying to do, you will need to create a stored procedure. For example, consider the following for the pubs database:

CREATE PROC spSalesForStore (
@iStore as int
)

AS

SELECT *
FROM sales
WHERE stor_id = @iStore

in your code (sample for VB), declare an ADOCommand object, set the .CommandText to the name of your stored procedure. Then:

oComm.Parameters.Refresh
oComm.Parameters("@iStore") = txtStoreID

Set oRS = oComm.Execute