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

 

[xenou]

That makes it harder then. Your code is really written as if the form is unbound. Also if your users are using the form to add data and also to scroll through and view data then you also have to handle two types of situations (one for adding records where you are trying to pre-populate new data, and one for view records where you are just displaying existing data).

 

[MHamid]

That's what I was afraid you'd say.
Yes, in this form the user will add data and will also need to scroll through data in order to see if they will need to update or delete any of their records.

Any suggestions how to make this possible? As it stands now, all fields are bound so the user is able to scroll through records and there is also a filter by user so they can scroll through their own records.

The Add Record and scroll through records were functioning well together. However, with the request that certain fields had to stay filled so the user won't have to refill them and me noticing that the REF ID field was not changing after a record was added caused some concern and I didn't want the user to be confused as to whether or not their records are being added or not. And also, now that I added a new field " Date Entered" and an Attachment field, the add record button stopped working.

I know the requestor is asking for a lot in this form, but I think that somehow it is possible.

 

[Micron]

You could consider removing built in navigation controls. Providing your own would allow navigation to and fro without permitting access to a new record. A button to navigate to a new record can grab any required field data and move to a new record. When you create the unique Id for that record is a matter of choice (usually I prefer when the record is saved rather than when started). If you do this in conjunction with a temp table you should be able to exert complete control.

 

[MHamid]

Hi Micron,

I'm not sure I'm understanding what you're saying.

I do have two navigation buttons (left and right) with the following code:

Private Sub NextRecord_Click()
If Me.CurrentRecord = Me.RecordsetClone.RecordCount Or Me.NewRecord = True Then
    MsgBox "You are on the Last Record!"
  Else
    DoCmd.GoToRecord , , acNext
End If
End Sub

Private Sub PreviousRecord_Click()
If Me.CurrentRecord = 1 Then
    MsgBox "You are on the First Record!"
  Else
    DoCmd.GoToRecord , , acPrevious
End If
End Sub

Is this what you're saying?

Is there any way that I can attach the database so you can see it?

 

[xenou]

It should be possible to save the last entered data. That's just a matter of some code or what have you. But you have watch for new records and do your prefilling only when a new record is started (there should be some kind of OnNew event ... I really haven't done this in a while lol so all I can tell you from memory is that it is probably not called "OnNew"). Also test the case where this is the first new record of the day - the very first one should work, even though nothing has yet been saved from a previous save.

As far as attachments go, I've never saved attachments in a DB so I don't know. I only have saved the addresses where files are saved (which is simply text data - a file location).

