Add a record in Access 2016

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

Answer 1

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

Answer 2

No



Answer 3

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 [e-mail].

Answer 4

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

Answer 5

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

Answer 6

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?

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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...

Answer 11

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 [e-mail].

Answer 12

  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?

Answer 13

done.  Here is the link to it.

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

Answer 14

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 [e-mail].

Answer 15

  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!

Answer 16

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

Answer 17

Here is the link to the OneDrive Folder.

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

Answer 18

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

Answer 19

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

Answer 20

  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?

Answer 21

I just added this code and it is abending big time now!  I am getting a 3022 abend!

Answer 22

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 [e-mail].

Answer 23

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.