Delete record problem

[frank]

I run a delete query as part of an archieve process.
However, I have a problem that occurs when the last order
entered is deleted, in that when a new order is
subsequently raised it uses DMax + 1 on the existing
order no field to calculate the next number. Obviously,
if the last entry has been archived and deleted then the
number sequence will conflict with existing archive
orders. So, is there a way to prevent the record from
being deleted if its the last record in the set? Any
suggestions would be apreciated.
..
Code examples stc?

 

[Andrew Smith]

You could add a criterion to your delete query to specify that the ID field
must be less than the highest ID - use a DMax fuction or a subquery for the
criterion, eg

DELETE ID
FROM tblName
WHERE ID<DMax("ID","tbName");

or

DELETE ID
FROM tblName
WHERE ID<(SELECT DISTINCT Top 1 ID FROM tblName ORDER BY ID DESC;

 

[frank]

Andrew,

many thanks!!! I used the following in the criterior of
the qry and it works fine

<DMax("[OrderNo]","tblSalesOrder")

Much appreciated.

 

[Tim Ferguson]

I run a delete query as part of an archieve process.

The other plan is simply not to delete the records: you can set a flag in a
"IsArchived" field and use the queries to excluded archived records. That
way, you can still summarise and count all records without having to mess
about with UNION queries and what not: there is no chance of getting
duplicates into the archive table; and your numbering system carries on
regardless.

One thought, though. Since you are now complaining about the one thing that
people find disagreeable with autonumbers not having (i.e.
consecutiveness), then why not simply use an autonumber?

All the best



Tim F