Access 2010 VBA - Won't Save as expected

Posted on 2012-08-16
Last Modified: 2012-09-11
I have an bound Access form on which the User needs to check a LOT of check boxes, while performing brief (1-5 minute) tasks between.  When a box is checked, I want to update a couple of fields and save the record immediately.  From the User's perspective, it should go like this:
    • Do some work.
    • Check the box (VBA should then populate the Username and Date/Time text boxes).
    • Do some more work
    • Check the next box (VBA as above)
    • Close the form when done with all the tasks on the form.

It seemed to me that I should be able to tie this to the On Dirty event.  The plan was to use Me.Dirty = False to save the record and make it clean again so they could go on to the next checkbox.

Didn't work that way at all.  In fact, I can't get the stupid thing to save the record no matter what I do.  I've tried:
    • Me.Dirty = False
    • DoCmd.RunCommand acCmdSaveRecord
    • Moving to another record, then back using DoCmd.GoToRecord...

None of this worked, but I think ANY of them should have.

I can use DoCmd.RunCommand acCmdSaveRecord if I make a separate button for it.  So the user would check a box, click the button, check the next box, click the button again, etc.  Very annoying for the Users!

Eventually I found that SendKeys "{F5}" right after I populate the additional fields DOES save the record immediately.  That seems to work fine, but I try to avoid SendKeys whenever possible.  So if anyone has any suggestions I haven't tried yet, please let me know.


