• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 951
  • Last Modified:

Access 2010 VBA - Won't Save as expected

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
1 Solution
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.


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.
Jeffrey CoachmanMIS LiasonCommented:
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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

AzDaytonIT GuyAuthor Commented:
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

Jeffrey CoachmanMIS LiasonCommented:
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"
...works, 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...
AzDaytonIT GuyAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now