UPDATE Query -

[Phil_b]

Backend: SQL Server
Front End: MS Access

Just a small problem with a stored procedure im trying to create:

UPDATE    tblOrders
SET              active = 1
WHERE     (invNumber = 'Forms!frmMainsub1!oID')

The above I believe is correct for updating all records to active with an invoice number of:

'Forms!frmMainsub1!oID'

However, after I try to save it I get the following error:

The paramenter is incorrect

Not very descriptive and after searching google and these forums I have still yet to find a solution. Help appreciated.

Phil

 

[reclusivemonkey]

Backend: SQL Server
Front End: MS Access

Just a small problem with a stored procedure im trying to create:

UPDATE    tblOrders
SET              active = 1
WHERE     (invNumber = 'Forms!frmMainsub1!oID')

The above isn't valid SQL. You need to turn Forms!frmMainsub1!oID into a variable first, then pass this to the sql. Access knows what Forms!frmMainsub1!oID is, but SQL doesn't. I *think* thats your problem.

 

[Phil_b]

The above isn't valid SQL. You need to turn Forms!frmMainsub1!oID into a variable first, then pass this to the sql. Access knows what Forms!frmMainsub1!oID is, but SQL doesn't. I *think* thats your problem.

that makes sence, i thought that might have been the problem! Does that mean I cannot then do this through a stored procedure but must do it via VBA?

Im unsure as to how I can pass a variable to a stored procedure ...


Phil

 

[reclusivemonkey]

I honestly couldn't tell you whether or not you could do it as a stored proceedure, but you can definitely do it via VBA with the above method. I am home now, but if you are still stuck tomorrow I can post you some code that might help. In the mean time, search the forum for ADODB connections, that should point you in the right direction.

 

[Phil_b]

thanks for the info. I have average knowledge of using SQL queries within my code (modules et al) although given im now using SQL server rather than Jet 4.0 I thought I would try a more clean method and use stored procedures.

Now im basically trying to return a row based on a record ID. I made a public function and then added that to the end of the SQL query in the stored procedure but it does not recognise the function (read up on this and it seems there are a few people with this problem). They were informed to make a table on the SQL server to store this ID in and then refer to it that way... is this the best solution? Seems very inefficient to do it this way.

Phil

 

[Phil_b]

fixed !!!!

Took me the best part of 7 hours solidly working on it but ive now fixed it. You can add parameter to a stored procedure and you can then pass a value to this. Took a very long time to figure this our (as it infact was only editable server side).