As far as dates, it should work if you add the new field to your save command. Since you are creating a raw sql statement the date value must be a valid date that can successfully convert from text to date , and the syntax must use hashes surrounding a literal date text:
insert into Foo (MyDateField) Values (#1/1/2018#)


Edit:

Note: I'm not sure what you mean by Ref IDs. If the Ref ID is an autonumber key you cannot prefill it. You should only see it when viewing previously saved records, not when creating new records. Ideally you just don't show it at all - the main thing about autonumber keys is that they generally don't have meaning to the user, but only to differentiate unique records in the database.

For instance, a user would be interested in:
Kermit, Frog
Miss Piggy, Pig
Fozzie, Bear
Gonzo, Unknown

The user wouldn't care about:
1, Kermit, Frog
2, Miss Piggy, Pig
3, Fozzie, Bear
4, Gonzo, Unknown

because the numbers really don't carry meaning.

Obviously, this is only theoretical - your users may well be interested in the autonumber values, but in any case they still can't assign them or change them nor should they worry about them since they are required and no successful save could either not have them or duplicate one of them.

 

[Micron]

Yes, that's what I meant. However, I'm also saying consider a button to Add New... (based on the visible record) where you pass whichever field values you need to the new record. Not really sure how you see the process being played out since this thread has a bit of history, much of which doesn't seem to apply to the originally stated problem.

Let's say you want to pre-fill a new record with some data. That data can be grabbed from a record the user is seeing, where they say "this one is what I want to base my new record on". Let's say you create the ID at the beginning of the record creation. If you copy the "static" fields to a new record and increment the number and the record creation is cancelled, how have you dealt with the new ID if you've created it? Delete the record? Then a concurrent user who is in the process of creating a record has started with an ID that is +1 higher than the one that was cancelled, thus you have gaps in the ID. If that's not important, then no big deal. If it is, you have a problem.

Suppose you create the ID when the user commits the record. Then the ID gets created at the moment of the save (you have to decide whether or not to close the form to control future edits). The concurrent user previously mentioned now saves their record, and their record ID gets incremented by one based on the last saved record. All should be good.

Whether or not you need a temp table depends on how you want to integrate this into the whole process, which includes editing. Edits don't need a record creation process, just an edit. In your case, it seems the ID field should always be locked or disabled so it can't be fudged with. As for users not knowing if their record creation was successful, you can present a message to announce that fact, but the code should use the Execute method of the DoCmd object with the dbFailOnError parameter to provide assurance that the creation was successful.

Hope that helps.

 

[MHamid]

Hello,

I just got my add button to work again. I see what you're both saying about the Ref ID. Users may delete records and that will cause ID's to be missing in the record, which is fine because I have an audit trail that keeps track of all deleted records. I think I will leave the Ref ID (auto-generated numbers) as a reference point for the users. So, it will be ok for Ref ID's to be missing.
I also think I will do a new record button that will give the user 2 options. 1) Add a completely blank new record and 2) Copy data from previous entry (pre-filled option).

What do you think?

 

[MHamid]

Hello,

Ok so I did get my Add Record button to work and I also got the new record button to work with the two options I mentioned above.

Add Record Code:

Private Sub Add_Record_Click()
Dim strNotes As String
Dim strOutliers As String
Dim strOther As String
Dim strOther2 As String
Dim SQL_Text 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
 
SQL_Text = "INSERT INTO QAMaster([EnteredBy],[DateEntered],[CycleMonth],[ReportType],[DateReviewed],[ReviewerType],[Reviewer],[ReviewerReportArea],[MainSection],[TopicSection],[Ownership-Individual],[Ownership-Team],[Count],[Priority],[ApprovedBy],[L1],[L2],[L3],[Other],[RepeatAsk],[Exception],[Notes],[Outliers],[AdditionalOutlier])" & _
    "VALUES ('" & Me.EnteredBy & "','" & Me.DateEntered & "','" & 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 & ");"
'Debug.Print SQL_Text
MsgBox "Record Added Successfully", vbInformation, "SUCCESS!"

Me.txtOther.Visible = False
Me.txtOther2.Visible = False

New Record Code:

Private Sub cmdNewRecord_Click()
Dim MyFirstField As String
Dim MySecondField As String
Dim MyThirdField As String
Dim MyFourthField 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
Dim MyEleventhField As String
Dim answer As String
answer = MsgBox("Do you need a blank new record?", vbYesNo + vbQuestion, "Blank Record Needed?")
    If answer = vbYes Then
        DoCmd.GoToRecord , , acNewRec
        Me.EnteredBy = Environ("USERNAME")
        Me.DateEntered = Format(DateValue(Now()), "Short Date")
    Else
        'Copy fields to variables
        MyFirstField = Me.EnteredBy
        MySecondField = Me.CycleMonth
        MyThirdField = Me.DateReviewed
        MyFourthField = Me.ReportType
        MyFifthField = Me.ReviewerType
        MySixthField = Me.Reviewer
        MySeventhField = Me.ReviewerReportArea
        MyEigthField = Me.Individual
        MyNinthField = Me.Team
        MyTenthField = Me.MainSection
        MyEleventhField = Me.TopicSection
        'Go to New Record
        DoCmd.GoToRecord , , acNewRec
        'Reverse process and plug old values into new record
        Me.EnteredBy = MyFirstField
        Me.CycleMonth = MySecondField
        Me.DateReviewed = MyThirdField
        Me.ReportType = MyFourthField
        Me.ReviewerType = MyFifthField
        Me.Reviewer = MySixthField
        Me.ReviewerReportArea = MySeventhField
        Me.Individual = MyEigthField
        Me.Team = MyNinthField
        Me.MainSection = MyTenthField
        Me.TopicSection = MyEleventhField
        Me.DateEntered = Format(DateValue(Now()), "Short Date")
    End If
