Unanswered: Setting form recordsource to a stored procedure at runtime using vba
I have a form that I use for adding and viewing records in an access adp project.
I have a button that opens the form for adding records in a dataentry format and another button that opens the form for viewing and shows a specific record.
The problem is that I need to change the recordsource of the form at runtime.
If the form is opened using the Add button, I set the recordsource to a View and then open the form. I use the OpnArgs property of the form to pass the datastring (view name) and on Load of the form I set the record source to Me.OpenArgs. Works fine for a view but not for a stored procedure.
If the form is opened for viewing I have to bind it to a parameteric stored procedure, but if I set the datastring as somthing like "SP_ShowDetails @p1='p1',@p2='p2' " the form won't recognize the difference between the SP and the parameters.
The other solution is to set all the properties of the form before opening it. In other words I have to instantiate a form, set the record source to "dbo.SP_ShowDetail" and set the InputParameters of the form dynamically.
The problem: I can't set the properties of the form if it is not open, in other words I have to open the form then set the properties.
Is there any way to intantiate a form when its closed?
(by the way, I know we can open a form and go to a specific record using VBA and the where condition or using a recordset clone, but that will be dificult for more than one parameter and also the processing is going to be on the client, which means the whole recordset is sent to the client over the network and I don't want to do that. I rather do the filtering on the server)
Another problem: I thought if I open a form in data entry format, it won't load the data. But I monitored the SQL server with profiler, and each time I open a form in a data entry format Access sends a select * command to the SQL Server... I can't understand why It would send a select * if the form is in data entry format. That is unnecessaryload on the SQL Server. What is the solution?!
The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.