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