Help using form variables on queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Help using form variables on queries

Author

Topic

ramonmch
Starting Member

5 Posts

Posted - 2004-05-24 : 14:05:05

If I have a form in an access project (adp), when we create a query with a where clause like:

SELECT *
FROM Table1
WHERE Column1=Form_Form1.myvar

this should do in access but it does not works the same with sql server.

What Am I doing wrong ?

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-24 : 14:24:22

How do you connect to sql server?

Brett

8-)

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-24 : 20:19:18

ADP's are different in that all the stored queries you came to know in normal Access db's are replaced with SQL Server stored procedures and views. As such, SQL Server has no idea what Form_Form1.myvar is.

If you're creating a query in the code, you should use:
sql = "SELECT * FROM Table1 WHERE Column1 = '" & Form1.myVar & "'"

Then you can execute it using the CurrentProject.Connection object

But - you can use a stored procedure too. Create the SP with an input variable called @myvar. If you run this while the form is open, Access will resolve that the control myvar maps to the input variable @myvar.

HTH

Tim

ramonmch
Starting Member

5 Posts

Posted - 2004-05-25 : 08:24:39

Thanks Tim.

One more thing. What you are saying is that if there is a control within my form FORM1 named MYVAR I do not have to declare it PUBLIC since I am calling the query from within the form. The sql sintax for the WHERE clause should be:

WHERE column1="'" & @MIVAR & "'"

Please advise if sintax is correct. Thanks

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-05-25 : 08:31:41

To use a variable to reference a value on another form you will have to pass that variable to your SP or Function. You do this by using the input parameters line in the form properties.

The Line would look like this:

@myvar = [Forms]![Form1]![myvar]

Your SP would look like this

Create Stored procedure dbo.myprocedure (@myvar Varchar (20))
SELECT *
FROM Table1
WHERE Column1= @MyVar

Jim
Users <> Logic

ramonmch
Starting Member

5 Posts

Posted - 2004-05-25 : 11:16:27

Thanks Jim

It worked fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources