Results 1 to 7 of 7
  1. #1
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23

    Must save before exit, help please.


    I have the following code set to a save button
    Code:
    Private Sub cmd_save_Click()Dim ctrl As Control
    str = Empty
    
    
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is TextBox Then
            If IsNullorEmpty(ctrl) Then
                ctrl.BackColor = RGB(119, 192, 212)
                ctrl.BorderColor = RGB(157, 187, 97)
                str = str & ctrl.Tag & vbNewLine
            Else
                ctrl.BackColor = vbWhite
                ctrl.BorderColor = RGB(192, 192, 192)
                
        End If
        End If
    Next ctrl
    
    
    If IsNull(str) Or str = "" Then
        DoCmd.OpenForm "frm_Josh"
        Exit Sub
    Else
        MsgBox "Please enter data for all the required fields below." & vbNewLine & _
            String(52, "-") & vbCrLf & str, vbInformation + vbOKOnly, "Fill in Blanks"
        Exit Sub
    End If
    End Sub
    This prevents any text boxes from being left empty. However anyone can just close the form without having to click the save button. I don't have a close button enabled, they can simply click on another tab to get out of the form. I was hoping I can add a me.dirty function or something that directs them to have to click the save button and then if all boxes are filled they can navigate away from this form.
    Thanks in advance.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    IMHO form validation is best done in the forms BeforeUpdate event which can be cancelled thereby preventing the record being saved.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are the controls in question not bound?
    If the form controls are bound, there's no problem, no? Edit a bound form, close it without explicitly saving and the data is saved.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Inadvertent duplicate post

    Linq ;0)>
    Last edited by Missinglinq; 11-21-2019 at 06:04 AM.
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    And if the validation code is in the Form_BeforeUpdate event (as it should be...and as Bob suggested) simply closing the Form will trigger the code...because if the Form is Dirty, the Form_BeforeUpdate event will fire before the Form closes.

    BTW: I know IsNullorEmpty() in NET...but has it been introduced into one of the later versions of Access VBA?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    ?IsNullOrEmpty("0")
    give an error in immediate window.

    However STRING(25,"-") does work. Odd that a function name is also a reserved word as in "Dim sSQL as String"

    Microsoft itself reserves the right to break the rules.
    Last edited by davegri; 11-20-2019 at 06:15 PM. Reason: correction

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    has it been introduced into one of the later versions of Access VBA?
    Don't think so. I took it to be a custom function (udf). It's not found in the object browser for all libraries in Access 2016 - at least, not on my pc.

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

Similar Threads

  1. Replies: 2
    Last Post: 11-13-2017, 08:51 AM
  2. Replies: 5
    Last Post: 07-08-2015, 10:39 AM
  3. Replies: 2
    Last Post: 12-20-2012, 03:06 PM
  4. Replies: 7
    Last Post: 11-23-2011, 08:14 PM
  5. exit
    By slimjen in forum Forms
    Replies: 4
    Last Post: 10-09-2011, 09:30 AM

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