Delete all records in a field

[cotri]

I am looking to put a “Command Button” on a form to delete all records in a table. I found a Macro that deletes the whole table but I just want to delete the records. Any Ideals? Thanks

[ConneXionLost]

Hi cotri,

I recommend you use a delete query for that. No need for a macro.

Cheers,

[cotri]

Hi cotri,

I recommend you use a delete query for that. No need for a macro.

Cheers,

Hello, Thanks for the help here. I went ahead and used a delete query here and it works just the way I wanted except I wanted to run it from a command button on a form. When I go to set it up with a button and specify which query is to be run I see all of my available queries except that one. Is there something I can do to correct this or another way to run it from the Form View? Thanks

[ConneXionLost]

Hi cotri,

When you add a button to a form using a wizard, Access writes a VBA code snippet to the code page of the form to handle the action resulting from clicking the button. Here's a typical example of this:

Private Sub cmdRunAnyQuery_Click()
On Error GoTo Err_cmdRunAnyQuery_Click

    Dim stDocName As String

    stDocName = "qryToTestButton"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdRunAnyQuery_Click:
    Exit Sub

Err_cmdRunAnyQuery_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunAnyQuery_Click
    
End Sub

You're right in saying that Access won't show you a delete query while running the wizard. (I never noticed that before, sorry!)

But it isn't a problem, just pick any query and follow through the wizard as though it were the delete query, then when the wizard is done, open the form's code page and modify the button code stDocName and DoCmd line as follows.

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

    Dim stDocName As String

    stDocName = "qryDeleteData"  'Change the query name here
    DoCmd.OpenQuery stDocName  'Remove the extra bits here

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
End Sub

Note that all I did was change the target query name at stDocname, and removed the ", acNormal, acEdit" from the DoCmd line.

Cheers,

[cotri]

Hi cotri,

When you add a button to a form using a wizard, Access writes a VBA code snippet to the code page of the form to handle the action resulting from clicking the button. Here's a typical example of this:

Private Sub cmdRunAnyQuery_Click()
On Error GoTo Err_cmdRunAnyQuery_Click
 
    Dim stDocName As String
 
    stDocName = "qryToTestButton"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
 
Exit_cmdRunAnyQuery_Click:
    Exit Sub
 
Err_cmdRunAnyQuery_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunAnyQuery_Click
 
End Sub

You're right in saying that Access won't show you a delete query while running the wizard. (I never noticed that before, sorry!)

But it isn't a problem, just pick any query and follow through the wizard as though it were the delete query, then when the wizard is done, open the form's code page and modify the button code stDocName and DoCmd line as follows.

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
 
    Dim stDocName As String
 
    stDocName = "qryDeleteData"  'Change the query name here
    DoCmd.OpenQuery stDocName  'Remove the extra bits here
 
Exit_cmdDelete_Click:
    Exit Sub
 
Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
 
End Sub

Note that all I did was change the target query name at stDocname, and removed the ", acNormal, acEdit" from the DoCmd line.

Cheers,

Hello, So far all has worked well. One minor problem that I would like to fix if its not too much trouble. When I click the command button that runs the delete query the records are deleted but each field on each record (that was just deleted) shows “# Deleted”. I have to close the form and reopen it to clear all of these fields filled with “# Deleted”. Is this easily cured? Thanks

[ConneXionLost]

Hi cotri,

There's no need to quote my entire message in this thread, just use the "Post Reply" button.

For the problem, add:

Me.Requery

after the DoCmd line.

Cheers,

[cotri]

Worked perfectly! Will use “Post Reply” Thanks for all the help and easy to follow instructions.