End Sub

Thank you for all of your help. I did ensure that it is being added to my QAMaster table. I have also ensured that no new records are being added to my audit trail.

Now on to my next issue that started yesterday ... delete button stopped working (Ugh!!!)

 

[MHamid]

Update:

The above method was not liked by the requestors.
Therefore, I did two codes. One for the addrecord button and another in the afterinstert event.


AddRecord_Click:

Private Sub AddRecord_Click()
On Error GoTo errHandler
CurrentDb.Execute "INSERT INTO QAMaster([EnteredBy], [DateEntered], [CycleMonth], [CycleYear], [ReportType], [DateReviewed], [ReviewerType], [Reviewer], [ReviewerReportArea], [MainSection], [TopicSection], [OwnershipIndividual], [OwnershipTeam], [Count], [Priority], [ApprovedBy], [L1], [L2], [L3], [Other], [RepeatAsk], [Exception], [Notes], [Outliers], [AdditionalOutlier], [Hyperlink], [PageNo])" & _
    "VALUES ('" & Me.EnteredBy & "',  '" & Me.DateEntered & "', '" & Me.CycleMonth & "',  '" & Me.CycleYear & "', '" & 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 & "', '" & Me.txtOther & "', '" & Me.Repeat & "', '" & Me.Exception & "', '" & Me.Notes & "', '" & Me.Outliers & "', '" & Me.txtOther2 & "', '" & Me.txtHyperlink & "', '" & Me.txtPageNo & "');", dbFailOnError
    DoCmd.GoToRecord , , acNewRec
    
exitHere:
Exit Sub
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub

Form_AfterInsert

Private Sub Form_AfterInsert()
On Error GoTo errHandler
Dim ID As Long
    ID = DMax("RefID", "QAMaster")
If Not Me.NewRecord Then
    DoCmd.GoToRecord , , acNewRec
End If
    Me.Approved = DLookup("ApprovedBy", "QAMaster", "RefID=" & ID)
    Me.CycleMonth = DLookup("CycleMonth", "QAMaster", "RefID=" & ID)
    Me.CycleYear = DLookup("CycleYear", "QAMaster", "RefID=" & ID)
    Me.DateReviewed = DLookup("DateReviewed", "QAMaster", "RefID=" & ID)
    Me.ReportType = DLookup("ReportType", "QAMaster", "RefID=" & ID)
    Me.MainSection = DLookup("MainSection", "QAMaster", "RefID=" & ID)
    Me.TopicSection = DLookup("TopicSection", "QAMaster", "RefID=" & ID)
    Me.ReviewerType = DLookup("ReviewerType", "QAMaster", "RefID=" & ID)
    Me.Reviewer = DLookup("Reviewer", "QAMaster", "RefID=" & ID)
    Me.ReviewerReportArea = DLookup("ReviewerReportArea", "QAMaster", "RefID=" & ID)
    Me.Individual = DLookup("OwnershipIndividual", "QAMaster", "RefID=" & ID)
    Me.Team = DLookup("OwnershipTeam", "QAMaster", "RefID=" & ID)
    Me.txtHyperlink = DLookup("Hyperlink", "QAMaster", "RefID=" & ID)
    Me.txtPageNo = DLookup("PageNo", "QAMaster", "RefID=" & ID)
        
    Me.EnteredBy = Environ("USERNAME")
    Me.DateEntered = Format(DateValue(Now()), "Short Date")
    Me.CycleMonth.SetFocus
    Me.CycleYear.SetFocus
    Me.DateReviewed.SetFocus
    Me.ReportType.SetFocus
    Me.MainSection.SetFocus
    Me.TopicSection.SetFocus
    Me.ReviewerType.SetFocus
    Me.Reviewer.SetFocus
    Me.ReviewerReportArea.SetFocus
    Me.Individual.SetFocus
    Me.txtHyperlink.SetFocus
    Me.txtPageNo.SetFocus
    Me.txtCount.SetFocus
    
    Me.txtOther.Visible = False
    Me.txtOther2.Visible = False
    
    MsgBox "Record Added Successfully!" _
    & vbCrLf & "Form is ready for new record.", vbInformation, "SUCCESS!"
