How to write code to delete record based on field value?

[VAer]

Dim dbs As Database

Set dbs = CurrentDb

'Step 1: Identify those records from TableGroup, and copy them to TableGroupDeleted, those two tables have similar data structure, except last field (add the current date)
'Step 2: Delete any records from TableGroup which do not have administrator in the group.

dbs.Execute "DELETE * FROM TableGroup WHERE there is not administrator in the group;"


Me.Refresh
dbs.Close

I have two tables: TableGroup & TableGroupDeleted

TableGroup: A group can have one or multiple administrators, or NO administrator.

Take attached two screenshots for example, in first screenshot TableGroup, I would like to delete any groups which do not have administrator. For this screenshot, I will need to need Group3, and there are two records associated with Group3; therefore, the last two records will need to be deleted. But before being deleted, those two records will need to be copied to TableGroupDeleted, which has similar data structure except last field (Copy 5 fields and add current date for last field DateDeleted).

How can I write the code?

Thanks.



------------------------------------------------------------------------------------------------------------------------------------------

[Gicu]

You would need to create one append query to insert your records to be deleted (with criteria =False in the Administrator field) and one extra field DateDeletedate() (if you want to track the time part too use Now()), then another query to actually delete the records (DELETE * FROM TableGroup WHERE Administrator=False;")

If you need to do it in VBA you can use Docmd.OpenQuery or CurrenctDb.Execute (later prefered by most as you do not have to suppress the warnings with Docmd.Setwarnings False as you do for the first option).
Cheers,
Vlad

[VAer]

(with criteria =False in the Administrator field)
(DELETE * FROM TableGroup WHERE Administrator=False;")

Thank you, but DELETE * FROM TableGroup WHERE Administrator=False is not accurate statement. I said "delete any groups which do not have administrator", not "delete any records which are not administrator". I do not want to delete third record (Group2 Person1), since Group2 has two administrators.

I don't really want to append all records, I only want to append those records which are going to be deleted. In this example, I only want to append last two records.

When you say "append query", does it mean INSERT INTO TableGroupDeleted SELECT GroupName, UserID, FirstName, LastName, GroupCreatedBy FROM TableGroup WHERE condition ?

At the meanwhile, how to add Now() to above Insert statement? Or I need another state UPDATE TableGroupDeleted SET DateDeleted = Now() Where DateDeleted IS Null ?

[Welshgasman]

I would not try to be clever here.
Create a query that identifies the groups that do not have an administrator.
The use that as the subquery to insert and delete.

[accesstos]

How can I write the code?

At first,

Create a query to get the "empty Groups" :

SELECT GroupName AS EmptyGroup
FROM TableGroup
GROUP BY GroupName
HAVING Sum([Administrator])=0;

Create the append query:
(replace the red [...] with the rest field(s))

INSERT INTO TableGroupDeleted ( GroupName, UserID, [...] )
SELECT GroupName, UserID, [...] 
FROM TableGroup
WHERE (((GroupName) In (SELECT EmptyGroup FROM qryEmptyGroups)));

Create the delete query:

DELETE *
FROM TableGroup
WHERE (((GroupName) In (SELECT EmptyGroup FROM qryEmptyGroups)));

Then, you can write code like that:

    Dim dbs  As DAO.Database
    
    Set dbs = CurrentDb
    
    dbs.Execute "AppendQuery", dbFailOnError
    If dbs.RecordsAffected Then
        dbs.Execute "DeleteQuery", dbFailOnError
        Debug.Print "Deleted " & dbs.RecordsAffected & " records"
    End If
    
    '[...]
    Set dbs = Nothing

Take care for error handling.

I hope helps.

Cheers,
John

[VAer]

Create the append query:
(replace the red [...] with the rest field(s))

INSERT INTO TableGroupDeleted ( GroupName, UserID, [...] )
SELECT GroupName, UserID, [...] 
FROM TableGroup
WHERE (((GroupName) In (SELECT EmptyGroup FROM qryEmptyGroups)));

dbs.Execute "AppendQuery", dbFailOnError
If dbs.RecordsAffected Then
dbs.Execute "DeleteQuery", dbFailOnError
Debug.Print "Deleted " & dbs.RecordsAffected & " records"
End If

Thank you very very much!

Two questions:
1) How to get Now() for field DateDeleted in INSERT statement? Data structure of two tables are not exactly the same.
2) Is AppendQuery syntax? Or I need to create AppendQuery first?

