Access 2013 - Changes Caused Duplicate (Run-Time Error 3022)

[Jurgen]

I'm trying to add records to an exisiting table called "Topics" (section as of "For Each SelectedTopic In SelectedTopicsC tl.ItemsSelecte d" in the code below). When executing the code i always get "Run-time error '3022': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship". So it goes wrong at the creation of the Autonumber in the field "ID" (= the only field that is indexed - no duplicates). When debugging, line "TopicRecord.Up date" in the code below is highlighted. I have read several posts on this topic on this forum and on other forums but still cannot get this to work - i must be overlooking something....

Private Sub Copy_Click()

Dim SelectedTopic As Variant
Dim JournalEntryToCopyFromCtl, JournalEntryToCopyToCtl, JournalEntryDateCreatedCtl, SelectedTopicsCtl As Control
Dim TopicRecord As Recordset
Dim Counter As Integer
Set TopicRecord = CurrentDb.OpenRecordset("Topics", dbOpenDynaset, dbSeeChanges)
For Each SelectedTopic In SelectedTopicsCtl.ItemsSelected
    TopicRecord.AddNew
    For Counter = 3 To SelectedTopicsCtl.ColumnCount - 1
        TopicRecord.Fields(Counter) = SelectedTopicsCtl.Column(Counter, SelectedTopic)
    Next Counter
    TopicRecord.Fields("JournalEntryID") = JournalEntryToCopyToCtl.Value
    TopicRecord.Fields("DateCreated") = JournalEntryDateCreatedCtl.Value
    TopicRecord.Update
Next SelectedTopic
TopicRecord.Close
Set TopicRecord = Nothing
End Sub

[NeoPa]

Jurgen:
So it goes wrong at the creation of the Autonumber in the field "ID" (= the only field that is indexed - no duplicates).

Are you sure that's the name of the field? It seems you have another called [JournalEntryID] which seems more likely to me, but I'm only guessing from limited information.

Assuming that is the name of the field, and your question had such a misdirection in it, then you should understand that setting the value of an AutoNumber field (as you seem to do in line #13) is generally not correct. There are situations where it makes sense, for instance when recovering data from an archive, but as a general rule the whole point of an AutoNumber is that you don't set it manually but allow Access (or Jet/ACE to be more accurate) to set it for you automatically.

My assumption could be wrong of course. Please let us know the full story.

[Jurgen]

Hi NeoPa,
Thanks for your reply.
The name of the field is indeed "ID" - this is an AutoNumber field and is also the primary key in table "Topics". The field "JournalEntryID " is another field (not indexed, not a primary key) and is the link to another table called "JournalEntries ".
So in line 13 of the attached code i am not setting the value of and AutoNumber field. The AutoNumber field is in TopicRecord.Fie lds(0) or TopicRecord.Fie lds(ID).

[ADezii]

1. I made a couple of changes to your Code and you should now have no problem getting it to work. The main problem that I saw was that you create an Object Variable named SelectedTopicsC tl as Control, but fail to set an explicit Reference to the actual Control.
2. You also need much tighter Scope in your Variable Declarations.
3. The With...End With Construct should be more efficient than simply repeating the Recordset Object.
4. A check for the existence of Items Selected should be performed.
5. I used Literals in this Demo only for convenience sake.
6. Good Luck with your Project.
   

   Dim SelectedTopic As Variant
   Dim SelectedTopicsCtl As Access.ListBox
   Dim TopicRecord As DAO.Recordset
   Dim Counter As Integer
   
   Set SelectedTopicsCtl = Me![cboData]
   If SelectedTopicsCtl.ItemsSelected.Count = 0 Then Exit Sub
   
   Set TopicRecord = CurrentDb.OpenRecordset("Topics", dbOpenDynaset, dbSeeChanges)
   
   For Each SelectedTopic In SelectedTopicsCtl.ItemsSelected
     With TopicRecord
       .AddNew
         For Counter = 3 To SelectedTopicsCtl.ColumnCount - 1
           .Fields(Counter) = SelectedTopicsCtl.Column(Counter, SelectedTopic)
         Next Counter
        
         .Fields("JournalEntryID") = 9999
         .Fields("DateCreated") = Date
       .Update
     End With
   Next SelectedTopic
   
   TopicRecord.Close
   Set TopicRecord = Nothing

@NeoPa:
Such a pleasure to see an old friend! 'Old' not being meant literally (LOL)!

[Jurgen]

Thanks, ADezzi
I did set an explicit reference
Set SelectedTopicsC tl = Forms![Copy Journal Entry]!TopicsToCopy
but had accidently deleted it together with some other code that was not relevant to the problem.
Now i discovered the problem... . Previously the first 23 records were manually deleted from table "Topics". When one adds data to this table in a form, the AutoNumber continues from the highest number already in this AutoNumber field. When running the code however, not sure why, but it seems that it takes the first number available (in this case 1) and as soon as you enter more than 23 records, obviously the newly generated AutoNumber already exists... Strange, but that's what seems to be the problem...

[Jurgen]

ADezii, one more question, why are you using "DAO.Record set" in "Dim TopicRecord As DAO.Recordset" where i was using "Dim TopicRecord As Recordset" ?

[ADezii]

You should always explicitly Reference the Recordset Object that is in use. DAO and ADODB do not always share the same Properties and Methods. Should you have both DAO and ADODB set in your References and you attempt to set a Property or Reference, the compiler will first look at the highest priority in the References Dialog Box. If you reference a valid DAO Recordset Object Property that doesn't exist in ADODB, and ADODB is listed ahead of DAO, you will receive an Error even though the Reference is valid. I apologize for my sloppy explanation, but I am sure that NeoPa will provide a more precise and clearer explanation. I do believe that the term used is to Disambiguate the Reference. This concept is similar to why I declared the Object Variable as Access.ListBox as opposed to simply Control.

[NeoPa]

Hi ADezii.

Good to catch you again :-)

