Setting form recordsource to a stored procedure at runtime using vba

[AppDev76]

Hi,
I have a form that I use for adding and viewing records.
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)

Thanks

 

[cmmrfrds]

What version of Access? An ADP or MDB app?

 

[AppDev76]

Access 2003, adp project.

 

[cmmrfrds]

Are you using the same form for a View and a Stored Procedure, and flopping between them.

 

[AppDev76]

Thanks for your reply.
Yes, I use the same form, but I open it either in data entry format or add format, and I close it before opening it in another format.

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?!

 

[cmmrfrds]

It might be a lot easier to make separate forms for the different data sources. One for the stored procedure and another for the view, then it should be easy to set the parameters in the parameter property of the form.

When you see the Select *, what is in the where clause?

 

[AppDev76]

1.It might be easy for the initial creation, but the maintenance is going to be difficult.
2.There is no where clause for the select. Its just a select * on the Server. The form load the datasource by default

 

[AppDev76]

Here is the solution to the first problem:
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.

>> When setting the recordSource property of a form using VBA and the stored procedure has parameters and you are passing the parameters in a string you need to use the "EXEC":
frm.recordSource="Exec SP_Procedure " & p1 "," & p2

 

[cmmrfrds]

Here is a thread701-871171 with a little different twist.

When you go production you will need to prefix all objects with dbo. unless the users/clients will be database owners.
Exec dbo.SP_Procedure

A Select * by itself without a table name should be invalid.

 

[AppDev76]

cmmrfrds,
thanks for your reply.
I call all Stored Procs with dbo.

By Select * I meant that the form does a select * on what ever the recordSource of the form is.

For example, if the recordsource of the form is VwProducts and I open the form in DataEntry mode, the first thing that Access does (even though I just want to INSERT a record) is SELECT * FROM VwProducts.

I have set the maxrecord count property of the form to 0 so atleast the network won't be overloaded, but there is extra load on the server anyway.

Don't know what to do!

 

[cmmrfrds]

Once the record source is used to create the fields on the form it can be deleted if you are dynamically building the record source each time. Remove the record source each time you leave the form.

 

[AppDev76]

Thats what I do.
On the unload event of the form, I set the Recordsource tp nothing, however, when I dynamicaly set the recordsource the view, Access sends a "select * from View".

Is there any way to select only thse structure of a view and not return any data?

 

[cmmrfrds]

I do this type of thing and have not seen any extraneous selects.

OnOpen event
Me.RecordSource = "Select fld1, fld2, etc from aView"

 

[AppDev76]

cmmrfrds,
thanks for your reply.
Yes, there won't be and "extra" select if you want to bind the data to the form, however if you want to open the form with the data source "Select fld1, fld2, etc from aView" in data entry format just to do an insert, you don't need to do a select from the datasource.
I guess my problem is that I'm bounding the form and I souldn't. I just need to creat an Insert stored procedure and an unbound form.