Deleting records from a table using a delete button (1 Viewer)

[Jabell]

Howdy,

I have a list box containing a query result, from a search text query in an address table.

How do you delete the record from the source table via a delete button in the form using the selected address in the list box?

 

[dcx693]

Assuming the addresses are unique, you can use a Delete query worded like this:

"Delete * FROM your_source_table WHERE your_table_addressfield='" & Me.your_form_listbox_name & "';"

Put the code in the Click event of the command button, have some error checking to make sure there was an actual choice made in the listbox, then requery the listbox after the delete query code using code like this:

Me.your_form_listbox_name.Requery

 

[Jabell]

It tells me that the macro does not exist or cannot be found. When inputting the code I have put.

Private Sub Command27_Click()
= "Delete * FROM prospects WHERE id= '" & Me.Results & "';"

End Sub

Does this look right?

 

[dcx693]

Sorry, forgot to note that you need to express it like this if you're running Access 97 with DAO:
Currentdb.Execute "Delete * FROM prospects WHERE id= '" & Me.Results & "';"

If you're using Access 2000 or higher with ADO, use:
CurrentProject.Connection.Execute "Delete * FROM prospects WHERE id= '" & Me.Results & "';"

Those should work assuming id is a text field. If id is a numeric field, then use:
WHERE id= " & Me.Results & ";"

 

[Jabell]

ok, it works now!!

thanks a lot, dcx693!!!