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?

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" <(E-Mail Removed)> wrote in message
news:3ee501c37646$afc9c490$(E-Mail Removed)...
> 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,

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

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

Much appreciated.


>-----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;
>
>
>"frank" <(E-Mail Removed)> wrote in message
>news:3ee501c37646$afc9c490$(E-Mail Removed)...
>> 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?
>>
>
>
>.
>

"frank" <(E-Mail Removed)> wrote in
news:3ee501c37646$afc9c490$(E-Mail Removed):

> 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