You are here:

Using MS Access/Existing autonumbered records being hijacked by new records


Am experiencing a very unusual phenomenon that's corrupting a table called tblWorkOrders, which contains a unique autonumber as the primary key for each record.

It occurs when I use this code to add a new record:

Set rsThis = DBthis.OpenRecordset(strSQL)

If Not rsThis.EOF Then

   'Here begins the process of creating a workorder

   rsWO!WOType = "Scheduled PM"
   rsWO!DateDone = NewDate
   rsWO!Status = "Done"
   rsWO!WOArchived = True
   rsWO!(a number of other text fields...)

   rsWO.Move 0, rsWO.LastModified (to refresh the record)

End If

What's happening:

Each new record is not being established at the next available autonumber, instead the process hijacks an existing record (and its previously issued autonumber) somewhere deep down in the table and replaces the contents of that original record with the new data established in the recordset. (The existing autonumber is untouched.)

Any ideas?

Your insight is always appreciated, and on a related topic, your defense of desktop Access in your blog entry was an inspired piece of writing. Very astute observations on the unique value of desktop Access.

Best regards,

Rich C.

That is wierd. Can you show me how rsWO is defined?

I'm not a big fan of using DAO for data entry. I would be more incline to use:

Dim strSQL As String

strSQL = "INSERT INTO WOtable (WOType, DateDone, Status, WOArchived, etc. ) " & _
         "VALUES('Scheduled PM', #" & datevalue & "#, 'Done', True, etc.
CurrentDB.Execute strSQL, dbFailOnError  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.