We help IT Professionals succeed at work.

Access 2007 - Delete all records in table related to subform

thegreals
thegreals asked
on
Medium Priority
1,356 Views
Last Modified: 2013-11-27
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
Comment
Watch Question

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
thegreals,

Hello again!
;-)

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

Docmd.RunSQL "DELETE * FROM YourSubFormTableName"

JeffCoachman

Author

Commented:
Hi Jeff,

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

Regards,

the greals

Author

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

MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

Author

Commented:
Thanks very much Jeff!!

Can you assist on the error trapping?
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.