Solved

Is it possible to delete records from a table that are not part of the filter on a form for that table?

Posted on 2014-10-10
3
184 Views
Last Modified: 2014-10-10
I have a form with a subform (child) that is a datasheet view. The user can filter records of the form. With that, I want to know if it is possible to have some code that will delete all of the records in the table that are not part of the filtered data.

I do not know how to write a delete query string for that.

If this type of action is possible, could someone please show me an example?

Thanks
0
Comment
Question by:PBLack
  • 2
3 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
VBA for that would look similar to this:

Dim strSQL as string
Dim lngID as long 
lng = {this is where you would define the criteria for the records to delete}
strSQL = "DELETE * FROM SomeTable WHERE [IDField] = " & lngID
CurrentDB.Execute strSQL, dbfailonerror

Open in new window


The exact code and placement of the code would depend on the specific table/field names in your database,  what you are trying to delete, and how you want the deletion to be triggered.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
<<  delete all of the records in the table that are not part of the filtered data. >>

Ah - if you are trying to delete ALL of the records that are not in the filter, try this (MAKE A BACKUP BEFORE TESTING THIS, PLEASE!!)

Dim strSQL as string
'  ** Use the inverse of the form's filter for your deletion criteria.
strSQL = "DELETE * FROM SomeTable WHERE  NOT(" & Me.Filter & ")"
CurrentDB.Execute strSQL, dbfailonerror

Open in new window

0
 

Author Closing Comment

by:PBLack
Comment Utility
That is what I wanted. Thanks. Backup not necessary as this table is built from pulling data from other tables on the fly.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now