Results 1 to 8 of 8
  1. #1
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81

    "Save Changes" suddenly stopped working to save record changes in a form


    I'm using a form to edit and update a record. I'm using "Me.Dirty" to detect if changes were made, then prompting to save changes. After my last update, I suddenly stopped saving changes to the data table, and I'm not sure why. This is my code:

    Code:
    Private Sub Return_button_Click()
       Dim lotNumber As Long
    
    .
    .  ' I'm omitting some irrelevant code here
    .
    
       lotNumber = Me!LOT_NUMBER
        
       
       If Me.Dirty = True Then
          If MsgBox(" Save Changes? ", vbYesNo) = vbYes Then
             
             GoTo EndRoutine
          Else
             Me.Undo
          End If
       End If
    EndRoutine:
       DoCmd.Close acForm, "RM_ADD_Form"
       DoCmd.OpenForm "Landing_Page", OpenArgs:=lotNumber
    End Sub
    I'm pulling my hair out, not sure where to look! I would appreciate any suggestions please.

    Thank you in advance,

    Paul

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Have you compiled the VBA code (in the menu DEBUG/Compile)?
    Have you done a "Compact and Repair"?

    Here is my version of your code:
    Code:
    Private Sub Return_button_Click()
        Dim lotNumber As Long
    
        ' .
        '   .  ' I'm omitting some irrelevant code here
        '    .
    
        lotNumber = Me!LOT_NUMBER
    
        If Me.Dirty Then
            If MsgBox(" Save Changes? ", vbYesNo) = vbNo Then
                Me.Undo
            End If
        End If
        
        DoCmd.Close acForm, "RM_ADD_Form"
        DoCmd.OpenForm "Landing_Page", OpenArgs:=lotNumber
    End Sub


    Instead of a button, consider using the "Form_BeforeUpdate" event.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This form is unbound? I don't see how that is supposed to update any record at all. If the form is dirty, you set a variable to something, then simply close the form. If the form is bound, there's no need for that code. If it isn't then you're not saving anything.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    @Micron This form is unbound in the properties sheet, but when it opens it is either: A.) bound to a query based on the open arguments sent to it, if the user wants to edit a record. or B.) Bound to a table to create a new record if there are no open arguments. Open arguments are passed if they select a record and choose an 'edit' button, or not if they choose 'create new record' button to open this form.

    @ssanfu thanks for the code cleanup, I never thought of it that way before. "Compact and repair" didn't have any effect on the fault that I could tell, and debug/compile found a lot of dead functions that I forgot were in there, lol.

    I'm not happy with what I did to fix this:

    1. One of the buttons on this form should update a text field in the table called 'LOT_STATUS', this is what was being lost when the form wasn't saved correctly.
    2. I changed that reference from Me.LOT_STATUS to Me!LOT_STATUS
    3. I also have a text box on that form that displays the current lot status, lets call it 'lotStatusBox'. (It's bound to LOT_STATUS in the property box)
    4. I had to run Me.lotStatusBox.Requery immediately after changing LOT_STATUS, in order to update this box. This is not necessary in the sister forms to this one that are not using bang(!) notation. I'd love some insight if anyone can offer it.

    I don't understand why I suddenly had to use bang(!) notation in this form, and my OCD is going nuts because the forms don't match.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have to wonder if you're over complicating this. I suspect there is only 1 table involved in the query since you can switch between one or the other if a record needs to be added. If so, then simply basing the form on a query of that table would allow either edits or appends - there is no need to use a table or query for one or the other task AFAIC.

    If your form code won't compile with Me.Lot_Status, then it usually means the reference doesn't exist (often due to misspelling). If it does compile yet errors at run time, and you can fix the error with Me! and you have Option Explicit at the top of the module, then I would suspect a corrupt form or control. If you don't have Option Explicit option turned on and it isn't at the top of every module it is highly recommended. However, you might find that a decompile after that will force you to fix other things you didn't know need fixing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Quote Originally Posted by Micron View Post
    I have to wonder if you're over complicating this. ...
    That may be. I don't think I'm using an append to open a new record, I'm using DoCmd.RunCommand acCmdRecordsGoToNew on the Form_Open event to create a new record. Does that count as an append?

    I just checked to be sure, I am using Option Explicit on this and all of the sister forms. The sister forms are for different departments to update their relevant fields.

    I am querying other tables in this form but not necessarily on the open event. Rather, a text box is bound to a function called getSpec().

    This is the relevant part of my Form_Open event:

    Code:
       If Nz(Me.OpenArgs) = 0 Then
          'set a toggle variable for additions   
          Me.AllowAdditions = True
      
          Me.RecordSource = "LOT_DATES"
          DoCmd.RunCommand acCmdRecordsGoToNew
          Me.LOT_NUMBER.Locked = False
          Me.SPEC_NUM.Locked = False
          GoTo skipLocks
       Else
          Me.RecordSource = "Select LOT_DATES.* FROM LOT_DATES WHERE (((LOT_DATES.LOT_NUMBER)='" & Me.OpenArgs & "'));"
       End If
    getSpec():

    Code:
    Private Function getSpec()
        If Nz(Me.OpenArgs) = 0 Then
           getSpec = "0"
        Else
           getSpec = DLookup("[SPEC_DESC]", "SPEC_DATA", "[SPEC_NUM] = LOT_DATES.SPEC_NUM")
        End If
    End Function
    EDIT: If you're saying that I may have a corrupt form or control, I can probably re-create this form from one of the sister forms pretty easily to make them all work the same way again. I was recently weighing the merits of making all of the functions and variables in these forms global, since they are very similar forms but I think doing that may have caused bigger problems. I'm going to mark this as solved and go with rebuilding from a sister form.

    EDIT2: This started when I made a recent update, which I started a separate thread on last week. This update involved adding a new button with new functionality, and a new field in the LOT_DATES table. But when I created this new button, rather than using a fresh control from the design menu, I copy and pasted an existing button then changed the names and events to what I needed. I wonder if that caused the corruption, and if making that change with a fresh button might fix this whole mess.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I didn't mean to imply you were using an append to add records (yes, append means add) or needing to query on the open event. If you create a query based on this table (regardless of how many table fields you add to it) you should be able to open it and add or delete or edit a record in that query. So if you set the recordsource for the form to be that query, I don't see the need for so much form code. If you want to open the form and do things like prevent those operations, that is a different thing. I'm saying I don't see the need to switch form record sources in this case.

    As for Option Explicit, then the Me. vs Me! thing makes no sense, especially if the reference is correctly spelled/does exist. I might try copying the db, changing the reference to Me. and do a decompile on the copy (research it - not the same as compiling code) to see if that fixes it. If not, then watch out for further issues with this form because although I hate to jump to concluding that the form or a control is corrupted, I don't know what else would cause that situation.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Thanks, I'll do some research!

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

Similar Threads

  1. Replies: 3
    Last Post: 09-13-2019, 06:07 AM
  2. Replies: 3
    Last Post: 07-23-2019, 12:43 PM
  3. Replies: 3
    Last Post: 02-13-2019, 01:49 AM
  4. Replies: 4
    Last Post: 04-28-2016, 02:11 PM
  5. Replies: 3
    Last Post: 08-12-2012, 11:53 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