help forms

The below stored procedure works. However, I am trying to use a text box
from a temp form for the where clause.
WHERE Transactions.TransactionID = [forms]![form1]![text0]
I even tried changing first line:
Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
And changed last line to:
WHERE Transactions.TransactionID = @.TID
So far, I haven't been able to have this parameter work from a temp form
with the parameter typed into the text0 box.
any help here? I get ado error near "!" or something to that affect.

this below is what does work fine:

Alter PROCEDURE S3 @.TID varchar (255)
AS
SELECT Transactions.TransactionID, Transactions.AccountID,
Transactions.TransactionNumber, Transactions.TransactionDate,
Transactions.TransactionDescription, Transactions.WithdrawalAmount,
Transactions.DepositAmount
FROM Transactions
WHERE Transactions.TransactionID = @.TID"JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
news:sHfGc.196809$Gx4.122482@.bgtnsc04-news.ops.worldnet.att.net...
> The below stored procedure works. However, I am trying to use a text box
> from a temp form for the where clause.
> WHERE Transactions.TransactionID = [forms]![form1]![text0]
> I even tried changing first line:
> Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
> And changed last line to:
> WHERE Transactions.TransactionID = @.TID
> So far, I haven't been able to have this parameter work from a temp form
> with the parameter typed into the text0 box.
> any help here? I get ado error near "!" or something to that affect.
> this below is what does work fine:
> Alter PROCEDURE S3 @.TID varchar (255)
> AS
> SELECT Transactions.TransactionID, Transactions.AccountID,
> Transactions.TransactionNumber, Transactions.TransactionDate,
> Transactions.TransactionDescription, Transactions.WithdrawalAmount,
> Transactions.DepositAmount
> FROM Transactions
> WHERE Transactions.TransactionID = @.TID

SQL Server is a pure server application - it doesn't know anything about
forms or other presentation of data. You need to capture the parameter value
in your client application (Access?), then pass it to the stored procedure
using a client library such as ADO. If you're unsure how to go about this,
you might want to post in a forum for whichever client application you have.

Simon|||I thought sql server was about sql. You're saying there's no way to pass
data from a form to it as a parameter?
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40e99857$1_2@.news.bluewin.ch...
> "JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
> news:sHfGc.196809$Gx4.122482@.bgtnsc04-news.ops.worldnet.att.net...
> > The below stored procedure works. However, I am trying to use a text
box
> > from a temp form for the where clause.
> > WHERE Transactions.TransactionID = [forms]![form1]![text0]
> > I even tried changing first line:
> > Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
> > And changed last line to:
> > WHERE Transactions.TransactionID = @.TID
> > So far, I haven't been able to have this parameter work from a temp form
> > with the parameter typed into the text0 box.
> > any help here? I get ado error near "!" or something to that affect.
> > this below is what does work fine:
> > Alter PROCEDURE S3 @.TID varchar (255)
> > AS
> > SELECT Transactions.TransactionID, Transactions.AccountID,
> > Transactions.TransactionNumber, Transactions.TransactionDate,
> > Transactions.TransactionDescription, Transactions.WithdrawalAmount,
> > Transactions.DepositAmount
> > FROM Transactions
> > WHERE Transactions.TransactionID = @.TID
> SQL Server is a pure server application - it doesn't know anything about
> forms or other presentation of data. You need to capture the parameter
value
> in your client application (Access?), then pass it to the stored procedure
> using a client library such as ADO. If you're unsure how to go about this,
> you might want to post in a forum for whichever client application you
have.
> Simon|||JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> I thought sql server was about sql. You're saying there's no way to pass
> data from a form to it as a parameter?

You are right SQL Server is about SQL, but forms are not SQL per se.

The difference between Access and SQL Server is that Access is a one-tier
application. That is, the query language knows about the GUI and can
interact with it directly. This makes it very easy to write application,
but it comes with a price: it does not scale well, and is best suited for
single-user applications. Also, since the application accesses the database
directly, that means a lot of network traffic if the database is on the
other machine.

SQL Server is intended for applications with two or more tiers. That is,
there is a client on one machine, which passes queries or remote procedure
calls to the server which sends data back. Since machine that hosts the
database does not have do care about drawing GUI:s it can concentrate
on managing the data. And since the server program works on the server,
only the data that the user actually requests has to travel the network.

There are of course several ways to pass a parameter from a form to SQL
Server, but the point is that once the parameter reaches SQL Server, SQL
Server has no idea where it came from. It could be from an input form, it
could have been a read from a file, or it could be a constant in the calling
client program.

To talk to SQL Server from a client, there is a whole range of client
libraries to choose from. From Access the most commonly used today is
ADO. But really how you this best in Access, is probably a question
for comp.databases.ms-access.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp