Results 1 to 13 of 13
  1. #1
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185

    Datasheet View Form - Check If User Clicks In

    Hi All



    I have a few datasheet view forms for admin users, currently if they open and close the datasheet form it triggers (on close) some VBA processing, that's quite time consuming.

    However often the admins will only open it to have a look, not actually change anything, if they don't change any field values, then my VBA doesnt need to run at all.

    So my question, is there a VBA way (or otherwise) to "check" if the user changed any value, and if "yes" run my code, if "no" just close the datasheet form.

    Thanks

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Private SomeForm_Close()
    If Me.Dirty Then
    run this code
    End If

    End Sub

    Form Dirty property, in other words. Not applicable to an unbound form though.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    That easy? Thought it would be complicated.

    It's a datasheet view form created directly off a table so should be bound but I'll check.

    Thanks

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    FYI - appreciate you question is about closing a form but if you are concerned about performance issues - basing forms on whole tables is one way to create slow performance. They should be based on a query to limit the number of records to be brought across. i.e. use criteria functionality not filtering functionality

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I re-read and then re-read the OP's posts and can't see where the form recordsource is even mentioned. I just took "VBA processing" to mean code runs that doesn't need to. Perhaps you have a copy of the subject db so you already know the form recordsource. Regardless, I agree that forms should be based on queries or sql statements.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Op said in a later post

    It's a datasheet view form created directly off a table

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Right you are!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Hi All

    So I put the below in Form_Close() to test:

    Code:
    If Me.Dirty Then    
        MsgBox "Dirty"
    Else
        MsgBox "Not dirty"
    End If
    But doesnt seem to work, always returns "Not dirty" even if I change values in the datasheet view form, which do save as they are still there when I open it again.

    I'm thinking the form only "saves" when it actually closes so up until that point it might be considered not dirty even when I have enter stuff? Not sure but maybe I need to force a save before hand? And then run the dirty check?

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    When you move off of a record, even if that's to click a button, the record gets saved so the form is no longer dirty. I don't think this happens if they use the built in form close button but am not 100% sure. I think I specified the wrong event - see https://docs.microsoft.com/en-us/off...rm.dirty(even)

    Public variables can be wonky within forms so if the above link doesn't help, I can try that or creating a form property, which is more complicated.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by Micron View Post
    When you move off of a record, even if that's to click a button, the record gets saved so the form is no longer dirty. I don't think this happens if they use the built in form close button but am not 100% sure. I think I specified the wrong event - see https://docs.microsoft.com/en-us/off...rm.dirty(even)

    Public variables can be wonky within forms so if the above link doesn't help, I can try that or creating a form property, which is more complicated.
    I was testing it just with the usual window close X button on top right. As its a datasheet view form there are no other buttons etc on the form other than datasheet view of the table.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Try this (tested and works)?
    Code:
    Option Compare Database
    Option Explicit
    Dim bolIsDirty As Boolean
    
    Private Sub Form_Close()
    MsgBox bolIsDirty
    End Sub
    
    Private Sub Form_Dirty(Cancel As Integer)
    bolIsDirty = True
    End Sub
    Instead of the message box you'd code to run the code you referred to or not. This should work regardless of how you close the form or whether or not someone has edited a record and then selected another record (which would make the dirty property false).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by Micron View Post
    Try this (tested and works)?
    Code:
    Option Compare Database
    Option Explicit
    Dim bolIsDirty As Boolean
    
    Private Sub Form_Close()
    MsgBox bolIsDirty
    End Sub
    
    Private Sub Form_Dirty(Cancel As Integer)
    bolIsDirty = True
    End Sub
    Instead of the message box you'd code to run the code you referred to or not. This should work regardless of how you close the form or whether or not someone has edited a record and then selected another record (which would make the dirty property false).
    Thanks so on form close I just do IF bolIsDirty = True then "my code"

    Does Sub Form_Dirty automatically run if the form is dirty?

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yes.
    Yes.
    WRT the 2nd question, wouldn't you learn more by researching such questions?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-22-2017, 04:47 PM
  2. Replies: 3
    Last Post: 01-15-2015, 12:23 PM
  3. Replies: 4
    Last Post: 06-01-2013, 11:32 PM
  4. Putting check box on Datasheet View
    By EddieN1 in forum Forms
    Replies: 2
    Last Post: 05-12-2013, 05:53 AM
  5. Unbound Check Boxes on Datasheet View
    By EddieN1 in forum Forms
    Replies: 2
    Last Post: 07-23-2012, 12:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums