Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: Setting form recordsource to a stored procedure at runtime using vba

    Hi,
    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?!
    Thanks
    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.

  2. #2
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    The solution to the first problem:
    You need to use "EXEC dbo.SP_procedure" not "dbo.SP_procedure"
    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.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    For the second solution, consider using unbound controls and posting an UPDATE statement instead of pulling a whole recordset.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks for your reply.
    Is ther any way to create an empty recordset in order to do batch updating?
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •