none
Using recordset and Stored procedures RRS feed

  • Question

  • I have a strange issue and cannot find the way to fix it. I have many forms with multiple sub-forms and datasheets, each of them bound to different SQL Stored Procedures. The application works fine until user decides to Unhide/Hide a column in any of the datasheets. When this happens the application saves back the "design" of the forms and with that, the Stored Proc in the Record Source as follow: {call spWhateverStoredProc(?,?,?,?)}. No matter what I do (before close), it does not go away because the form design was saved, therefore the Record Source with it. The only way I can fix this is by designing the forms once again and remove the Record Source at design.

    Any ideas anyone?


    Gustavo Miller

    Wednesday, October 9, 2019 1:56 AM

All replies

  • In Form_Open:

    Me.RecordSource = "myRecordSource"

    (of course you replace myObjectNames with yours)


    -Tom. Microsoft Access MVP


    Wednesday, October 9, 2019 3:43 AM
  • Thank you Tom. You are right that would be the way to do it; if you are using a local query or table. Plain and simple. I am using a recordset.

    1. Open recordset using ADODB, on object variable (objData).

    2. Set Form.Recordset = objData

    3. Present form, user does it thing and then decides to hide a column.

    4 Form closes and it prompts user to save; he does,

    5. Cannot open the form anymore because Recordsource = "{ call spBLABLA ?,?,? }"

    This is the issue...


    Gustavo Miller

    Wednesday, October 9, 2019 8:42 PM
  • myRecordSource could be a passthrough query (if readonly is good enough).

    -Tom. Microsoft Access MVP


    Thursday, October 10, 2019 1:48 PM
  • Yes Tom it can this is correct; Recordsource can be a local table, a query or even a text containing the SQL, but this is using the RecordSource, I am not using this attribute, I am assigning a Recordset at run-time. So basically the form design in unbound.

    In an Edit form which are type Single Form; with no Sub Forms this does not happen. Why? because the user cannot resize/hide the fields: it is a Single Form, controls are static. User can resize the Form (if applicable).

    Things are different when you have a Form with a Sub-Form or a split form -for that matter; user can hide, unhide and resize controls. Once the user does that and closes the form, access will prompt the user to save the objects modified (even though columns resizing/hiding happened only on one of the subforms and not on the parent objects). It is here when the Recordsource are saved with the { call spBLABLA ?,?,?,? } even though I am NOT using the Recordsource.

    Now, when user tries to re-open the form, it fails. I have to design form, remove the RecordSource and save.

    Thank you for your time though... I am still trying different methods from time to time, for now I just told users NOT to resize/hide/rearrange columns.


    Gustavo Miller

    Thursday, October 10, 2019 3:13 PM
  • Does this happen when you publish your database as .ACCDE?

    -Bruce

    Thursday, October 10, 2019 8:21 PM
  • That is actually a very good suggestion, I will try it. In a ACCDE database the objects would be read-only. I'll keep you posted! Thank you for the suggestions. I have not tried anything lately...


    Gustavo Miller

    Thursday, October 10, 2019 8:25 PM
  • Thank you for your time though... I am still trying different methods from time to time, for now I just told users NOT to resize/hide/rearrange columns.

    Hi Gustavo,

    I guess your problem is related to the DataSheet. I can imagine Access wants to save the changes in layout for your later use. What happens by the way when the user answers NOT to save?

    I never use DataSheets. Instead I use a (generalized) form, totally unbound, without RecordSource and without ControlSources. I use it to display any RecordSet in any application. In the Open event of the form the RecordSource is set, or the RecordSet is assigned, the relevant Controls are unhidden, their dimensions are filled in, and bound with the ControlSource.

    Users now can hide/unhide columns, resize the width of the controls, change editability of the columns, etc. What I experience is that the users hardly do that. They have other means to read controls that are too small to display the full content of the control, or the controls that fall outside the window. All this is done in memory, without going into development mode, so no savings at Close.

    Perhaps this gives you a thought for a "different method".

    Imb.

    Thursday, October 10, 2019 9:16 PM
  • Thank you lmb. Correct, the problem is in the Datasheets. By design Access saves the object (parent) and all its related components. I have a forms that contains two and even 4 Sub-forms with Datasheets. When something like that happens (user resizes/hide/re-arranges columns) the Parent Form and ALL it's sub-forms are saved automatically - I guess before the object is even closed. This happens too with a Split-form, it does save the object.

    What you said made me think (different method)....  I could open the form in-memory using a variable object; this will make the form design NOT available to Access therefore it would not save the design.

    I'll try it later today...


    Gustavo Miller

    Friday, October 11, 2019 11:41 AM
  • Thank you lmb. Correct, the problem is in the Datasheets. By design Access saves the object (parent) and all its related components. I have a forms that contains two and even 4 Sub-forms with Datasheets. When something like that happens (user resizes/hide/re-arranges columns) the Parent Form and ALL it's sub-forms are saved automatically - I guess before the object is even closed. This happens too with a Split-form, it does save the object.

    What you said made me think (different method)....  I could open the form in-memory using a variable object; this will make the form design NOT available to Access therefore it would not save the design.

    I'll try it later today...


    Gustavo Miller

    OK. I tried it and it works; opening it through an object-variable it prevents saving the object, but it does not remember the columns that were hidden or rearranged, which is fine. I'll have to see the user acceptance, they may not like it....

    I'll close the issue later this afternoon... maybe someone else comes up with something else..


    Gustavo Miller

    Friday, October 11, 2019 3:22 PM