refresh after SQL Delete statement

[Flamer]

I'm writing code In vb and when I run this code it says it cant refresh because the object is not open. When I put in "Adodc1.RecordSet.Open" it works perfectly the first time.
But If i run the function again, I get an error saying it cant open the recordset cause its already open.

I've tried putting adodc1.recordset.close, right before the function ends figuring that i'd be ok, but alas that didn't work either. Is there any way I can check to see if the recordset is already open?

I would really appreciate the help.. Thanks in Advance.


IF Adodc1.Recordset.RecordCount > 0 THEN

Adodc1.RecordSource = "DELETE FROM EquipOut WHERE Sequence = " & SeqNum
Adodc1.Refresh
MsgBox "DELETED SeqNum"

END IF

[mdake]

Your problem is probably because you are using the RecordSource property to delete records. You are replacing whatever you used to get the records to begin with when you say ...
Adodc1.RecordSource = "DELETE FROM EquipOut WHERE Sequence = " & SeqNum

now you try and refresh, but Adodc1 thinks its record source is "DELETE FROM EquipOut WHERE Sequence = " & SeqNum

You can use the Connection or Command Objects Execute Method to issue SQL commands to the DB.

Or you can delete the record from the Recordset and then refresh...

If Adodc1.Recordset.RecordCount > 0 THEN 
 
Adodc1.Recordset.MoveFirst
Adodc1.RecordSet.Find "Sequence = " & SeqNum
Adodc1.Recordset.Delete
Adodc1.Refresh 
MsgBox "DELETED SeqNum"
End If

You need to have an updateable Recordset to do this.

[Rockman]

To determine if a recordset is open or not, use the .State property of the recordset.

hth

- Jeff