none
back end refresh SQL Server RRS feed

  • Question

  • splt db with tables in SQL Server in a separate building but still on premise within the same LAN, all works fine, Access 2013 and forms are tabbed form option (not overlapping windows)

    Form A sourced to Table A,  Form B sourced to Table B

    in Form B is button to open Report 1.  Report 1 contains data from Table A.

    scenario:

    * User has both forms open; updates/enters/edit into a field in Form A

    * using mouse moves to Form B, presses button to open Report 1 - and the report does not contain the new data from A

    I know what the issue is and how to deal with it - but I don't know why.  I can actually go back using my mouse and see the cursor still blinking in the form A field even though we've used the mouse to select form B tab, and then click on form B button.

    Why does not the focus move such that Form A is no longer dirty?  I tend to say that this seems more prevalent with SQL Server as the back end and that Access back ends seem more "tightly coupled" and don't have this issue - though that observation is casual and off the cuff.  I would think the mouse clicks would move the cursor off Form A and thus the write to the table A would have occurred before the B button is pressed.  It seems like I have to more frequently handle explicit dirty/requery than usual.

    Friday, September 22, 2017 12:48 PM

Answers

  • it just seems that if you use your mouse to click into a field in Form B - then you're done with Form A and it's no longer Dirty.....(I think)

    but if you instead click on a button in Form B rather than a field, then Form A remains Dirty... at least that's my take.  One would think clicking on the Form B tab and then again clicking anywhere in Form B would suffice that Form A should be no longer Dirty.... but oh well

    This is just a case where Access doesn't conform to your expectations.  But consider the case where you are entering data in a form, and you have to look up something on another form.  So you open that form, have a look for what you need, and come back to the form you were working on.  Would you expect to find that your record was saved?  What if that piece of information was necessary to finish the record you were working on?

    hey why bother to even check the If Dirty?  why not just

    If CurrentProject.AllForms("A").IsLoaded Then

    Forms!A.Dirty = False

    End If

    .... is there any downside to this more abbreviated approach?

    Not much of one.  Setting the form's .Dirty property to False when the form isn't dirty doesn't raise any error or cause any harm.  I did some benchmarking once, and found that doing that was marginally less efficient than first testing the .Dirty property and only setting it to False if it was currently True.  However, that wouldn't make any practical difference in any real-life scenario I can imagine. 

    Of course, if you want to give the user the opportunity to *not* save the record, then it's best to test the Dirty property first so you can display a confirmation dialog.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, September 22, 2017 3:41 PM
  • If CurrentProject.AllForms("A").IsLoaded Then

    Forms!A.Dirty = False

    End If

    Hi msdnP...,

    What will happen if you use a third form "C", must you check to "undirty" both "A" and "B"? Or a fourth form "D"?

    So the trigger to save the form can be better on the form itself, than on the next form (unless you keep track of which form is the next and which is tjhe former).

    In that case the form in question is always loaded.

    Another area of interest are the subforms.

    Imb.

    Friday, September 22, 2017 3:46 PM

All replies

  • Why does not the focus move such that Form A is no longer dirty?

    Hi msdnP...,

    In the Deactivate event of a form I use standard a "Save_record" routine, containing a.o. a check for the Dirty property of the form.

    Imb.

    Friday, September 22, 2017 1:08 PM
  •  got it

     but how about when the back end tables are in Access....    I don't remember ever having to do this....

     is it a SQL / ODBC thing? 

    Friday, September 22, 2017 1:16 PM
  • This sounds like the expected behavior to me, regardless of whether the back-end is SQL Server or Access.  The fact that you switch your attention to a different form doesn't necessarily mean (so far as Access is concerned) that you are done with the current record on the current form, so it wouldn't save it.  Only if you close the current form, or move to another record on that form, or explicitly tell Access to save the record, would Access do that.

    I'd recommend adding to the code on form B that opens the report, a little code along these lines:

    If CurrentProject.AllForms("A").IsLoaded Then
        With Forms!A
            If .Dirty Then .Dirty = False
        End With
    End If

    You may or may not want to include a message asking the user if he wants to save the record on Form A, before forcing the save in code.



    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, September 22, 2017 1:41 PM
  • you are probably right DG - my memory may be off

    it just seems that if you use your mouse to click into a field in Form B - then you're done with Form A and it's no longer Dirty.....(I think)

    but if you instead click on a button in Form B rather than a field, then Form A remains Dirty... at least that's my take.  One would think clicking on the Form B tab and then again clicking anywhere in Form B would suffice that Form A should be no longer Dirty.... but oh well

    hey why bother to even check the If Dirty?  why not just

    If CurrentProject.AllForms("A").IsLoaded Then

    Forms!A.Dirty = False

    End If

    .... is there any downside to this more abbreviated approach?

    Friday, September 22, 2017 3:23 PM
  • it just seems that if you use your mouse to click into a field in Form B - then you're done with Form A and it's no longer Dirty.....(I think)

    but if you instead click on a button in Form B rather than a field, then Form A remains Dirty... at least that's my take.  One would think clicking on the Form B tab and then again clicking anywhere in Form B would suffice that Form A should be no longer Dirty.... but oh well

    This is just a case where Access doesn't conform to your expectations.  But consider the case where you are entering data in a form, and you have to look up something on another form.  So you open that form, have a look for what you need, and come back to the form you were working on.  Would you expect to find that your record was saved?  What if that piece of information was necessary to finish the record you were working on?

    hey why bother to even check the If Dirty?  why not just

    If CurrentProject.AllForms("A").IsLoaded Then

    Forms!A.Dirty = False

    End If

    .... is there any downside to this more abbreviated approach?

    Not much of one.  Setting the form's .Dirty property to False when the form isn't dirty doesn't raise any error or cause any harm.  I did some benchmarking once, and found that doing that was marginally less efficient than first testing the .Dirty property and only setting it to False if it was currently True.  However, that wouldn't make any practical difference in any real-life scenario I can imagine. 

    Of course, if you want to give the user the opportunity to *not* save the record, then it's best to test the Dirty property first so you can display a confirmation dialog.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, September 22, 2017 3:41 PM
  • If CurrentProject.AllForms("A").IsLoaded Then

    Forms!A.Dirty = False

    End If

    Hi msdnP...,

    What will happen if you use a third form "C", must you check to "undirty" both "A" and "B"? Or a fourth form "D"?

    So the trigger to save the form can be better on the form itself, than on the next form (unless you keep track of which form is the next and which is tjhe former).

    In that case the form in question is always loaded.

    Another area of interest are the subforms.

    Imb.

    Friday, September 22, 2017 3:46 PM
  • so thanks everybody - I think maybe the issue is not unique to SQL/ODBC back end and that was just my imagination.

    the big picture I think as laid out in my original post is that if the user clicks on another tab form, then clicks on a button in that other tab form (to generate a report) - they pretty much think to themselves and expect the db to realize they are done with Form A.  

    As DG pointed out - maybe not clicking on the tab itself - cause maybe just referencing some info - - but once you click on a button inside the form B - then Form A really should no longer be dirty in my humble opinion....

    I think Imb has a pragmatic solution in just doing the trigger when form goes inactive - then it's over & done with and you don't have to deal with it in every button on every other form...

    good input, thanks.

    Friday, September 22, 2017 4:26 PM