Error Checking in Excel
Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Adding new Record is skipping primary key

  1. #1
    Board Regular
    Join Date
    Jan 2013
    Posts
    311
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding new Record is skipping primary key

    Hello,

    I have a code in my database to add a new record into a table.
    The primary key is the RefID and I have this field in the form so the user can scroll through records within the form.

    When a record is added from the form into a table, I have it coded to go to the next record with various fields copied from the prior record into the new record. However, when a new record is ready to be populated with the remaining fields, I've noticed that the RefID goes from 39 to 42. It skips a record or two. So when I go to look at the table. I see a record added for 40, record 41 is skipped and the new record added is 42. Did I so something wrong with my code?

    Code:
    Private Sub Add_Record_Click()
    Dim strNotes As String
    Dim strOutliers As String
    Dim strOther As String
    Dim MyFirstField As String
    Dim MySecondField As String
    Dim MyThirdField As String
    Dim MyFouthField As String
    Dim MyFifthField As String
    Dim MySixthField As String
    Dim MySeventhField As String
    Dim MyEigthField As String
    Dim MyNinthField As String
    Dim MyTenthField As String
    
    If IsNull(Me.Notes) Then
        strNotes = "Null"
    Else
        strNotes = "'" & Me.Notes & "'"
    End If
    
    If IsNull(Me.Outliers) Then
        strOutliers = "Null"
    Else
        strOutliers = "'" & Me.Outliers & "'"
    End If
    
    If IsNull(Me.txtOther) Then
        strOther = "Null"
    Else
        strOther = "'" & Me.txtOther & "'"
    End If
    
    CurrentDb.Execute "INSERT INTO QAMaster([Entered By],[Cycle Month], [Report Type], [Date Reviewed], [Reviewer Type], [Reviewer], [Reviewer Report Area], [Main Section], [Topic Section], [Ownership], [Count], [Priority], [Approved By], [L1], [L2], [L3], [Other], [Exception], [Notes], [Outliers])" & _
        "VALUES ('" & Me.EnteredBy & "', '" & Me.CycleMonth & "', '" & Me.ReportType & "', '" & Me.DateReviewed & "', '" & Me.ReviewerType & "', '" & Me.Reviewer & "', '" & Me.ReviewerReportArea & "', '" & Me.MainSection & "', '" & Me.TopicSection & "', '" & Me.Ownership & "', '" & Me.txtCount & "', '" & Me.PriorityLevel & "',  '" & Me.Approved & "','" & Me.L1 & "', '" & Me.L2 & "', '" & Me.L3 & "', '" & Me.txtOther & "', '" & Me.Exception & "', '" & Me.Notes & "', '" & Me.Outliers & "');", dbFailOnError
    MsgBox "Record Added Successfully", vbInformation, "SUCCESS!"
    
    'Copy fields to variables
    MyFirstField = Me.EnteredBy
    MySecondField = Me.CycleMonth
    MyThirdField = Me.DateReviewed
    MyFouthField = Me.ReportType
    MyFifthField = Me.ReviewerType
    MySixthField = Me.Reviewer
    MySeventhField = Me.ReviewerReportArea
    MyEigthField = Me.Ownership
    MyNinthField = Me.MainSection
    MyTenthField = Me.TopicSection
    
    'Go to New Record
    DoCmd.GoToRecord , , acNewRec
    
    'Reverse the process and plug old values into new record
    Me.EnteredBy = MyFirstField
    Me.CycleMonth = MySecondField
    Me.DateReviewed = MyThirdField
    Me.ReportType = MyFouthField
    Me.ReviewerType = MyFifthField
    Me.Reviewer = MySixthField
    Me.ReviewerReportArea = MySeventhField
    Me.Ownership = MyEigthField
    Me.MainSection = MyNinthField
    Me.TopicSection = MyTenthField
    
    End Sub
    Thank you

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,571
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding new Record is skipping primary key

    I presume the field is an autonumber field? Then between 49 and 51 you must be doing something to start a new record, then cancel it. You probably should figure out why although it's not important for autonumbers to be consecutive. Where this could be an issue is that if a related record were being saved with the missing number and then you lose the number in the other record.

    some odd things in the code:
    you assign Me.Notes value to strNotes then you use Me.Notes in the sql instead of strNotes.
    you also use Me.Whatever instead of the variable that holds the data
    you are assigning the word "Null" to a string variable (strNotes = "Null") which is not the same as making the field Null. If you're trying to actually make the field Null, it cannot be a string variable. It has to be a variant. If you're actually wanting to store the word "Null" in a table field, that is ill advised. Null is a reserved word and should not be used for this. It will probably cause you grief later.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  3. #3
    Board Regular
    Join Date
    Jan 2013
    Posts
    311
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding new Record is skipping primary key

    Hello,

    Yes, the field is an auto-generated number. I need it to be consecutive without skipping any numbers. I'll look into this and see what I'm doing wrong.

    As for the odd things you've noticed. This was a code that I found. I wanted to allow the fields to be left blank. I don't need the word "Null" to be stored in the field. If I make it as below and use strNotes to add into the table. It should still work like I need to, correct?
    Code:
    If IsNull(Me.Notes) Then
        strNotes = ""
    Else
        strNotes = Me.Notes
    End If

    UPDATE: I just made the change I described above and clicked the add record button. This added the record twice, but I don't really understand where in the code it's stating to add the record twice.

    My original issue before I decided to add the copy fields, go to new record, and reverse the process was that I needed the auto-generated number to update to the next available number with some fields already pre-populated so the user would not have to make the same selections again.


    Thank you
    Last edited by MHamid; Dec 7th, 2017 at 12:44 PM. Reason: Adding additional notes

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,571
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding new Record is skipping primary key

    Yes, the field is an auto-generated number. I need it to be consecutive without skipping any numbers.
    This is not a good idea. Suggest you read http://www.utteraccess.com/wiki/Autonumbers
    Whatever it is you're trying to do probably has a much simpler solution as (I hope) your thread re: producing a list of edited controls. Maybe the beginning would be to start with a recordset clone, but you'd have to be clear on what the goal is and why.
    Also
    If I make it as below and use strNotes to add into the table. It should still work like I need to, correct?
    maybe, but putting empty strings into a table isn't a good idea IMHO. If you search on that field using Is Null, you won't get the ones that contain "". They are not the same thing. If you must set a field to Null (because this is for a vba sql statement) and use a variable, the variable has to be a Variant as already mentioned I think, and the assignment is Null, not "Null".
    Last edited by Micron; Dec 7th, 2017 at 09:25 PM.

  5. #5
    Board Regular
    Join Date
    Jan 2013
    Posts
    311
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding new Record is skipping primary key

    Hello,

    I read the article and I get why it's not a good idea, but I have nothing else that would make the record unique since everything else can be duplicated. This database is to keep track of errors. Now these errors can be made by the same person within the same month. The ID Primary key is the only thing I have that makes the record unique. Do you think I'm going to have to create a unique identifier for each record that is not an auto-generated number?

    The purpose of the add record data button is to keep track of errors into a table. I need to allow the user to be able to leave certain combo boxes blank so if no data is needed it would leave the column blank when added to the table. I don't need the word Null. I just need the ability to not choose anything from a combo box or leave text field blank. All of my items on the form are combo boxes with the exception of Entered By, Date reviewed, RefID, Count, Exceptions, Priority and Notes.

    Am I overcomplicating an easy task?

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,985
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Adding new Record is skipping primary key

    This was your original requirement:
    I need it to be consecutive without skipping any numbers.
    if you need that, you really shouldn't use autogenerated primary keys (IDs) since they can't easily be guaranteed not to skip numbers (the easiest way to create a gap would be to start a new record, then abort the data entry without committing the new record, which "eats" the next ID but never saves it.

    Generally, if you do want or need to have numbers in sequence, you have to alter the form's save event so that right before the record is saved, you query for the current max ID and then save the record with the next number in sequence.

    But also if you can just not worry about the gaps then you can use the autonumber IDs !!
    Last edited by xenou; Dec 8th, 2017 at 03:15 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,571
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding new Record is skipping primary key

    Am I overcomplicating an easy task?
    Pretty much impossible to say. If you try to read your posts while imagining you have absolutely no knowledge of what exactly is going on, you might realize how little understanding we have of the process. If you're copying the id from the prior record over to a new record, about all you can do and stay on the side of good design is to make that id value the FK of a new record. However, that new record wouldn't be going into the same table IHMO. Maybe there's more to this than you/we think as far as table structure goes.

  8. #8
    Board Regular
    Join Date
    Jan 2013
    Posts
    311
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding new Record is skipping primary key

    Hello,

    Ok, I think I'm just going to start from scratch with this issue.

    I have a data entry form where I will need to add, edit, and delete records into a table.

    These are the fields that will need to be filled using the form:
    Entered By
    Date Entered
    Cycle Month
    Report Type
    Date Reviewed
    Reviewer Type
    Reviewer
    Reviewer Report Area
    Main Section
    Topic Section
    Ownership-Individual
    Ownership-Team
    Count
    Priority
    Approved By
    L1 (Quality Category L1)
    L2 (Quality Category L2)
    L3 (Quality Category L3)
    Other (If Other is selected in L3)
    Repeat Ask (check box)
    Exception (check box)
    Notes
    Outliers
    Additional Outliers

    The fields that could be left blank are:
    Other (If Other is selected in L3)
    Notes
    Outliers
    Additional Outliers

    The fields that I will need zeroed out after new record is added are:
    Count
    Priority
    L1
    L2
    L3
    Repeat Ask
    Exception
    Notes
    Outliers
    Additional Outliers
    Approved By
    Other
    This is because the other fields could be repeated for the next record and instead of making the user reselect these, we want to allow the user to be able to just update the selections that will change for the new record (if that makes any sense).

    In the form I have added the REF ID (auto-generated number) to allow users to scroll through records. However, I have noticed that when I add record, the REF ID does not change. It will stay the same, but when a new record is added it is added as a new REF ID.
    Is there a way to get a new record to appear after a new record was added instead of seeing the same REF ID?
    For instance, once a new record is added it shows as REF ID 3 and the fields to be zeroed out are zeroed out, but the REF ID still shows as 3.

    Below is the code that I am using that works without skipping REF ID when added to the QAMaster table.
    Code:
    Private Sub Add_Record_Click()
    Dim strNotes As String
    Dim strOutliers As String
    Dim strOther As String
    Dim strOther2 As String
    
    If IsNull(Me.Notes) Then
        strNotes = ""
    Else
        strNotes = Me.Notes
    End If
    If IsNull(Me.Outliers) Then
        strOutliers = ""
    Else
        strOutliers = Me.Outliers
    End If
    If IsNull(Me.txtOther) Then
        strOther = ""
    Else
        strOther = "'" & Me.txtOther & "'"
    End If
    If IsNull(Me.txtOther2) Then
        strOther2 = ""
    Else
        strOther2 = "'" & Me.txtOther2 & "'"
    End If
    
    CurrentDb.Execute "INSERT INTO QAMaster([Entered By],[Cycle Month], [Report Type], [Date Reviewed], [Reviewer Type], [Reviewer], [Reviewer Report Area], [Main Section], [Topic Section], [Ownership-Individual], [Ownership-Team], [Count], [Priority], [Approved By], [L1], [L2], [L3], [Other], [Repeat Ask], [Exception], [Notes], [Outliers], [Additional Outlier])" & _
        "VALUES ('" & Me.EnteredBy & "', '" & Me.CycleMonth & "', '" & Me.ReportType & "', '" & Me.DateReviewed & "', '" & Me.ReviewerType & "', '" & Me.Reviewer & "', '" & Me.ReviewerReportArea & "', '" & Me.MainSection & "', '" & Me.TopicSection & "', '" & Me.Individual & "', '" & Me.Team & "', '" & Me.txtCount & "', '" & Me.PriorityLevel & "',  '" & Me.Approved & "','" & Me.L1 & "', '" & Me.L2 & "', '" & Me.L3 & "', '" & strOther & "', '" & Me.Repeat & "', '" & Me.Exception & "', '" & strNotes & "', '" & strOutliers & "', " & strOther2 & ");", dbFailOnError
    MsgBox "Record Added Successfully", vbInformation, "SUCCESS!"
    
    Me.txtCount = ""
    Me.PriorityLevel = ""
    Me.L1 = ""
    Me.L2 = ""
    Me.L3 = ""
    Me.Repeat = 0
    Me.Exception = 0
    Me.Notes = ""
    Me.Outliers = ""
    Me.txtOther2 = ""
    Me.Approved = ""
    Me.txtOther = ""
    Me.Refresh
    Me.txtOther.Visible = False
    Me.txtOther2.Visible = False
    
    End Sub
    Is there a better way to do what I need than the code that I have?

    Thank you
    Last edited by MHamid; Jan 2nd, 2018 at 11:16 AM.

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,985
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Adding new Record is skipping primary key

    Are you using a bound form or an unbound form (more specifically, are the fields bound to a table or are they unbound?)

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #10
    Board Regular
    Join Date
    Jan 2013
    Posts
    311
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding new Record is skipping primary key

    yes, the fields are bound to the table

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •