Delete Dups Query Failing

[esn]

I have a form set up for data entry with a subform linking child
records in a different table. In the case where a user closes the
form halfway through entering child records then reopens it to finish
adding the child records, I don't want to keep the duplicate master
record they create when they reenter the information in the controls
bound to the master table. So I made a simple find duplicates query,
added a "MaxofID" field, and joined it to a delete query that should
delete the second instance of the duplicate record. I was going to
run this query whenever the form is closed. Unfortunately, I get an
error message saying Access is unable to delete the records from the
specified table. Any ideas? The help topic that pops up seems way
off, since it only discusses the possibility that the database is
opened as read only and I can manually delete the records from the
table with no problems. Here are the queries:

Find Duplicates Query:
SELECT First([UPFU Mammal Visit Data].Site) AS [Site Field], First
([UPFU Mammal Visit Data].Day) AS [Day Field], First([UPFU Mammal
Visit Data].Visit) AS [Visit Field], First([UPFU Mammal Visit
Data].Date) AS [Date Field], First([UPFU Mammal Visit
Data].Start_time) AS [Start_time Field], First([UPFU Mammal Visit
Data].End_tIme) AS [End_tIme Field], Count([UPFU Mammal Visit
Data].Site) AS NumberOfDups, Max([UPFU Mammal Visit Data].ID) AS
MaxOfID
FROM [UPFU Mammal Visit Data]
GROUP BY [UPFU Mammal Visit Data].Site, [UPFU Mammal Visit Data].Day,
[UPFU Mammal Visit Data].Visit, [UPFU Mammal Visit Data].Date, [UPFU
Mammal Visit Data].Start_time, [UPFU Mammal Visit Data].End_tIme
HAVING (((Count([UPFU Mammal Visit Data].Site))>1) AND ((Count([UPFU
Mammal Visit Data].End_tIme))>1));

Delete Query:
DELETE [UPFU Mammal Visit Data].*, [UPFU Mammal Visit Data].ID
FROM [Find duplicates for UPFU Mammal Visit Data] INNER JOIN [UPFU
Mammal Visit Data] ON ([Find duplicates for UPFU Mammal Visit Data].
[Date Field] = [UPFU Mammal Visit Data].Date) AND ([Find duplicates
for UPFU Mammal Visit Data].[End_tIme Field] = [UPFU Mammal Visit
Data].End_tIme) AND ([Find duplicates for UPFU Mammal Visit Data].
[Start_time Field] = [UPFU Mammal Visit Data].Start_time) AND ([Find
duplicates for UPFU Mammal Visit Data].[Visit Field] = [UPFU Mammal
Visit Data].Visit) AND ([Find duplicates for UPFU Mammal Visit Data].
[Day Field] = [UPFU Mammal Visit Data].Day) AND ([Find duplicates for
UPFU Mammal Visit Data].[Site Field] = [UPFU Mammal Visit Data].Site)
WHERE ((([UPFU Mammal Visit Data].ID)=[Find duplicates for UPFU Mammal
Visit Data]![MaxOfID]));

Thanks in advance for any insight!

 

[esn]

Thanks Ken, but I don't think that will work - the main form's "data
entry" property is set to yes, so users can't navigate to a previously
enetered record. I tried leaving the data entry property set to yes
for the subform and switching the main form to allow additions but not
edits or deletions, but this caused the subform not to display at
all. I'd prefer to keep it set up for data entry so that users don't
have to navigate to a new record to start entering data - they can
just start typing as soon as the form is opened.

Another thing I should have noted is that referential integrity isn't
enforced for the relationship between the master and child tables, so
that shouldn't be causing the error.

 

[John Spencer]

Your delete query won't work as it is structured. You need to only have one
table specified in the FROM clause. Construct a WHERE clause that identifies
which record you want to delete. Without trying to simplify, you should be
able to use the following.

Delete Query:
DELETE [UPFU Mammal Visit Data].*
FROM [UPFU Mammal Visit Data]
WHERE [UPFU Mammal Visit Data].ID IN
(SELECT [UPFU Mammal Visit Data].ID
FROM [Find duplicates for UPFU Mammal Visit Data]
INNER JOIN [UPFU Mammal Visit Data]
ON [Find duplicates for UPFU Mammal Visit Data].[Date Field]
= [UPFU Mammal Visit Data].Date
AND [Find duplicates for UPFU Mammal Visit Data].[End_tIme Field]
= [UPFU Mammal Visit Data].End_tIme
AND [Find duplicates for UPFU Mammal Visit Data].[Start_time Field]
= [UPFU Mammal Visit Data].Start_time
AND [Find duplicates for UPFU Mammal Visit Data].[Visit Field]
= [UPFU Mammal Visit Data].Visit
AND [Find duplicates for UPFU Mammal Visit Data].[Day Field]
= [UPFU Mammal Visit Data].Day
AND [Find duplicates for UPFU Mammal Visit Data].[Site Field]
= [UPFU Mammal Visit Data].Site
WHERE [UPFU Mammal Visit Data].ID)=
[Find duplicates for UPFU Mammal Visit Data]![MaxOfID])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[esn]

Thanks John - worked like a charm.

Ken - I agree with everything you've said and I plan on restructuring
as soon as I have all the data in the same place. Unfortunately right
now it exists in several different files of various formats on several
different computers in several states. I was just trying to throw
something together for the last bit of data entry that needs to be
done. When I do get my hands on everything I can eliminate all those
duplicate fields and set up a relationship based on a foreign key with
referential integrity, but before I get to that point there are a few
hoops that require jumping through.