Code Need To Be Fixed To Delete Field Instead Of Row

[aamer]

Hi am trying to delete only a field "MasterSR" from "Purchases" But when I execute the code it deletes the whole row.
Where as I only want "MasterSR" from "Purchases" table to delete or make it null.


Following is the code, how can I fix this.






Private Sub cboDeleteGPass_AfterUpdate()
On Error GoTo Error_Handler
Dim sSQL As String
Dim pSQL As String
If MsgBox("DELETE All Salary Voucher Records With Voucher Number = " & Me.cboDeleteGPass & "?", vbQuestion + vbYesNo, " C O N F I R M ") = vbNo Then Exit Sub
sSQL = "DELETE * FROM MonSlryVchrTbl WHERE GPPurNumbr = " & Me.cboDeleteGPass
pSQL = "DELETE * FROM Purchases WHERE MasterSR = " & Me.cboDeleteGPass


'Debug.Print sSQL
CurrentDb.Execute sSQL, dbFailOnError
CurrentDb.Execute pSQL, dbFailOnError
CurrentDb.TableDefs.Refresh
Me.cboDeleteGPass.Requery
Me.cboDeleteGPass = ""


Error_Handler_Exit:
On Error Resume Next
Exit Sub
Error_Handler:
Select Case Err
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ")", vbExclamation, "Error in Sub cboDeleteGPass_AfterUpdate of Form_MonSlryVchrFrm"
End Select
Resume Error_Handler_Exit
Resume
End Sub

[June7]

Use ALTER TABLE DROP COLUMN action SQL or TableDefs to modify table. This will delete field from table and data is gone.

Programmatically modifying db structure is usually bad idea and indicative of poor design.

Use UPDATE action SQL to set field to NULL for specific record(s).