Passing Parameters to SQL through VBA

[sross81]

Hello,

I have been struggling with this for days. I finally just got the stored procedure to execute through vba and to populate a form in my database's record source property. I started first with just getting it to execute a stored procedure with no parameters because I was having trouble passing them. NOw I know it executes, but I need to understand how to pass the parameters.

Ultimate I want to pass in a number, then 2 dates. So it would be exec sp_GetAMIWorklist 1, '07/01/2012','09/30/2012' if i just execute straight from SSMS. I just don't understand how to know when I put the right quotes and how to concatenate it all together. Does anyone have any good refrences?

Here is my code: (I made the line red where I have trouble)

Private Sub Form_Load()
Dim db As DAO.Database
Dim rstAMIWorklist As DAO.Recordset

' Set database variable to current database.
Set db = CurrentDb

' Open QueryDef object.
Set qdf = db.QueryDefs("sp_GetAMIWorklist")

'HERE IS MY PROBLEM LINE

qdf.SQL = "EXEC sp_GetAMIWorklist " & Forms!frmNewMainMenuSelections!cboFacility & "" & '"& Forms!frmNewMainMenuSelections!txtStartDate & "" &
& Forms!frmNewMainMenuSelections!txtEndDate & ""

qdf.ReturnsRecords = True
'Open Recordset object.
Set rstAMIWorklist = qdf.OpenRecordset()
rstAMIWorklist.MoveFirst
Set Me.Recordset = rstAMIWorklist

 

[pbaldy]

Try this to see what the finished SQL looks like. I think you'll see where you're going wrong.

http://www.baldyweb.com/ImmediateWindow.htm

 

[sross81]

This is how I ended up solving the problem. The actual stored procedure is referenced as a passed through query in my database and it seems to have to be there for it to work. This gets me the results I want. I am sure there are better ways to do it so that i don't have to store the pass through query in the database. I just haven't figured it out yet. This is my first time doing this ever .

The pass through query should be saved with the name of the sp you will reference below. In the pass through it should have exec sp_GetWorklistAMI (or your name).

I would like ideas on if I should be closing this recordset or when? I tried doing it in the unload event, but I can't reference the open recordset because it is not declared there so I am confused about that.


Private Sub Form_Load()
'Declare needed variables and collections
Dim db As DAO.Database
Dim rstAMIWorklist As DAO.Recordset
Dim strFacilityID As Integer
Dim strAMISQLString As String
Dim strStartDate As String
Dim strEndDate As String
'Assign values to variables based on users parameter selections on the main menu.
strFacilityID = Forms!frm_NewMainMenuSelections!cboFacility
strStartDate = Forms!frm_NewMainMenuSelections!txtStartDate
strEndDate = Forms!frm_NewMainMenuSelections!txtEndDate
'Build SQL string to pass into sp_GetWorklistAMI based on user input parameters.
strAMISQLString = "EXEC sp_GETAMIWorklist " & strFacilityID & "," & "'" & strStartDate & "'" & "," & "'" & strEndDate & "'"
' Set database variable to current database.
Set db = CurrentDb
'Open QueryDef object.
Set qdf = db.QueryDefs("sp_GetAMIWorklist")
'Assign query def sql command to the sql string built form user selections.
qdf.SQL = strAMISQLString
'Allow the stored procedure to return a result set to be bound.
qdf.ReturnsRecords = True
'Open Recordset object as a query def.
Set rstAMIWorklist = qdf.OpenRecordset()
'Move to the first record of the record set.
rstAMIWorklist.MoveFirst
'Cound the current form that is loading to the recordset returned by the stored procedure containing only user's selection.
Set Me.Recordset = rstAMIWorklist
'Maximize the form.
DoCmd.Maximize
End Sub