Passing Form references to a Stored Proc

I have a stored procedure on SQLServer which requires a variable. This variable is the contents of a textbox on a form. I have tried many ways to do this (in code and a pass-through query but nothing works.

This is the contents of my ps query:-

EXEC procMyProc '" & Forms!myRef & "'"

Can anyone show me how to do this in either code with querydefs or in a ps query. BTW RRR has been trying to help me - Thanks RRR but I'm still struggling.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What version of Access are you using?  If you are using 97 and the DAO object library, try the following:
Private Sub CallQDsp()
    Dim qd As QueryDef
    Dim sConnect As String
    Dim sSQL As String, sServer As String
    Dim sLoginID As String, sPassword As String
    Dim cn As Connection
    Dim ws As Workspace
    sLoginID = "sa"
    sPassword = vbNullString
    sServer = "DAREK"
    'The type parameter sets the workspace to ODBCDirect
    Set ws = DBEngine.CreateWorkspace("NewODBCDirectWS", sLoginID, _
                sPassword, dbUseODBC)

    'Set the default Workspace type to ODBCDirect
    DBEngine.DefaultType = dbUseODBC
    'Define the DSN-less connection
    sConnect = "ODBC;Driver=SQL Server;UID=" & sLoginID & _
        ";PWD=" & sPassword & _
        ";Server=" & sServer & _
    'Open the new connection
    Set cn = ws.OpenConnection("", dbDriverComplete, False, sConnect)
    'Create the new procedure
    sSQL = "Create Proc CountStateRows " _
        & "(@state char(2), @rows Int Output) As " _
        & "Select @rows = Count(*) From authors Where state = @state"
    cn.Execute sSQL
    'Call the procedure
    sSQL = "{ Call CountStateRows (? ,?) }"
    Set qd = cn.CreateQueryDef("", sSQL)
    qd.Parameters(0).Value = SOME TEXT BOX ON A FORM
    qd.Parameters(1).Direction = dbParamOutput
    MsgBox "Number of rows: " & qd.Parameters(1).Value
End Sub

....that is, the above uses ODBCDirect to call a stored procedure that in this case returns a value (an output parameter).

Let me know if this is what you are looking for or if it is something else.  That is, from your question I am not sure what you are trying to accomplish exactly.
Tim_KemptonAuthor Commented:
Could this be done easier with a pass-through query?  BTW I'm using Access 97 and DAO
I suppose, however I personally use the above ODBCDirect wherever possible to call stored procedures.  The reason? Well, my stored procedure could have 10 output parameters and also return a resultset... ODBCDirect provides me with this ability.

Anyway, to use pass-through queries try the following:
1 - Create a pass-through query in your queries tab called "TempForSPs".  Place anything you want in the SQL (it does not matter what you put here at all)

2 - Create a new form and place on it a command button.

3 - On the click event of the command button, try the following code... again I do not know exactly what your stored procedure is trying to accomplish so am demonstrating a couple of things here.

Dim rs As Recordset

CurrentDb.QueryDefs("TempForSPs").SQL = "usp_RowCount"
DoCmd.OpenQuery "TempForSPs", acViewNormal
Set rs = CurrentDb.QueryDefs("TempForSPs").OpenRecordset
With rs
    Do Until .EOF
        Debug.Print rs.Fields(1)
End With

Tim_KemptonAuthor Commented:
I think the ODBCDirect method is the best way. I asked this question because I was trying to populate forms and subforms the quickest way possible when using SQLServer with upto 100 users using Access as the front-end.
So do you need any more information or have I answered your question enough?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.