Using Stored Proc with parms in a form

  • Thread starter Thread starter Jonathan Blitz
  • Start date Start date
J

Jonathan Blitz

I am using Access with SQLServer.

I can use a SP as the recordsource for a form.
Problem is that if the SP requires parameters then a window pops up for each
parameter.

I want to give my own window and then supply the values so that the user
gets a proper selection screen.

How do I do this?

I tried running the command myself on the Form_Open event and setting the
recordset to the one I get but this didn't work - it calaims it is missing
fields.

Jonathan Blitz
AnyKey Limited
 
Jonathan

Assuming that you are using a MDB and not a ADB try this.

Bind the form to a passthru query. Then write a little code to delete and
re-create the passthru query from the form load event. Code might look
like:

' Delete It
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = "qdfYourQuery"
CurrentDb.QueryDefs.Delete qdf.Name
End If
Next qdf
' Recreate It
Set qdf = CurrentDb.CreateQueryDef("qdfYourQuery")
qdf.Connect = "Your Valid Connection string for the Sql Database"
qdf.SQL = "Execute YourProc" _
& "@ParamYourFirstParam= '" & strYourYourFirstParam & "' " _
& "@ParamYourSecondParam=" & lngYourSecondParam & " " _
& "@ParamYourLastParam=" & lngYourLastParam
qdf.Close
Set qdf = Nothing
Me.RecordSource = "qdfYourQuery"

This should work with any version of Sql Server and all MDB's from Access 97
and greater.

Ron W
 
Ooooopppppsss...

qdf.SQL = "Execute YourProc" _
& "@ParamYourFirstParam= '" & strYourYourFirstParam & "' " _
& "@ParamYourSecondParam=" & lngYourSecondParam & " " _
& "@ParamYourLastParam=" & lngYourLastParam

Should be:

qdf.SQL = "Execute YourProc" _
& "@ParamYourFirstParam= '" & strYourYourFirstParam & "', " _
& "@ParamYourSecondParam=" & lngYourSecondParam & ", " _
& "@ParamYourLastParam=" & lngYourLastParam

Need to include the comma delimiters between params. Really sorry about
that. :-(

Ron W
 
I'm using an ADP project.
Will it still work?

Ron Weiner said:
Jonathan

Assuming that you are using a MDB and not a ADB try this.

Bind the form to a passthru query. Then write a little code to delete and
re-create the passthru query from the form load event. Code might look
like:

' Delete It
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = "qdfYourQuery"
CurrentDb.QueryDefs.Delete qdf.Name
End If
Next qdf
' Recreate It
Set qdf = CurrentDb.CreateQueryDef("qdfYourQuery")
qdf.Connect = "Your Valid Connection string for the Sql Database"
qdf.SQL = "Execute YourProc" _
& "@ParamYourFirstParam= '" & strYourYourFirstParam & "' " _
& "@ParamYourSecondParam=" & lngYourSecondParam & " " _
& "@ParamYourLastParam=" & lngYourLastParam
qdf.Close
Set qdf = Nothing
Me.RecordSource = "qdfYourQuery"

This should work with any version of Sql Server and all MDB's from Access 97
and greater.

Ron W
Click to expand...
 
Hey Ron!

This is just like something I need... One quick question... I can use
MS Access to create a pass-through query, but the wizard doesn't seem
to make the connect string visible... the values are entered into a
wizard form... How can I learn what the specific syntax of my
successfull pass-through connect string is?

Thanks in advance!

- Ed
 
Open the pass-through query in Design mode then look at the query's
properties (View | Properties from the menu bar). The second property (ODBC
Connect str) is what you're looking for.
 
Thank you very much - just right-clicking on the query in the list
view of all queries gives you properties... but not THESE
properties....

Gotta love consistency!

Thanks again!
 
Back
Top