[accesstos]

Thank you very very much!

Two questions:
1) How to get Now() for field DateDeleted in INSERT statement? Data structure of two tables are not exactly the same.
2) Is AppendQuery syntax? Or I need to create AppendQuery first?

You are very welcome!

1. Set it directly as Default Value of the field in Field Properties in table design, so, you don't have to worry for that field in your append queries.



2. AppendQuery and DeleteQuery are dummy names for the relevant queries in my first post. You have to replace them with the actual names of the query definitions that you will create and save them in your database.

Cheers,
John

[VAer]

1. Set it directly as Default Value of the field in Field Properties in table design.

Thank you very much again. Does default value Now() affect previously deleted record?

For example, I delete Group3 today and DateDeleted is 12/29/2020, the date for these two records can not be changed in the future. In the future, I may run the program again and delete other groups, insert new records into TableGroupDeleted, but previously deleted two record should keep original deletion date 12/29/2020.

[accesstos]

Thank you very much again. Does default value Now() affect previously deleted record?

No, does not. Default value affect only the new record(s).

You are very welcome again!

[VAer]

Still don't quite get it. I don't have much knowledge about Access VBA syntax, I am only familiar with Excel VBA.

What is the relationship between qryEmptyGroups and first SELECT statement? Do I need to store SELECT statement into qryEmptyGroups ? Something like below? I am not sure what it exactly means to create a query? Should I create it in Access Query Wizard? How to turn the SELECT statement into qryEmptyGroups?

Thanks.

Dim qryEmptyGroups As String

qryEmptyGroups = "SELECT GroupName AS EmptyGroup " &
"FROM TableGroup " &
"GROUP BY GroupName " &
"HAVING Sum([Administrator])=0;"

dbs.Execute qryEmptyGroups

[accesstos]

The qryEmptyGroups returns the groups that doesn't have an admin. That query is used within the other queries to restrict the records to append/delete (using IN() clause).

Check the sample db in attachment.

[VAer]

The qryEmptyGroups returns the groups that doesn't have an admin. That query is used within the other queries to restrict the records to append/delete (using IN() clause).

Check the sample db in attachment.

Thank you VERY VERY much. I will take a look at it when I have chance.

By the way, where can I write code for Access query? I clicked Create > Query Wizard/Query Design, and did not find where to write code.

[accesstos]

In this pane (picture) you write SQL code (in query design, right click>>SQL).

You write VBA code in modules (see Module1).
Add some empty groups in TableGroups and run the sub DeleteGroups() of Module1.

You are VERY VERY welcome!
John

[VAer]

Thanks a lot!

[VAer]

Why we need so many parentheses for HAVING statement(in your attached Access file)?

Thanks.

SELECT TableGroup.GroupName AS EmptyGroupFROM TableGroup
GROUP BY TableGroup.GroupName
HAVING (((Sum(([Administrator])))=0));

Does below code missing dbs.Execute "qryEmptyGroups" for the first line?

Sub DeleteGroups()
    Dim dbs As DAO.Database


    Set dbs = CurrentDb
    'Copy empty groups
    dbs.Execute "AppendQuery", dbFailOnError
    If dbs.RecordsAffected Then
        'Delete empty groups
        dbs.Execute "DeleteQuery"
        Debug.Print "Deleted " & dbs.RecordsAffected & " records"
    End If
    Set dbs = Nothing
End Sub