Delete Qry 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. Is there a way to prevent the record from being
deleted if its the last record in the set? Any
suggestions would be apreciated.

 

[Joan Wild]

One idea is to store the order number in a separate table.

 

[frank]

Unable to get this to work without having to change a lot
of the existing tbl scructures and relationships.
Should I post in the programming section?

Thanks

 

[Frank]

I posted this problem into Modules, DAO & VBA and Andrew
give the following suggestion

-----Original Message-----
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;

I responded with:

Andrew,

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

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

Much appreciated.