none
Access form unwantedly saving new row in table on close. RRS feed

  • Question

  • I have a form to modify data in a table that should not be able add a new row to the table it is modifying.

    When the form is opened their is a combo box populated by a query of the table with no row selected. The query gets the index and name, and a few other columns but only displays the name.

    When the user selects a row from the combo box it executes some visual basic that populates text fields using the data in the selection combo box's hidden rows.

    When I close the form, it is taking the data in the text boxes and creating a new row in the table with the data.

    I have tried closing by right clicking the forms tab, using a close button with access's macro, or a button calling vba that just calls the close method. However I try it adds a new row to my table.

    Even one with close that sets all of the text box's values to null and disables them, I just get a blank row in my table.

    The only time it dose not is if I open the form and close it without do anything at all.

    Dose anyone have a clue how to fix this?

    Wednesday, August 17, 2016 7:34 AM

Answers

  • I tried the code Mr. Hoffman listed but it didn't appear to do anything and I am still getting unwanted rows. But I don't understand it completely and will have to look up some things and maybe I'll understand how to make it work for me.

    In this case there must be some code or a macro saving the data. Cause the generic Cancel = True blocks the Default way of saving data.

    hmm, how is the form opened? Maybe in DataMode = acFormAdd ?

    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:42 AM
    • Marked as answer by David_JunFeng Wednesday, August 24, 2016 2:37 PM
    Thursday, August 18, 2016 7:57 AM
  • When I close the form, it is taking the data in the text boxes and creating a new row in the table with the data.

    If the combo box is unbound, instead of assigning data to the Value property of each control (the default), assign it to the DefaultValue property.  This does not Dirty the form, so when the user closes the form without inserting any further data, or editing one or more of the default values, the row will not be saved.

    Note that the DefaultValue property is always a string expression regardless of the data type of the column to which a control is bound, so must be wrapped in literal quotes characters, e.g. to assign the current date as the default value would be:

    Me.txtDateEntered.DefaultValue = """" & VBA.Date & """"

    If the combo box is bound, the form will be Dirtied upon making the selection of course, so this won't work.  You'd need to Cancel the update in the event of no further data being inserted or edited..


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Thursday, August 18, 2016 11:29 AM Typo corrected.
    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:42 AM
    • Marked as answer by David_JunFeng Wednesday, August 24, 2016 2:37 PM
    Thursday, August 18, 2016 11:29 AM
  • Hi Christopher. Not sure if this will help with your situation; but to make your form "unbound," make sure the RecordSource property of your Form is empty. Of course, having an unbound form means you'll have to "save" the data to the table yourself. Hope it helps...
    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:42 AM
    • Marked as answer by David_JunFeng Wednesday, August 24, 2016 2:37 PM
    Thursday, August 18, 2016 3:07 PM