exitHere:
Exit Sub
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub

This works without adding another button for the user to click and it also adds the data from the prior record added instead of two records prior (just found this issue today, lol).

Thought I'd post the final product for others running into a situation like this for their database.

Thank you

 

[MHamid]

Correction:

The above did actually caused the auto-generated number to skip (ie. adding new record twice).

So I have updated the code to add the record, refresh the form and update fields with prior record added. This code will add the record to the bound table, refresh the form and will add new record with some of the prior record's data. Now, I see the ref ID (auto-generated number) not skipping a record, and the data that is being copied is from the record I just added.

Private Sub AddRecord_Click()
On Error GoTo errHandler
Dim ID As Long
 
Forms![Data Entry].Requery
    ID = Nz(DLast("RefID", "QAMaster"))
If Not Me.NewRecord Then
    DoCmd.GoToRecord , , acNewRec
End If
    Me.Approved = DLookup("ApprovedBy", "QAMaster", "RefID=" & ID)
    Me.CycleMonth = DLookup("CycleMonth", "QAMaster", "RefID=" & ID)
    Me.CycleYear = DLookup("CycleYear", "QAMaster", "RefID=" & ID)
    Me.DateReviewed = DLookup("DateReviewed", "QAMaster", "RefID=" & ID)
    Me.ReportType = DLookup("ReportType", "QAMaster", "RefID=" & ID)
    Me.MainSection = DLookup("MainSection", "QAMaster", "RefID=" & ID)
    Me.TopicSection = DLookup("TopicSection", "QAMaster", "RefID=" & ID)
    Me.ReviewerType = DLookup("ReviewerType", "QAMaster", "RefID=" & ID)
    Me.Reviewer = DLookup("Reviewer", "QAMaster", "RefID=" & ID)
    Me.ReviewerReportArea = DLookup("ReviewerReportArea", "QAMaster", "RefID=" & ID)
    Me.Individual = DLookup("OwnershipIndividual", "QAMaster", "RefID=" & ID)
    Me.Team = DLookup("OwnershipTeam", "QAMaster", "RefID=" & ID)
    Me.txtHyperlink = DLookup("Hyperlink", "QAMaster", "RefID=" & ID)
    Me.txtPageNo = DLookup("PageNo", "QAMaster", "RefID=" & ID)
        
    Me.EnteredBy = Environ("USERNAME")
    Me.DateEntered = Format(DateValue(Now()), "Short Date")
    Me.CycleMonth.SetFocus
    Me.CycleYear.SetFocus
    Me.DateReviewed.SetFocus
    Me.ReportType.SetFocus
    Me.MainSection.SetFocus
    Me.TopicSection.SetFocus
    Me.ReviewerType.SetFocus
    Me.Reviewer.SetFocus
    Me.ReviewerReportArea.SetFocus
    Me.Individual.SetFocus
    Me.txtHyperlink.SetFocus
    Me.txtPageNo.SetFocus
    Me.txtCount.SetFocus
    
    Me.txtOther.Visible = False
    Me.txtOther2.Visible = False
    MsgBox "Record Added Successfully!" _
    & vbCrLf & "Form is ready for new record.", vbInformation, "SUCCESS!"
        
exitHere:
Exit Sub
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub