DELETE PROBLEM - Cannot DELETE after ADDing new record or UPDATING ?

Have a small problem here...very minor but a pain nonetheless.

Is anyone aware of any issues that would cause this problem:

If I open my form, browse through my records, and select a record to delete, and press Delete, it deletes no problem.


I can add a record, and it Inserts Into a table. If I press delete directly after, it will not delete. No error, no delete either.
or
I can update a record successfully. If I press delete directly after this, it will not delete either. Again no error, no delete either.


If I unload the form, and then reload the form again (Exit the form and re-open), I can delete the record fine.


Does anyone know what would be causing this issue?

PS - Its nothing to do with Locking/Enabling buttons etc. I checked that already. The delete button is fully enabled and unlocked ready for use.

After you perform an INSERT or UPDATE or DELETE on your table, you should requery your recordset. Structure has changed, so your recordset should reflect the change too.

I use a SQL UPDATE to update my table.

My Recordset is not closed after each action,
after Updating, it is set to .MoveLast

It won't allow me to use adoPrimaryRS.Requery after I do my update, it says "Operation is not allowed in this context".

I cannot use adoPrimaryRS.Update either, as it says it would create duplicate values in the primary key.

Okay, I've tried opening and closing recordset before and after each transaction - Its not making any difference.

It performs as it did before, not allowing a Delete after an Add or Update etc.

But, I have just noticed, not just delete, but after adding/updating a new record - ALL action buttons do not function :(

ie. after pressing Update...
Delete does not work
Amend does not work (it will allow typing data into txtbox but not update)
Browse buttons do not work (First/Last/Back/Forward)

I have even tried including a Refresh command button to no avail. As it is yet another action button, it will not work either.

The only thing I think that works is Cancel, but then this is set to .CancelUpdate (so the new record is then removed) :(


Any thoughts on this anyone ?

I've decided to post my code... please make any suggestions you see fit, but please not major overhauls as I've spent too much time on this already and need to be moving on (although in saying that Adding/Updating/Deleting are essentials!)

I have dropping the opening and closing of the recordset as it was making no difference and resorted to my original code, which is displayed below.


Here is my code for the Add button:

Code:

---

Private Sub cmdAdd_Click()

txtStatus.Text = "Adding New Record..."


'db is globally dimmed

Set db = New Connection
db.CursorLocation = adUseServer
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ConnPath & "\DatabaseDB.mdb;"


Dim InsertNewTask As String

InsertNewTask= "INSERT INTO Task(TaskID) VALUES('" & txtTaskID & "')"

db.Execute InsertNewTask


End Sub

---

And here is my code for the Update button:

Code:

---

Private cmdUpdate_Click()


Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ConnPath & "\DatabaseDB.mdb;"

Dim UpdateTaskRecord As String

UpdateTaskRecord = "UPDATE Task SET TaskDescription='" & txtTaskDescription & "', TaskDate='" & txtTaskDate & "', TaskTime='" & txtTaskTime & "', CustomerID='" & txtCustomerID & "' WHERE TASK.TaskID='" & txtTaskID & "'"


db.Execute UpdateTaskRecord


txtStatus.Text = "Save Successful"


  If CStr(adoPrimaryRS.AbsolutePosition) = (-1) Then
  txtCount.Text = "Total: 0"
  Else: txtCount.Text = "Total: " & CStr(adoPrimaryRS.RecordCount)
  End If


  mbEditFlag = False
  mbAddNewFlag = False
  mbDataChanged = False

Exit Sub


If Not IsEmpty(adoPrimaryRS) Then
cmdDelete.Enabled = True
Else
cmdDelete.Enabled = False
End If

End Sub

---

Please make any suggestions you think of. Thanks

Just one thought:
- Close the connection when you've finished with it
- Poss refresh db - see whether you need to

The code you posted seems to stay open (the db connection isn't closed) and with Access this may prevent anything else happening until it is closed.

The refresh of the db is because sometimes the update is not actually updated immediately, this was mentioned for access 97, and may happen for 2k+. For that you needed to refresh the database, poss via connection. It used to be (scrabbles through distant memories) on the dbsystem.refreshcache or something, I only had this happen once or twice for vb with access, as the mdb wasn't immediately updated and the immediate refresh of the form didn't see any change.


Vince