Delete Record

[rekhatr10]

Hi Everyone,


I have a combo box where the user can choose the name and click on the delete button. But whenever I do that I am using the command button wizard and every time it deletes the first records. How do I make it so that it deletes the record which is chosen in combo box?
Also, how do i use a sql query to delete a record. e.g I am using docmd.runsql "Delete from doc where docname = testing " and I get a msg box to enter a value.

Thank you for your help
Rekha

 

[KenReay]

Hi

"I have a combo box where the user can choose the name and click on the delete button. But whenever I do that I am using the command button wizard and every time it deletes the first records. How do I make it so that it deletes the record which is chosen in combo box?"

You must first move to the record, using code in after update event of combo, see FAQ in this forum for ho to do that


"Also, how do i use a sql query to delete a record. e.g I am using docmd.runsql "Delete from doc where docname = testing " and I get a msg box to enter a value"

docmd.runsql "Delete from doc where docname = 'testing'; "

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[BobStubbs]

Part 1 - I get the combo box to display the chosen record, then the [Delete] button will delete it. Example code, placed in the After Update event of an unbound combo box on the form:

Private Sub cmbChooseRecord_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[RecordID] = " & Str(Me![cmbChooseRecord])
    Me.Bookmark = rs.Bookmark
End Sub

Part 2 - sample code from my database, which deletes a record via SQL in a VBA procedure:

dim strSQL as string

strSQL = "DELETE tblEmployees.EmployeeID FROM tblEmployees "
strSQL = strSQL & "WHERE tblEmployees.EmployeeID=" & strIDToDelete & ";"

DoCmd.RunSQL strSQL

Note that EmployeeID is a numeric field in the table. If it was a string, you would need slightly different syntax including single quotes:

dim strSQL as string

strSQL = "DELETE tblEmployees.EmployeeID FROM tblEmployees "
strSQL = strSQL & "WHERE tblEmployees.EmployeeID='" & strIDToDelete & "' ;"

DoCmd.RunSQL strSQL

3. Remember that a good way to find SQL syntax is to build a query which does the job - e.g. deleting one record from a table - then look via SQL view at the SQL which Access generates.


I hope that this helps.


Bob Stubbs

 

[rekhatr10]

Bob,

I used the sql statment to delete the record. It worked

Thank you
RR