@Jurgen.
ADezii will see you right I'm sure. Thank you for confirming the situation.

[NeoPa]

Jurgen:
Why are you using "DAO.Record set" in "Dim TopicRecord As DAO.Recordset" where i was using "Dim TopicRecord As Recordset" ?

.Recordset is a member of a library. The problem here is that two different libraries both include a .Recordset property. So, to ensure the DAO.Recordset is the one that's used, and understood by anyone reading the project, it makes good sense to disambiguate it as suggested.

@Jurgen.
I find your comments about resetting the AutoNumber quite strange, and I'm not sure I can quite accept the full truth of them.

Now, this is after I already appreciate that you're not the type of person that says things lightly and without intelligent cause. However, I've done some playing in this area myself and here's what I've discovered about when and how AutoNumbers can be reset.

1. With normal use they just keep clocking up forever. This is to avoid the situation you describe.
2. A Compact & Repair of the database will reset the seed, but only to one if the table is empty. What it actually does is to determine the maximum currently in use in the table & increment it.
3. To support backing up and restoring records to/from elsewhere it actually allows setting a value in that field in an APPEND query. In such a case it will find the highest value in that particular dataset and increment it for the new seed.

I suspect what you've seen is indeed from the seed being reset somehow but it's not as simple as you suggest as normally you're protected from that type of thing by the behaviour outlined above.

Of course, it's always possible that there's a gap in my knowledge. I keep finding new ones :-( Nevertheless, my research has led me to believe that such a situation is at least very hard to arrive at.

[Jurgen]

@NeoPa,
Thanks for your further explanation and feedback.
On the strange behaviour of the AutoNumber field, i only reported what i saw with my own eyes, indeed after deleting a number of records and after "Compacting and Repairing" the database - i could not believe it myself either as i was actually convinced that the seed never resets...

[NeoPa]

I hear you Jurgen. I'd need to look more closely to determine if something else is really happening.

As to resetting the seed, that's a common assumption but from playing with various scenarios I've determined that it can be reset, both to 1 and other numbers, by the methods described earlier.

Good luck anyway :-)