I get no current record error when i attempt to run the database; here is the code:

Private Sub Command55_Click()

'Hard Edits
If Eligibilityub = "" Or IsNull(Eligibilityub) Then
MsgBox "Please Select The Eligibility For The DSP", vbCritical, "Incomplete"
Exit Sub
End If

Dim db As Database
Set db = CurrentDb()
Dim tOSID As String
tOSID = Forms!Frm_main!OSIDub
'Add the new record to tbl_Sums
Dim rstAddNew As Recordset
Set rstAddNew = db.OpenRecordset("select * from tbl_Sums where OSID = '" & tOSID & "' and SAP_Number = '" & Me!SAP_Numberub & "';")

rstAddNew.Edit

rstAddNew!Eligibility = Eligibilityub

If Eligibilityub = "Standard" Then
rstAddNew!Completed = True
End If

If Eligibilityub = "100% Dept. Avg." Then
rstAddNew!Completed = True
End If

If Eligibilityub = "Ineligible" Then
rstAddNew!Completed = True
End If

If Eligibilityub = "50% Dept. Avg." Then
rstAddNew!Completed = True
End If

Forms!Frm_main!Prev_Call1 = rstAddNew!Eligibility

rstAddNew.Update
rstAddNew.Close
Set rstAddNew = Nothing

Dim rstCurrent As Recordset
Set rstCurrent = db.OpenRecordset("select * from tbl_Sums where SAP_Number = '" & tOSID & "' and DSP= '" & DSPub & "';")
Dim rstUpdate As Recordset
Set rstUpdate = db.OpenRecordset("Select * from tbl_DSP where OSID = '" & tOSID & "';")

rstUpdate.Edit
rstUpdate!Edited = True
rstUpdate.Update

If rstCurrent!Completed = True Then
rstUpdate.Edit
rstUpdate!Completed = True
rstUpdate.Update
End If

rstUpdate.Close
rstCurrent.Close
Set rstUpdate = Nothing
Set rstCurrent = Nothing

Forms!Frm_main.Requery
Forms!Frm_main.Refresh

DoCmd.Close

End Sub




any assistance is greatly appreciated.

Hi
Would I be right is suggesting you Move & Loop through the recordset...!!

rstAddNew.MoveFirst
..edit record
rstAddnew.MoveNext

rstUpdate.MoveFirst
..edit record
rstUpdate.MoveNext

Where exactly in the code should i put this and how should it look?

Hi
Try:


If your only returning a single record then rem out the MoveNext line..

Thanks for your assistance, that worked wonderfully. When the data is saved to the tbl_Sums, it records multiple entries...how could I code it so that it updates the entry that is already in the tbl_Sums and not put a new one (there should be no duplicates)?

Hi
You have two halfs working in the code which one gives you the dup..!!

The data in the form is being pulled from a query and saved to tbl_Sums. Just one of the fields is edited and the data is saved again to tbl_Sums. This process is causing 2 entries to be saved. Is there a way to get tbl_Sums to only update the field that is changed rather than adding a new entry?

Hi
Not sure what's happening as your code only edits a record..
Could it be relationship and form are saving an instance of the record..!!
You may have to post the db so I can have a look.

here is the db

Hi
Your attachment failed..
Compact size (500kb limit)
Compressed and zipped..