December 14, 2007 at 3:28 pm
UPDATE dbo_MBA_Grower_Lot SET dbo_MBA_Grower_Lot.CLOSE_FLAG = '1'
WHERE (((dbo_MBA_Grower_Lot.JOB_NUMBER)=Forms!F_EDIT_CLOSED!lst_Job_Number.BoundColumn(0)) AND ((dbo_MBA_Grower_Lot.PROJECTED_DATE)=Forms!F_EDIT_CLOSED!lst_Job_Number.BoundColumn(1)));
I had to write an access application for a user where they will update a flag. Very simple. But, the table has an index on the job number and projected date see above. So, I created this pass through and I need to include the parameters from the form.
I get the error incorrect syntax near '!' - how do I pass thos variables?
December 14, 2007 at 4:13 pm
MS Access Pass-through queries don't get processed in Access, so the native parameter binding doesn't work. (They're called "pass-through" because that SQL is not process locally, but is "passed through" (or sent up) to SQL server to process, and only the ANSWER comes back)
You have a few choices:
- do someting to "simulate" parameters, like this article describes, http://support.microsoft.com/kb/q131534/[/url]
- read up on how to use an ADO Command object.
Either way - you really should build that update statement into a SQL stored procedure that validates the parameters, instead of that dynamic SQL. That way - the whole syntax used in either of the two methods above will be MUCH simpler.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply