?
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
Medium Priority
?
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 40372651
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 2000 total points
ID: 40372669
<<  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
ID: 40372699
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

770 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