Thanks Jim. I'm curious what you mean by "not appending to the collection". Here's some more information. My code looks something like this:
Private Sub Form_Load()
strSql = "EXEC dbo.GPIA_GetPartnerTransactions @show_whole_year=" & show_whole_year & ", @as_of_date = '" & as_of_date & "'"
'Me.RecordSource = strSql
Set Me.Recordset = ExecSQLRS(strSql, strErrorDesc, intError)
End Sub
The function ExecSQLRS is one that I made which returns a recordset using ADO. I tried using both that and the Me.RecordSource above. I've tried all combinations of supplying inputparamters, not supplying them, etc. Another interesting twist: it only prompts me when my procedure has more than one parameter.
When I do the exact same thing but with only one parameter it works fine. When I add a second parameter, it seems Access keeps automatically adding the following to the InputParameters and prompting me:
InputParameters: ? [AllowDefault] = as_of_date
When I step through in debug mode it's odd - the parameter prompt comes after my recordset or recordsource assignment. It seems like Access is trying to do something on its own even after I assign my own recordset.
Thanks again,
H
Ok well again not all the code is there to look at but you are basically asking to return a recordset to the form and there are various ways to do that. The simplest way is to (early bind) set the recordsource of the subform to the name of the store procedure itself with the owner predicate which in your case would simply be
dbo.GPIA_GetPartnerTransactions
In this case the inputparameters property of the form would be the provider of the parameters for the procedure to which the form is BOUND. You would place these parameters into that property.FORM......DESIGN....View....Properties... InputParameters
I do not know what the datatypes of your parameter values are.... for me sat here they could be numeric or varchar or indeed anything else but I am assuming show_whole_year might be a varchar(3) a YES value or something like that. I will also assume that the as_of_date parameter might be an actual date value you are wanting to pass in.
The InputParameters of the form could then be set by you in the ON_LOAD event if you so wished so that the stored procedure has something to work with when it kicks in when the form actually opens
Without knowing the datatypes and your system its difficult therefore I am going to give you an example of something related to one of MY forms which is bound to a stored procedure and which also requires parameters and where I SET those parameters at runtime (the values of which are being passed from another form which is currently open in memory
My form has its recordsource set simply to
dbo.usp_qryFetchList
My form to open relies on a date and the value of a particular caption from one of 26 toggle buttons in an option group located on the other 'open' form (a bit like a telephone directory if you get me where I use classic alpha filter buttons. you can see the example of this in Northwind)
The on load event of my form has this code
-
-
myparams = "@datefrom='" & Format(Forms!frmMainMenu!DateFromCrit, "mm/dd/yyyy") & "'"
-
myparams = myparams & ",@dateto='" & Format(Forms!frmMainMenu!DateToCrit, "mm/dd/yyyy") & "'"
-
myparams = myparams & ",@grpazfilter='" & Forms!frmTelephoneList("Btn" + Format$(grpazfilter)).Caption & "'"
-
Me.InputParameters = myparams
-
Now this is all well and good because the form is an early bind and I want the functionality of continuous form. If YOUR form is something you want to remain as UNBOUND where you populate the controls using some recordset method as I am assuming might be the case given you have some kind of function there
(ExecSQLRS(strSql, strErrorDesc, intError)) then the approach would be different and you would want to return an ADO recordset based on parameters passed in code then you are going to need to look at CREATEPARAMETER method
Below is the area that you need to be looking look at to return the ADO recordset if that is indeed what you are doing by your function? It is THIS area that I was talkiing about when I referred to APPENDING to the collection (its easy to create a parameter and forget to append then wonder why nothing works if you get me)
-
-
Private CN As ADODB.Connection
-
Private CMD As ADODB.Command
-
Private PRM As ADODB.Parameter
-
Private RS As ADODB.Recordset
-
Set PRM = New ADODB.Parameter
-
Set PRM = CMD.CreateParameter("TheNameOfYourFirstParam", adInteger, adParamInput, , TheFirstVariableToPassIn)
-
CMD.Parameters.Append PRM
-
Set PRM = CMD.CreateParameter("TheNameOfYourSecondParam", adVarChar, adParamInput, 11, TheSecondVariableToPassIn)
-
CMD.Parameters.Append PRM
-
Set RS = New ADODB.Recordset
-
Set RS.Source = CMD
-
RS.CursorType = adOpenStatic
-
RS.LockType = adLockOptimistic
-
RS.Open
-
The above is only an 'example' of where you need to go obviously the values contained in it are representative like the 11 in the second value is datasize and whether you need a client side or server side cursor for whatever reason you may or may not have I havent a clue
Hope this helps you but without viewing everything you have there I have to guess this end
Most of the time I stay away from too much convaluted coding in favour of binding because its simple to do, works (never failed me) and unless specifically needed why complicate matters SQL Server is way fast as it is when properly indexed and optimised
Regards
Jim
6
