Run Time Error 3709

[bryn30]

can't figure this one out.

this code works to archive when the tables are local. However I need this to be a split database with the tables on the server. When that happens the code errors.
it stops on the Delete portion. and it cannot find the search key.
here is the error;

Run Time Error 3709
The search key is not found in any record

here is the Code;

If Not IsNull(Me.Text116) Or Not IsNull(Me.Scopus) Then
Dim holder As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Archive&quot ' Change name appropriately

holder = MsgBox("Are You Sure You Want To Archive?", vbYesNo)
If vbYes Then

Me.ArcDate = Now()
Me.Archived = True

With rs
.AddNew
' Here is where you would specify what fields you want to add to your second table.
![OrderStatus] = Me.OrderStatus
![Case ID] = Me.IPS
![Site Name] = Me.Site_Name
![PimsCaseID] = Me.PimsCaseID
![ProductID] = Me.ProductID
![Region] = Me.Region
![Market] = Me.Market
![OrderDate] = Me.OrderDate
![StatusDate] = Me.StatusDate
![Notes] = Me.Notes
![TestTurnUp] = Me.TestTurnUp
![ReasonCode] = Me.ReasonCode
![OC] = Me.OC
![calday] = Me.calday
![busday] = Me.busday
![Obusday] = Me.OAB
![OCalday] = Me.OAC
![ABusday] = Me.ASAB
![ACalday] = Me.ASAC
![AdjStatusDate] = Me.StatusDate
![ArcDate] = Me.ArcDate
![DumpDate] = Me.DumpDate
![Archive] = Me.Archived
![Otype] = Me.Otype
![Exitstatus] = Me.Exitstatus
![Scopus] = Me.Scopus
.Update
.Close
End With
Else
End If

db.Close
DoCmd.SetWarnings False

'Delete the current record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

DoCmd.SetWarnings True

Else
MsgBox "PIMS Number and Scopus Number are required", vbOKOnly

End If
Me.Notes.Enabled = False

 

[Databaseguy]

Is the recordset based on more than one table ? If so, you have to include the DISTINCT keyword in your query or SQL satatement when deleting. (Or , set the queries unique reords property to yes)