Access 2007 - Delete all records in table related to subform

Hi Experts,

Can you please assist with a few macros that I am struggling with in Access 2007:

I want to delete all the records in a subform from it's original table. Can you please assist with a vb macro for this please. Subform is called "Subform_Mistakes".

Thanks for you help,


Regards,

the greals
thegrealsAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
As I posted:

   Docmd.RunSQL "DELETE * FROM YourSubFormTABLENAME"

Note that above I have emphasized *TableName*.
What you are really wanting to delete are the records form the subforms RecordSource (the underlying table), ...not the "Form".
You can only use SQL to delete records from a Table, not a Form.
;-)

You will get this error if you put the name of the *subform* itself in, instead the actual *table* name.

So go into the properties of the form and note the RecordSource.
If it is a table, then use that name.
If it is a query, then open the query and find out the table name.
If it is an SQL statement, click the Build button (the button to the right with the three dots.  [...] ) ...and again, find the table name.

Try again and let me know the result.
;-)

(Note: If the query or SQL recordsource is a multitable query then let me know)

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
thegreals,

Hello again!
;-)

You can do this by deleting all the records in the subforms Recordsource

Docmd.RunSQL "DELETE * FROM YourSubFormTableName"

JeffCoachman
0
 
thegrealsAuthor Commented:
Hi Jeff,

I Know ow to run vb - do I put htis SQL statement in a Private sub?

Regards,

the greals

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
thegrealsAuthor Commented:
Hi,

This is my code:

Private Sub Frm_Discrenpancy_Mulitplier_Single_Reset_Interest_Rate_Click()
    On Error GoTo Err_Frm_Discrenpancy_Mulitplier_Single_Reset_Interest_Rate_Click
    DoCmd.RunSQL "DELETE * FROM Subform_Discrepency_Multipier_Single"

Exit_Frm_Discrenpancy_Mulitplier_Single_Reset_Interest_Rate_Click:
    Exit Sub

Err_Frm_Discrenpancy_Mulitplier_Single_Reset_Interest_Rate_Click:
    MsgBox Err.Description
    Resume Exit_Frm_Discrenpancy_Mulitplier_Single_Reset_Interest_Rate_Click

End Sub

I am getting an error:

The Microsoft jet engine cannot find the form or query " Subform_Discrepency_Multipier_Single"....

I use the space key to select the form. It is the name of the subform in the properties window.

Can you please assist.


Regards,

the greals

0
 
thegrealsAuthor Commented:
Hi Jeff,

I am going to allocated the points,

Just a quick question. How do I modify the msg box when I delete a record and when I click no, stop the Run SQL action is canceled from coming up.

The SQL query works awesome - thanks so much!

Regards,


the greals
0
 
thegrealsAuthor Commented:
Thanks very much Jeff!!

Can you assist on the error trapping?
0
 
Jeffrey CoachmanMIS LiasonCommented:
thegreals,

"How do I modify the msg box when I delete a record and when I click no, stop the Run SQL action is canceled from coming up"

Usually the message is supressed because you "always" want the records to be deleted automatically.
If you wat to be "asked", then that is another branch of logic.
Then you would use something like this:

Dim bytConfirm As Byte
bytConfirm = MsgBox("Delete all subform records?" & vbCrLf & "This operation cannot be undone.", vbQuestion + vbYesNo)
   
    If bytConfirm = vbNo Then
        Exit Sub
    End If
   
    DoCmd.SetWarnings False
        DoCmd.RunSQL "Delete * from YourSubFormTableName"
    DoCmd.SetWarnings True

End Sub



To supress the warning message, and always delete automatically use this:

    DoCmd.SetWarnings False
        DoCmd.RunSQL "Delete * from YourSubFormTableName"
    DoCmd.SetWarnings True

;-)

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.