[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2007 - Delete all records in table related to subform

Posted on 2009-02-15
7
Medium Priority
?
1,339 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
0
Comment
Question by:thegreals
  • 4
  • 3
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23647517
thegreals,

Hello again!
;-)

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

Docmd.RunSQL "DELETE * FROM YourSubFormTableName"

JeffCoachman
0
 

Author Comment

by:thegreals
ID: 23652526
Hi Jeff,

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

Regards,

the greals

0
 

Author Comment

by:thegreals
ID: 23654132
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 23656887
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
 

Author Comment

by:thegreals
ID: 23663862
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
 

Author Closing Comment

by:thegreals
ID: 31547209
Thanks very much Jeff!!

Can you assist on the error trapping?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23668583
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question