- Dayton - Thu. 08/16/2012 @ 13:02:06
Question by:AzDayton
    LVL 18

    Expert Comment

    Sounds like the save is actually working fine and what you want is to refresh the form. And doing F5 does refresh the form. Try using me.refresh instead of your sendkeys command.


    LVL 12

    Expert Comment

    Can you provide the full sample subroutine code where you're trying to save the record? I'm just trying to clarify what action is triggering the save attempt and what the difference would be.

    To start, it's a little odd to use F5 to do this. That's the shortcut key to switch to form view, which effectively reloads the form if you're already in it. If you end up having to use SendKeys, Ctrl+S or Shift+Enter might work better.

    For the checkbox, are you using the OnChange Event of the object, or the Click event? And then you're including the DoCmd.RunCommand acCmdSaveRecord statement there, and it doesn't do anything? But then when you include the same statement in the Click event of a separate button, it saves as expected?

    Usually for the Dirty method I do this:
    If Me.Dirty Then Me.Dirty = False
    Breaking here would be a good check to see whether the form actually considers the record to be dirty at the point you're trying to save. If you're using the Click event of the checkbox, perhaps the click hasn't taken effect yet and the record isn't considered dirty? That would mean Me.Dirty is already false, and setting it to be false again won't do anything. It's only the change from true to false that actually triggers the record to be saved.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    The Dirty Event does not always trigger when you think it should.

    Like telyni19, I would like to see a sample database that exhibits this issue.
    It is very hard to troubleshoot an issue like this without seeing the code, of the Application itself.

    Author Comment

    Okay.  The form is essentially just an automated route slip with a bunch of checkboxes and text boxes on it.  What I'm after is for the user to click the checkbox, and the text boxes will automatically populate with their UserID and the current date (so a record is kept that they approved it, and when).

    Similarly, if the box is checked and they click it, it should remove the UserID & Date.

    There's also a check to see if a "Responsible Person" has been assigned to this level of checking.  There are four levels of checks performed, with eight checks per level, making a total of 32 checkboxes, with the accompanying 32 Date text boxes and 32 UserID textboxes.

    I could probably do this by writing a separate AfterUpdate even for each check box, but I was hoping for something a lot less cumbersome by using the Dirty event tied to the form, which would then figure out the ActiveControl name, and thereby deduce the TextBox names it needed to update.  Much of the code is dedicated to figuring out the other control's names.  The controls are named such as:
      * chkL1_C1_CB = Level 1, Check 1, Checkbox
      * txtL1_C1_DT = Level 1, Check 1, Date textbox
      * txtL1_C1_By = Level 1, Check 1, User ID textbox (check completed BY)
    The levels run 1-4, the checks run 1-8, so the last checkbox is named chkL4_C8_CB, with corresponding txtL4_C8_DT and txtL3_C8_By textboxes.

    I made UpdateTable into a separate SUB as I was experimenting with methods of getting it to update.  I'll leave in the REM'd out stuff I tried and notes.  In it's current (working) incarnation, it just sends the F5 keystroke.

    Here's the code.  Also attached is a cleansed picture of the route slip form:
    Private Sub Form_Dirty(Cancel As Integer)
    Dim strCBase As String  'Control Name Base
    Dim strCName As String  'Control Name
    Dim strLRP As String    'Name of RP for this level
        strCName = Me.ActiveControl.Name
        strCBase = Mid(strCName, 4, 5)
        If Right(strCName, 3) = "_CB" Then
            strLRP = Nz(Me.Controls("txt" & Mid(strCName, 4, 3) & "Name"), "")
            If strLRP = "" And Nz(Me.Controls("txt" & strCBase & "_DT"), "") = "" Then
                MsgBox "A Responsible Person has not been assigned to this level yet.", vbExclamation, "WARNING"
            End If
            If Not Me.Controls(strCName) Then
                Me.Controls("txt" & strCBase & "_DT") = Format(Now(), "MM/DD/YYYY")
                Me.Controls("txt" & strCBase & "_By") = Environ("USERNAME")
                Me.Controls("txt" & strCBase & "_DT") = ""
                Me.Controls("txt" & strCBase & "_By") = ""
            End If
        End If
        DoCmd.GoToControl strCName
    End Sub
    Private Sub UpdateTable()
        'Either of the following should save the record but neither does
        'Eventually found workaround by sending an F5 keystroke with SendKeys
        'This is a kludge, and should be researched to find out why the
        'normal save methods don't seem to work correctly.
        '    DoCmd.RunCommand acCmdSaveRecord
        '    Me.Dirty = False
        'Moving off the current record, then back, also did not work.
        'DoEvents didn't help either.
        '    DoCmd.GoToRecord , , acNext
        '    DoEvents
        '    DoCmd.GoToRecord , , acPrevious
        '    DoEvents
        'Using Me.Refresh did nothing.  The checkbox checks/unchecks, but the
        'text boxes do not update unless the form is closed then reopened every time.
        'Adding Repaint didn't seem to help.  Neither did adding DoEvents
        '    Me.Refresh
        '    Me.Repaint
        '    DoEvents
        SendKeys "{F5}"
    End Sub

    Open in new window

    LVL 74

    Accepted Solution

    Still not completely clear on why you have designed this in such a complicated manner...
    But I think this is your sticking point:
    <The plan was to use Me.Dirty = False to save the record and make it clean again so they could go on to the next checkbox.>

    You really can't "reset"  the dirty event (make it clean again) while in the same record.
    It only occurs once per record.

    <I could probably do this by writing a separate AfterUpdate even for each check box, but I was hoping for something a lot less cumbersome by using the Dirty event tied to the form>
    Well if: "writing a separate AfterUpdate even for each check box", then just use that.

    It seems once you started "hoping for something a lot less cumbersome by using the Dirty event tied to the form"
    Then this approach *Did not* work.

    I will always take something that works, over something that does not.

    In any event, always do things seperately, then try "streamlining" the code when you are sure it works.

    So try your code like this first:
    Private Sub Form_Dirty(Cancel As Integer)
        DoCmd.RunCommand acCmdSaveRecord
    End Sub

    ..I mean, if this does not work, then wraping it in the UpdateTable function, or doing all that "other stuff" won't work either.

    Finally, most code on the Dirty event only saves the record, so again, I am unsure if all that other code should really be there.
    Perhaps you shoulre move all of that other code to the UpdateTable sub...

    Author Closing Comment

    Good advice.  Thanks.

    Basically, it's that complicated because that's what the customer wants.  At the end of the day, this form is a convenience to that Manager's Users, and not really a database thing anyway.  It's just an electronic route slip, and it works fine using the F5 without having to write (and maintain)  32 separate "After Update" event procedures.  The customer was very clear on NOT wanting to go that route, even if that meant making it more complex.  (Shrug)  The boss is always right!

    I was (and still am) curious why F5 seems to refresh the "Dirty" event, but moving off-record then back on-record doesn't; but I don't have any time to spend on it anymore.

    Thanks for your input.  Good advice!

    - Dayton - Tue. 09/11/2012 @ 09:51:52

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now