none
Add a record in Access 2016 RRS feed

  • Question

  • I am trying to save a record and then add a blank record to the form for the user.

    It is a save and then add a blank record process.  It works like a charm on the 1st record

    I add.  Creates a blank form for data entry. Then I key in the data and press the button

    again, then I get a run-time error of 2105.  Here is the code and can someone plz help me

    to understand why I am getting this and how to get around it?  Also, why does the message display

    twice?

    Private Sub cmdSaveRecord_Enter()
       MsgBox "Record has been successfully Added."

       DoCmd.GoToRecord , , acNewRec
    '  DoCmd.RunCommand (acCmdRecordsGoToNew)
    End Sub

    Monday, December 18, 2017 10:06 PM

All replies

  • Is your form's Data Entry property set to Yes by chance?

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, December 18, 2017 10:38 PM
  • No

    

    Monday, December 18, 2017 10:43 PM
  • Hello,

    Please use the click event of the command button.

    CommandButton.Enter Event (Access) The Enter event occurs before a control actually receives the focus from a control on the same form or report.

    If you use enter event, the button would get focused when you click the button, so the enter event fires. When the form goes to a new record, the command button would get focused again, the enter event fires again.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 19, 2017 7:45 AM
    Moderator
  • I changed it to a Click event.  After the message displays, it abends with a '2105', you cannot go to the specified record.  Here is the code.  I am just trying to save the record and put another blank one to the screen for the user to continue to enter data.  I am trying to add a new blank record on the screen.

    Private Sub cmdSaveRecord_Click()
      MsgBox "Record has been successfully Added. Click Event"
     DoCmd.GoToRecord , , acNewRec

     End Sub


    • Edited by ballj_351 Tuesday, December 19, 2017 11:07 AM missed setting the alert me checkbox
    Tuesday, December 19, 2017 11:06 AM
  • I found it.  Prior to the addnewrec, the following code makes everything work just fine.  Current

    working code:

    Private Sub cmdSaveRecord_Click()
      MsgBox "Record has been successfully Added. Click Event"
      Form.AllowAdditions = True
      DoCmd.GoToRecord , , acNewRec
     End Sub

    • Marked as answer by ballj_351 Tuesday, December 19, 2017 2:52 PM
    • Unmarked as answer by ballj_351 Tuesday, December 19, 2017 3:46 PM
    Tuesday, December 19, 2017 2:52 PM
  • This works the 1st time I open the form,then after that, I get the 2105 error again,.

    1.  Close the form.

    2.  Open the form.  Add data, click on save and add blank.  Blank form displays.

    3.  Enter data and then click on save and  add blank.  I get the 2105 runtime error.

    This is not making sense!  Any ideas, comments on this issue?

    Tuesday, December 19, 2017 3:49 PM
  • If you want a form solely for inserting new records, moving to an empty form after each record is saved, then set the form's DataEntry property to True (Yes).  All you need to do then is requery the form in the Save button's Click event procedure with:

        Me.Requery

    However, you would probably also want to prevent the user saving the record by any other means than clicking the button, e.g. by closing the form.  This would require code in the form's module.  You'll find an example of such code in SaveDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the user is forced to save/undo a record and/or close the form by means of command buttons, each of which is enabled/disabled by code in the form's module on the basis of the current state of the form.  It does not move the form to an empty record after saving, however, and it also allows manual navigation to other records.  To change this behaviour, all that would be necessary would be to set the form's DataEntry property to True, and add a line to the save button's Click event procedure as follows:

    Private Sub cmdSave_Click()

       Const MESSAGETEXT = "Save record?"

       If Me.Dirty Then
           ' if user confirms set variable to True and attempt to save record
           If MsgBox(MESSAGETEXT, vbQuestion + vbYesNo, "Confirm") = vbYes Then
               blnSaved = True
               On Error Resume Next
               RunCommand acCmdSaveRecord
               ' if record cannot be saved set variable to False
               If Err <> 0 Then
                   blnSaved = False
               Else
                   ' disable buttons
                   ' move focus to another control first
                   Me.FirstName.SetFocus
                   Me.cmdSave.Enabled = False
                   Me.cmdUndo.Enabled = False
                   Me.cmdClose.Enabled = True
                   Me.Requery               ' new line added
               End If
           Else
               blnSaved = False
           End If
       End If
        
    End Sub

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Tuesday, December 19, 2017 6:19 PM Hyperlink added.
    Tuesday, December 19, 2017 6:15 PM
  • This is not working, here is the code:

    Private Sub cmdSaveRecord_Click()
      MsgBox "Record has been successfully Added. Click Event"
      Form.AllowAdditions = True
      Me.Requery
      DoCmd.GoToRecord , , acNewRec
    '  Me.Requery
     End Sub

    Tuesday, December 19, 2017 6:27 PM
  • If the form is *solely* for inserting new records, you don’t need to set the AllowAdditions property to True in the code, but by default in the form's properties sheet.  You do need to set the form's DataEntry property to True however.  You don't need any code to specifically move to an empty new record, the combination of a True DataEntry property and calling the Requery method does that already, so the basic code is:

      MsgBox "Record has been successfully added."
      Me.Requery

    But to force the user to save the record *only* by means of the button you need to do more elsewhere in the form's module.  See my demo for details.

    If, on the other hand, you want to force the user to save a record only by means of the button, and then go to an empty new record, but at the same time be able to navigate to other records then the only change to my demo would be to add the following line to the button's evnt procedure in place of the line which calls the Requery event:

        DoCmd.GoToRecord acForm,Me.Name,acNewRec

    Ken Sheridan, Stafford, England

    Tuesday, December 19, 2017 6:45 PM
  • This works the 1st time I open the form,then after that, I get the 2105 error again,.

    1.  Close the form.

    2.  Open the form.  Add data, click on save and add blank.  Blank form displays.

    3.  Enter data and then click on save and  add blank.  I get the 2105 runtime error.

    This is not making sense!  Any ideas, comments on this issue?

    Hi,

    My guess is the record save may be failing due to invalid data. Try to do the data validations at the form level to give you more flexibility for handling errors.

    Just my 2 cents...

    Tuesday, December 19, 2017 8:13 PM
  • Hello,

    I suggest you share your database via OneDrive and paste the link here. Please visit Share OneDrive files and folders


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 20, 2017 3:18 AM
    Moderator
  •   Yes this is what I have done, per your direction.  Works great, excluding the fact when I click on Save & Add New, it goes to the CmdSave_Click procedure before it goes to the BeforeUpdate event.  I have a message to display in the Save_Click event and this is why I know it displays first.

      Now I need to work on the Save & Copy record event.  I want to save the new record and then keep all values on the screen, excluding the prime key to make entry of the next record easier for the user.  Any ideas or examples?

    Wednesday, December 20, 2017 4:21 AM
  • done.  Here is the link to it.

    https://1drv.ms/u/s!Ahlc2yUeKSx2kmQwBL_6jIfmbudq

    Wednesday, December 20, 2017 4:30 AM
  • Hello,

    Many fields like Year_Car are set required, so you would get runtime error 2105 if one of them is empty. To fix it, please check if the required fields are null before going to next new record by DoCmd.GoToRecord , , acNewRec

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, December 20, 2017 5:49 AM
    Moderator
  •   I have removed all required field checks from the table definition.  I can enter the form and save the 1st record I enter.  It gives me a new/blank record on the screen.  Yet, I can enter all fields, but it will not save the record.  All validation is done on the form.  Yet, when I click save, it fires off the cmdSave_Click() event and displays my message.  Then it goes to the Befoe_Update() event and does the form edit checks.  Even though all edit checks, pass, it still does not add the record.  It is like I have to exit/close the form and then come back in and add another record.  To me, it should have done the form validation in Before_Update, before executing the save.  Then having to close and open is not good either. I know things like this is standard data entry, but Access is all over the place.

      Really wish I could speak with someone online and understand why all of this is going on.  I have done enough reading and testing, where I think this should be working consistently now, but it is not.  I would like to speak with an expert and get input from them. Very very challenging!

    Wednesday, December 20, 2017 1:07 PM
  • You are speaking with experts, some of the best in the world.  The issue is we don't have access to your db to examine.  Any chance you can post a copy (removing any sensitive information first) to a file share, OneDrive, DropBox, ...?

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, December 20, 2017 3:26 PM
  • Here is the link to the OneDrive Folder.

    https://1drv.ms/u/s!Ahlc2yUeKSx2kmQ7fG1rS_oP6kp1

    Wednesday, December 20, 2017 3:32 PM
  • I commented out your BeforeUpdate event and it works just fine, so the issue lies with your beforeupdate validation.

    On a side note, you need to properly normalize your data into tables and not have one huge table as is the case now.  Whenever you create numbered field like Rebate_Prog_1, Rebate_Prog_2 ... Leasing_Prog_1, Leasing_Prog_2 ... Dealer_Cash_Prog_1, Dealer_Cash_Prog_2 ... Safety_1, Safety_2 ... Options1, Options2 ... you have a normalization problem as these should be in separate tables with a 1-many relationship to the main table.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, December 20, 2017 5:30 PM
  • This works for me:

    Option Compare Database
    Option Explicit
    
    Private Sub Close_Form_Click()
        Me.Undo
        DoCmd.Close acForm, Me.Name
    End Sub
    
    Private Sub cmdExitForm_Click()
        DoCmd.Close acForm, Me.Name
    End Sub
    
    Private Sub cmdSaveCopy_Click()
        MsgBox "Record has been successfully Added. Copying data to new record. Cick"
        Form.AllowAdditions = True
    
        On Error Resume Next
        DoCmd.RunCommand acCmdSelectRecord
        If (MacroError = 0) Then
            DoCmd.RunCommand acCmdCopy
        End If
        If (MacroError = 0) Then
            '       DoCmd.GoToRecord , , acNewRec
            'DoCmd.RunCommand acCmdRecordsGoToNew
        End If
        If (MacroError = 0) Then
            DoCmd.RunCommand acCmdSelectRecord
        End If
        If (MacroError = 0) Then
            DoCmd.RunCommand acCmdPaste
        End If
        If (MacroError <> 0) Then
            Beep
            MsgBox MacroError.Description, vbOKOnly, ""
            DoCmd.GoToRecord , , acLast
        End If
    
        Model_N1.SetFocus
    End Sub
    
    Private Sub cmdSaveRecord_Click()
        On Error GoTo Error_Handler
    
        If Me.Dirty Then Me.Dirty = False
        DoCmd.GoToRecord , , acNewRec
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Sub
    
    Error_Handler:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: cmdSaveRecord_Click" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Sub
    
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If IsNull(Me.Model_N1) Then
            MsgBox "Model Number from the Manufacturer cannot be blank!"
            Cancel = True
            Me.Model_N1.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.Year_Car) Then
            MsgBox "Year of the Car is Missing"
            Cancel = True
            Me.Year_Car.SetFocus
            GoTo End_Check
        Else
            If Me.Year_Car < 2016 Then
                MsgBox "Year of Car must be > 2015"
                Cancel = True
                Me.Year_Car.SetFocus
                GoTo End_Check
            Else
                If Me.Year_Car > 2050 Then
                    MsgBox "Year of Car must be < 2050"
                    Cancel = True
                    Me.Year_Car.SetFocus
                    GoTo End_Check
                End If
            End If
        End If
    
        If IsNull(Me.Model) Then
            MsgBox "Model of the Car is Missing"
            Cancel = True
            Me.Model.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.Make) Then
            MsgBox "Make of the Car is Missing"
            Cancel = True
            Me.Make.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.Series_1) Then
            MsgBox "Series of the Car is Missing"
            Cancel = True
            Me.Series_1.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.Transmission) Then
            MsgBox "Transmission of the Car is Missing"
            Cancel = True
            Me.Transmission.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.Model_Combo) Then
            MsgBox "Model Combo of the Car is Missing"
            Cancel = True
            Me.Model_Combo.SetFocus
            GoTo End_Check
        End If
    
        If Me.City_MPG = 0 Then
            MsgBox "City MPG must be Greater than 0"
            Cancel = True
            Me.City_MPG.SetFocus
            GoTo End_Check
        End If
    
        If Me.City_MPG > 49 Then
            MsgBox "City MPG must be less than 50"
            Cancel = True
            Me.City_MPG.SetFocus
            GoTo End_Check
        End If
    
        If Me.Hwy_MPG = 0 Then
            MsgBox "Highway MPG must be Greater than 0"
            Cancel = True
            Me.Hwy_MPG.SetFocus
            GoTo End_Check
        Else
            If Me.Hwy_MPG > 49 Then
                MsgBox "Highway MPG must be less than 50"
                Cancel = True
                Me.Hwy_MPG.SetFocus
                GoTo End_Check
            End If
        End If
    
        If Me.Invoice_No_Shipping > 99999.99 Then
            MsgBox "Invoice Shipping mut be < 99999.99"
            Cancel = True
            Me.Invoice_No_Shipping.SetFocus
            GoTo End_Check
        End If
    
        If Me.MSRP_No_Shipping > 99999.99 Then
            MsgBox "MSRP Shipping must be < 99999.99"
            Cancel = True
            Me.MSRP_No_Shipping.SetFocus
            GoTo End_Check
        End If
    
        If Me.Shipping > 9999.99 Then
            MsgBox "Shipping must be < 9999.99"
            Cancel = True
            Me.Shipping.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.SIR_Rate_36) Then
            MsgBox "SIR Rate 36 is Missing"
            Cancel = True
            Me.SIR_Rate_36.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.SIR_Rate_48) Then
            MsgBox "SIR Rate 48 is Missing"
            Cancel = True
            Me.SIR_Rate_48.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.SIR_Rate_60) Then
            MsgBox "SIR Rate 60 is Missing"
            Cancel = True
            Me.SIR_Rate_60.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.SIR_Rate_72) Then
            MsgBox "SIR Rate 72 is Missing"
            Cancel = True
            Me.SIR_Rate_72.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.SIR_Rate_84) Then
            MsgBox "SIR Rate 84 is Missing"
            Cancel = True
            Me.SIR_Rate_84.SetFocus
            GoTo End_Check
        End If
    
        If Me.Residual_Percentage > 99 Then
            MsgBox "Residual Percentage must be < 99"
            Cancel = True
            Me.Residual_Percentage.SetFocus
            GoTo End_Check
        End If
    
        'If Me.Mny_Fac_Zero < 9.99999 Then
        '    MsgBox "Money Factor Zero must be < 9.99999"
        '    Cancel = True
        '    Me.Mny_Fac_Zero.SetFocus
        '    GoTo End_Check
        'End If
    
        'If Me.Mny_Fac_Mny < 9.99999 Then
        '    MsgBox "Money Factor Money must be < 9.99999"
        '    Cancel = True
        '    Me.Mny_Fac_Mny.SetFocus
        '    GoTo End_Check
        'End If
    
        If Me.Man_Lse_Pmt_Zero > 999.99 Then
            MsgBox "Money Lease Payment Zero must be < 999.99"
            Cancel = True
            Me.Man_Lse_Pmt_Zero.SetFocus
            GoTo End_Check
        End If
    
        If Me.Man_Lse_Pmt_Mny > 999.99 Then
            MsgBox "Money Lease Payment Money must be < 999.99"
            Cancel = True
            Me.Man_Lse_Pmt_Mny.SetFocus
            GoTo End_Check
        End If
    
        If Me.Man_Money_Dwn > 999.99 Then
            MsgBox "Money Down Lease Special must be < 999.99"
            Cancel = True
            Me.Man_Money_Dwn.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.Miles_per_Yr) Then
            MsgBox "Miles Per Year is Missing"
            Cancel = True
            Me.Miles_per_Yr.SetFocus
            GoTo End_Check
        End If
    
        If IsNull(Me.Months_Lease) Then
            MsgBox "Lease Month is Missing"
            Cancel = True
            Me.Months_Lease.SetFocus
            GoTo End_Check
        End If
    
    End_Check:
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
        DoCmd.GoToRecord , , acNewRec
    End Sub
    
    


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, December 20, 2017 5:49 PM
  •   I understand what you are saying about normalization.  Yet, I want to validate the fields and ensure the entry is correct.  I removed them from the validation rules on the table and added the validation on the form.  I also removed from making them required on the table, to just editing the fields on the form. 

      If I removed them from the Before_Update, then I would need to validate them on each field on the form and that could become a headache to the user.  I thought the Before_Update event would trigger first and then the save_record would happen.  Regardless of what I do now, it only saves 3 records @ a time, then I have to shut down and the function does not work any longer.

      I would like to speak with you.  Is this possible or screen share?

    Wednesday, December 20, 2017 6:01 PM
  • I just added this code and it is abending big time now!  I am getting a 3022 abend!
    Wednesday, December 20, 2017 6:10 PM
  • Hello,

    I notice you have post a new thread. Do you fix the issue? I suggest you mark your solution or helpful post as answer to close this thread. If you still have problem, i suggest you test with a few field firstly. You may use field's beforeupdate event to check if the data meets your requirement and use form's beforeupdate event to check if there is empty field.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 22, 2017 5:20 AM
    Moderator
  • No I did not fix my issue.  I just moved forward onto more development and figure I will come back to this issue later.  Sending too much time on something that should be easy to resolve.
    Friday, December 22, 2017 10:11 AM