Reference a form in SQL Server

[jgoodman00]

Using SQL Server 2000 as a datasource, & an Access XP project as the client app, how would the folowing query need to be rewritten in order to make it work in SQL??

UPDATE tblHierarchy SET tblHierarchy.Priority = [Forms]![SelectCriteria]![lstPriority];

That is the existing query but this does not work as a stored procedure in SQL, because it doesn't understand the reference to the form. Does anyone know how to do this??
James Goodman
j.goodman00@btinternet.com

 

[rajeevnandanmishra]

Hi James,
I didn't understand your requirement correctly. But if you want to create a stored procedure in SQL to accomplish this task then:
1. Create a procedure in SQL server as
create procedure updHierarchy (@priority varchar(50)) as
UPDATE tblhierarchy SET priority=@priority
2. Call this procedure in ACCESS by passing your update value as parameter.

 

[jgoodman00]

I have managed to do this, but that value needs to be passed in from a form. It is passing this value which it seems to struggle with, because using the @ symbol simply makes it ask for a parameter. This suggests it is working correctly, but that parameter needs to be filled by the value from a control on a form.

As in:

Form Name: SelectCriteria
ControlName: lstPriority
Target Table: tblHierarchy
Target Field: Priority


So how do I get the value from lstPriority in form SelectCriteria into the stored procedure without manually entering it into the stored procedure each time it runs??? James Goodman
j.goodman00@btinternet.com

 

[rajeevnandanmishra]

Hi James,
OK! Now try to do following. I am not sure how it is going to work on Access XP. But it is working on Access 2000.
In the click event of save button write as following:

DIM sqlstr AS STRING
'If Priority is integer
sqlstr="UPDATE tblHierarchy SET Priority =" & _
ME.lstPriority.VALUE
'If Priority is string
sqlstr="UPDATE tblHierarchy SET Priority ='" & _
ME.lstPriority.VALUE & "' "
DOCMD.RUNSQL(sqlstr)



Let me know if it works!!!

 

[jgoodman00]

I have already something approximating to this & it does work. However, in an attempt to save memory I was trying to write as little code as possible for each form, & only have the code activate when it is required....

James Goodman
j.goodman00@btinternet.com

 

[rajeevnandanmishra]

Great!!! You had found the answer by yourself!

 

[jgoodman00]

Well I had, but I was hoping it was possible to pass that parameter directly into the stored procedure..... James Goodman
j.goodman00@btinternet.com

 

[rajeevnandanmishra]

Hi James,
Just have a look on this code. If you think it useful, incorporate it.
In the Save Button of your form write following

Dim mydb As Database, myq As QueryDef, strsql as String
Set mydb = CurrentDb()
Set myq = mydb.CreateQueryDef("&quot
'Correct Connect String depending on your server e.t.c
myq.connect = "ODBC;DSN=sql;UID=sa;PWD=;DATABASE=pubs"
myq.returnsrecords = False
strsql = "updHierarchy '" & ME.lstPriority.VALUE & "' "
myq.sql = strsql
myq.Execute
myq.Close
mydb.Close

I hope this will give you idea of executing stored procedures with parameters.