jdasilva (5/6/2014)
How does SQL get the value of the variable from the form in access?In the example I am working on, I am polling the data for specific info for each person in an office. Currently, since the query is in the access file, I check he current form that calls up the query. In that form is a text field that holds the current office code. Let's say that it is in [Forms]![aform]![loc]. How do I code my SQL statement on the SQL server to see this value?
Or are you saying YES to my statement about making a table that holds the current office location code? If this is what you are suggesting, what happens when multiple offices try and access this query at the same time?
SQL Server cannot see your form. However, your form is where you will execute the stored procedure. From Access it would be something like this:
cmd.CommandText = "MyStoredProc"
dim parm as New ADODB.Parameter
set parm = cmd.CreateParameter("LocID", adInteger)
cmd.Parameters.Append parm
cmd.Parameters("LocID") = [Forms]![aform]![loc]
Set rs = cmd.Execute()
I would recommend NOT using some sort of table to hold the current location. That is not a good approach when you can just use parameters.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/