Adding new Record is skipping primary key

[MHamid]

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?

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

 

[Micron]

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.

 

[MHamid]

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?

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

 

[Micron]

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

 

[MHamid]

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?

 

[xenou]

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

 

[Micron]

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.

 

[MHamid]

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.

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

 

[xenou]

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

 

[MHamid]

yes, the fields are bound to the table