Delete a record from record ID

[Yabanize]

Hi



I have an access database, I want to create a form that has a text field and you type the ID of a record in the textbox and press the button, then the record is deleted from the table

Can anyone help with this?

Thanks

[jzwp11]

Welcome to the forum!

The query would look something like this (in SQL view)

DELETE * FROM yourtablename WHERE yourIDfieldname= forms!yourformname!yourtextboxnamefortheID

You'll have to substitute your own names where indicated.

You can create the query and save it. Then on your form, add a command button using the wizard and choose Miscellaneous-->Run Query and select the name of the query you created.

If you have critical data in the database, I would recommend backing it up before you run the query in case things do not go as planned.

[Yabanize]

Hi, I tried that and when I click the button it brings up a datasheet view of the id I typed in. I also noticed the table name is supposed to be the table name and the field when I did it in design view. Can you try it in access first to see if it works please? Thanks, this help is greatly appreciated

[jzwp11]

I've attached an example database with a table, form and query. You can open the query in design view or SQL view to see how the query construction. The datasheet view will just show you what record will be deleted. You will want to open the form, enter an ID and then click the button which should run the query automatically for you.

[Yabanize]

Hi, I just downloaded it and there is only a table, TblExample. I dont see any forms or queries in the side bar

[Yabanize]

http://d.pr/WpTK Heres my database, maybe you could edit it to include it, the form is called pickup

[jzwp11]

You just need to modify your query to the following shown in red; DB attached

DELETE Tasks.ID, *
FROM Tasks
WHERE Tasks.ID=forms!pickup!text0;

[Yabanize]

Thanks, Do you know how to turn off the message that is displayed "You are about to run a delete query that will modify information in your table?" and the message after that, you are about to delete 1 record etc, also after its deleted it changes to #Deleted, can i just make it go away completely?

[jzwp11]

deleted it changes to #Deleted, can i just make it go away completely?

The #Deleted should go away once you close and reopen the table or requery/refresh the form depending on where you are seeing it.

As to getting rid of the warnings, you can turn warnings off just before you run the query and then turn them back on again right afterwards, but I have heard that doing so is generally not recommended. The better way is to execute the query a little differently. In the attached DB, I modified the code in the On Click event of the button. The code now builds the query dynamically and runs it. After running the code, a message saying that the record was deleted is returned (you'll have to modify the code according to your table and field names).

[Microsoft Access Database]

You can either use a querydef in VBA or pick up the ID in a normal query

Create a delete query and in the criter where line for the ID field enter

=forms!formname!textboxname