Duplicate Record Created on update...

[ToshTrent]

Hi all,

When I try to update a record using the code below, somehow a duplicate record is created, could someone please advise me on how to fix this?

[blue]
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM qry_MediaRequests WHERE ClntD = " & cboClients.ItemData(cboClients.ListIndex) & " AND MgD = " & cboMagazines.ItemData(cboMagazines.ListIndex), DBcn, adOpenStatic, adLockOptimistic

rs!ClntD = cboEditClients.ItemData(cboEditClients.ListIndex)

rs!MgD = cboEditMagazines.ItemData(cboEditMagazines.ListIndex)

rs!MdCd = Trim(txtEditMediaCode.Text)
rs!Stts = cboStatus.ItemData(cboStatus.ListIndex)
rs!FP = strFormProvided
rs!Cmmnts = txtEditComments.Text

If cboStatus.ItemData(cboStatus.ListIndex) = 2 Then
rs!CmpltdDt = CompletedDate
rs!CmpltdBy = UCase(User)
Else
rs!CmpltdDt = ""
rs!CmpltdBy = ""
End If

rs.Update
rs.Close[/blue]

The section of code above is from my Save Command Button,



[red]
Thankyou[/red]
Matt

 

[Spellman]

after your rs.open statement try putting in the line rs.edit. Hopefully this will sort it for you.

Mark

The key to immortality is to make a big impression in this life!!

 

[ToshTrent]

rs.edit is not a valid option,

I have rs.editmode but this doesn't work either, as the compiler expects more code...?

Thanx

 

[Spellman]

Try using ad opendynamic instead of ad open static. If that doesn't work then what you can do is build the sql update statement and use Dbcn.execute sqlstatement to update it. Sorry I'm not at my machine at the moment so I can't be of more help.

Mark

The key to immortality is to make a big impression in this life!!

 

[CCLINT]

ToshTrent, make sure the code isn't executing twice for some reason and using the same data.

Put a break point in it, or a

Debug.Print "Adding: " & ClntD
and viewing the results in the immediate/debug window

or even just a message box, after the rs.Update

 

[ToshTrent]

Hi thank you for your comments,

I've worked out the problem...

In the search procedure it located the record, then closed the record set, in the save procedure it doesn't relocate the original record. I have now deleted the rs.close from the search procedure.

Thanx again.