DELETE with VBA

[timothyl]

Hello, I have a bound form with navigation buttons, user's want to delete records, have the following code, but keep getting aplication defind error and #### marks in controls(Deletion dose work), these go away if I refresh but form dose not execute refresh code, must do manualy. any ideas. Thanks

Private Sub cmdDelete_DblClick(Cancel As Integer)
On Error GoTo Err_cmdDelete_Click
Dim intIndex As Integer
Dim SQL_Text As String
SQL_Text = "DELETE * FROM ZtblChairBuildDetails " & _
"WHERE [ChairBuildDetailsID] = " & Me.txtChairBuildDetailsID.Value & ";"

Forms!BuildSheet!Status.Value = "Available" 'Put equipmen back into stock

Forms!BuildSheet!StorageLocation.Value = Forms!BuildSheet!PhoStorage.Value
DoCmd.RunSQL (SQL_Text), acEdit

Me.Refresh
Forms!BuildSheet.Forms.Refresh
Me.Form.Refresh
Exit_cmdDelete_Click:

Exit Sub
cmdDelete_Error:
Select Case Err.Number
Case 3059
Forms!BuildSheet!StorageLocation.Value = "Out" 'Take equipmeback out of stock
Forms!BuildSheet!Status.Value = "Committed"
MsgBox "You have canceled the delete operation.", vbOKOnly, "Delete Canceled"
Exit Sub
Case Else
Me.Refresh
MsgBox "Error number: " & Err.Number & " has occurred. " & Err.Description, vbOKOnly, "Error"
End Select
Err_cmdDelete_Click:
Me.Refresh
MsgBox Err.Description
Resume Exit_cmdDelete_Click
Me.Refresh
End Sub

 

[Mr. B]

timothyl,

Try using:
Me.requery

instead of the me.Refresh

The Refresh method only refreshes the data in the current record set. The Requery method actually causes a re-querying of the records.

 

[timothyl]

That did it Mr B, thank you

 

[missinglinq]

Just to add, Refresh doesn't remove deleted records (as you've already found out) nor does it re-order records or show new records, when added by another user. It also doesn't remove records that no longer meet a recordset's criteria. It only shows changes that have been made to existing records. For everything else you have to use Requery.

 

[timothyl]

Thank you Missingling, I did not know any of that

 

[missinglinq]

That's why Access is so much fun, to me! There's always something new to learn!