All replies

  • This sounds like the default behavior of Access: Data changes are saved automatically when leaving the current row. 

    In your case, the question is: Do you need a bound form? Otherwise use the Form_BeforeUpdate event to test whether you want to save the data. E.g.

    Option Compare Database
    Option Explicit
    
    Private m_UpdateCancelled As Boolean
    
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
      Cancel = True
      m_UpdateCancelled = True
      
    End Sub
    
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
     
      If m_UpdateCancelled Then
        Response = DATA_ERRCONTINUE
      Else
        Responst = DATA_ERRDISPLAY
      End If
    
    End Sub

    Wednesday, August 17, 2016 9:05 AM
  • Hi Christopher00010010,

    According to your description, I have made a sample to try to reproduce this issue, unfortunately, I am not able:

    Using Table:

    Create Form:

    Set Property:


    Private Sub CmbStudent_Change()
    
    
      TxtAge.SetFocus
    
      TxtAge.Text = CmbStudent.Column(2, CmbStudent.ListIndex + 1)
    
      TxtAddress.SetFocus
    
      TxtAddress.Text = CmbStudent.Column(3, CmbStudent.ListIndex + 1)
    
    End Sub

    So I suggest that you could provide more information about your issue, for example sample code, screenshot etc, that will help us reproduce and resolve it.

    Thanks for your understanding.

                
    Thursday, August 18, 2016 2:45 AM
  • Hi Christopher. As Stefan said, maybe you don't want to use a bound form. Can you explain to us what is the purpose of your form? What is the purpose of those hidden textboxes? When do you want the data saved in the table?
    Thursday, August 18, 2016 2:59 AM
  • I tried the code Mr. Hoffman listed but it didn't appear to do anything and I am still getting unwanted rows. But I don't understand it completely and will have to look up some things and maybe I'll understand how to make it work for me.

    I'll have to look up what a bound form is, I have never used access until 2 days ago. I did some VB in HS and a lot of C++ & C# in college. Since then my coding is limited to html, php & JavaScript for the occasional website.

    I'm stuck with access as the only option for development as our company doesn't trust web based applications and wont buy visual studio so I could make this in C#. So I'm trying to do most of the work in VB so I'm able to completely understand and control what is happening. I would prefer it if access doesn't do anything but allow me to create a basic interface and let me program everything else with VB.

    For now I've been using access to create queries and populate fields with them via the design view property sheet. But if access is going to manipulate my data because of that I'll change to populating the fields with queries in on load.

    This form is part of a production system I am working on for my company. The modify area form I am working on now is used to change the details of a production area. The database with the production area table and all other tables for this system are in on access file. All of the forms and reports I am creating are in a separate one that links the data. Once finished the data will be hosted on our corporate HQ's server in Ohio while the application access files will be tailored for each user and save on their local workstations in Illinois which are connected with a VPN.

    It wont let me post photos yet buy here is the code for the form so far.

    Private Sub cbxSelectArea_Change()
        
        'Add check to see if they have changed anything, prompt with pop up to continue or cancel select box on click
        
        
        'Update the possible values of the comboboxes
        Me.cbxInputArea.Requery
        Me.cbxOutputArea.Requery
        
        'Update the editing fields in this form with thier current values and enabling modification of thoese valuse
        Me.txtAreaName.Value = Me.cbxSelectArea.Column(1)
        Me.txtAreaDescription.Value = Me.cbxSelectArea.Column(2)
        Me.chkAreaExisits.Value = Me.cbxSelectArea.Column(3)
        Me.chkAreaAbstract.Value = Me.cbxSelectArea.Column(4)
        
        
        'Checking if the area is abstract or not
        If Me.cbxSelectArea.Column(4) Then
            
            'Settign the abstract area to true
            Me.chkAreaAbstract.Value = True
            
            'Enabling the input & output comboboxes
            Me.cbxInputArea.Enabled = True
            Me.cbxOutputArea.Enabled = True
            
            
            'Settign a variable for a loop
            Dim i As Integer
            
            
            'Changing the bound coulmns so we can retrive the value of coulmn 2 using item data
            Me.cbxInputArea.BoundColumn = 2
            Me.cbxOutputArea.BoundColumn = 2
            
            
            'Looping though the input area combobox to find the colum mathing our selected area
            For i = 0 To Me.cbxInputArea.ListCount - 1 Step 1
                
                'Checking to see if input ares index matches our selected areas index
                If Me.cbxSelectArea.Column(5) = Me.cbxInputArea.ItemData(i) Then
                    
                    'Changing the bound coulmns back so we can edit the dispay value
                    Me.cbxInputArea.BoundColumn = 1
            
            
                    'Selecting the current input area in cbxInputArea
                    Me.cbxInputArea.SetFocus
                    Me.cbxInputArea.ListIndex = i
                    
                End If
                
            Next i
            
            
            'Looping though the output area combobox to find the colum mathing our selected area
            For i = 0 To Me.cbxOutputArea.ListCount - 1 Step 1
                
                'Checking to see if output ares index matches our selected areas index
                If Me.cbxSelectArea.Column(6) = Me.cbxOutputArea.ItemData(i) Then
                    
                    'Changing the bound coulmns back so we can edit the dispay value
                    Me.cbxOutputArea.BoundColumn = 1
            
            
                    'Selecting the current output area in cbxOutputArea
                    Me.cbxOutputArea.SetFocus
                    Me.cbxOutputArea.ListIndex = i
                    
                End If
                
            Next i
            
            
            'Changing the bound coulmns back so we can edit the dispay value
            Me.cbxInputArea.BoundColumn = 1
            Me.cbxOutputArea.BoundColumn = 1
            
        
        Else
        
            'Setting input and output areas to descritive names
            Me.cbxInputArea.Value = "Input Area"
            Me.cbxOutputArea.Value = "Output Area"
            
            'Disabling the input & output comboboxes
            Me.cbxInputArea.Enabled = False
            Me.cbxOutputArea.Enabled = False
            
        End If
        
        'Moniter for changes and only enable btn's if a change has occured *****************************************************************
        'Enabling the modification areas that have not allready been enabled
        Me.txtAreaName.Enabled = True
        Me.txtAreaDescription.Enabled = True
        Me.chkAreaExisits.Enabled = True
        Me.chkAreaAbstract.Enabled = True
        Me.btnRevert.Enabled = True
        Me.btnSave.Enabled = True
        
        
        'Setting the focus to the revert button to minimize mistakes
        Me.btnRevert.SetFocus
        
    End Sub

    Private Sub Command42_Click()
        
        Me.txtAreaName.Value = Null
        Me.txtAreaDescription.Value = Null
        Me.txtAreaName.Enabled = False
        Me.txtAreaDescription.Enabled = False
        
        DoCmd.Close
    End Sub

    Thursday, August 18, 2016 5:26 AM
  • I tried the code Mr. Hoffman listed but it didn't appear to do anything and I am still getting unwanted rows. But I don't understand it completely and will have to look up some things and maybe I'll understand how to make it work for me.

    In this case there must be some code or a macro saving the data. Cause the generic Cancel = True blocks the Default way of saving data.

    hmm, how is the form opened? Maybe in DataMode = acFormAdd ?

    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:42 AM
    • Marked as answer by David_JunFeng Wednesday, August 24, 2016 2:37 PM
    Thursday, August 18, 2016 7:57 AM
  • When I close the form, it is taking the data in the text boxes and creating a new row in the table with the data.

    If the combo box is unbound, instead of assigning data to the Value property of each control (the default), assign it to the DefaultValue property.  This does not Dirty the form, so when the user closes the form without inserting any further data, or editing one or more of the default values, the row will not be saved.

    Note that the DefaultValue property is always a string expression regardless of the data type of the column to which a control is bound, so must be wrapped in literal quotes characters, e.g. to assign the current date as the default value would be:

    Me.txtDateEntered.DefaultValue = """" & VBA.Date & """"

    If the combo box is bound, the form will be Dirtied upon making the selection of course, so this won't work.  You'd need to Cancel the update in the event of no further data being inserted or edited..


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Thursday, August 18, 2016 11:29 AM Typo corrected.
    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:42 AM
    • Marked as answer by David_JunFeng Wednesday, August 24, 2016 2:37 PM
    Thursday, August 18, 2016 11:29 AM
  • Hi Christopher. Not sure if this will help with your situation; but to make your form "unbound," make sure the RecordSource property of your Form is empty. Of course, having an unbound form means you'll have to "save" the data to the table yourself. Hope it helps...
    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:42 AM
    • Marked as answer by David_JunFeng Wednesday, August 24, 2016 2:37 PM
    Thursday, August 18, 2016 3:07 PM
  • Thank you all for the information it has been very helpfull. I did find a few of my textboxes has a datasource and removed them but was still getting input. So i went ahead and created a new project and made sure not to use any wizards that might end up leaving macros that I dont know qbout. With the addition of Mr Hoffmans code my project is now working as intended. I am guessing that in my old project i had made some macros when using the wizards as well as having the fields bound and thays why just the code to stop the default behavior did not work. 
    Wednesday, August 24, 2016 9:13 PM