Re: finds gaps in sequential numbers?

Dear Pip:

Open the query as a recordset, read the record, and reference the value:

Public Function testme() As String
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT MAX(invoiceNo) AS MaxInvNo
FROM YourTable")
rst.MoveFirst
Debug.Print rst!MaxInvNo
End Function

Replace YourTable with the actual name of your table. You don't need the
Debug.Print line, it just shows that the value you need is now available as
rst!MaxInvNo.

Tom Ellison


"Pip''''n" <Pipn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BE6107A8-9A0B-47D1-81A2-5EC2FFBF62FC@xxxxxxxxxxxxxxxx
Sorry, I asked the wrong question last time.

I know how to create the query to show the max(invoiceNo). I also know how
to run the query using the DoCmd.OpenQuery. But how do I grab the single
value in order to assign the value to the table. If i'm not to use the
DoCmd.OpenQuery what do I use?

I understand as you mentioned that you can't use an update query in the
BeforeUpdate event because you havn't updated the table with the new
record.

Pip''n

"Tom Ellison" wrote:

Dear Pip:

Use a query that returns the value, as we have discussed. Run this query
from code in the BeforeUpdate event. Grab the returned value (one row,
one
column).

Tom Ellison


"Pip''''n" <Pipn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2D6675FB-1CBF-485E-8A55-F393D9D361DB@xxxxxxxxxxxxxxxx
I understand using the BeforeUpdate event, how do I determine the
invoice
number to use?

Pip''n

"Tom Ellison" wrote:

Dear Pip:

I don't understand using an update query to assign the InvoiceNo. I
suggest
setting this in the BeforeUpdate event when it is a NewRecord, as I
said
before.

Testing NewRecord in the code of a form is Me.NewRecord.

Tom Ellison


"Pip''''n" <Pipn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9C81B54A-A69A-46C5-8CB5-6111364EB10A@xxxxxxxxxxxxxxxx
Tom,

you mentioned assigning the Max(Invoice) to the record using a
query.
When
I
try it i get the error that the field InvoiceNo is not being used as
part
of
an aggregate function.

Here's the code I tried:
UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1
WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#]));


Also, how do you test for NewRecord? I've been checking to see if
the
key
field is Null.

"Tom Ellison" wrote:

Dear Pip:

There are usually other ways a record can be caused to be saved
other
than
your "save" button. You need an event that covers all cases. I'm
accustomed to using the BeforeUpdate event and testing for
NewRecord.

You could use the Max(InvoiceNo), obtained from a query, to assign
this,
or
you could have a control table with a row for Next Invoice Number
from
which
you get this. In multi-user situations it is usually recommended
to
use
locking in either case while you do this. In extensive tests I've
run, I
could never get it to foul up, but it's theoretically possible.

In a singlue user system, you have no worries with locking.
Unless,
of
course, it get's installed later multi-user.

Tom Ellison


"Pip''''n" <Pipn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F02F4998-71D8-457A-B382-BFC976C5ED47@xxxxxxxxxxxxxxxx
Dear Tom:

No, Work orders are never deleted. They are recorded manually
from
printouts
so the work order number is manually entered. I just need a
listing
of
the
missing work orders to enter any that may have been missed
through
the
manual
entry system.

This system will only ever have one user for each location.

*******************************
This second part is for a different program.

Is there an easy way to test for a new record?

These records aren't deleted after they are completed. However
there
are
times that the invoice is started and then canceled for some
reason
to
be
entered later. Once the form is finished it can not be deleted.

I'll probably use the BeforeUpdate event like you mentioned.
However,
how
do
I assign in code the maximum(InvoiceNo) to the field. Just use an
update
query called from the event? I could also call it from the 'save'
button
or
is that not suggested?

Cheers and thanks,

Pip''n

"Tom Ellison" wrote:

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to
assign
the
number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order
numbers
until
the BeforeUpdate event. Test for NewRecord, and if true, set
the
Work
Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created
by
deletions.
Perhaps you would consider not deleting such work orders, but
just
marking
them. Depends on may factors of the design.

Tom Ellison


"Pip''''n" <Pipn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4D0594B5-C8D7-4CDB-90B4-64954ECE398F@xxxxxxxxxxxxxxxx
Dear Tom:

Currently I have a listing of [Work Order]s in a table called
[Work
Orders]
i know the naming isn't the best but it was before I learned
what
I
know
now.
This listing is used to create a bill each month. To prevent
the
possibility
of missing work orders I need to list all missing work orders
before
I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by
storing
the
min
work order for the location. and Run a count+1 on the work
order
numbers.
Opening a form to the desired record each time. If the record
can't
be
found
the number is concatenated to a string that is returned once
the
maximum
work
order is reached. Each time the code is run, the first missing
work
order - 1
is stored as the minimum work order for that location.

The values stored are:
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work
orders
(values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to
make
it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a
record
upon
completion of a sale. Currently I assign the maximum number +
1
but
i
know
this could create problems if a number is assigned when
opening
this
form,
a
second is opened and the first is cancelled. I would end up
missing
an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

"Tom Ellison" wrote:

Dear Pip:

Showing all the missing numbers would be quite a different
problem.
You
would need a table of numbers, perhaps thousands. A LEFT
JOIN
or
NOT
EXISTS
can be made made against this list, limited by the largest
value,
if
that's
what you desire.

Given full details, I could work out a solution on this
basis,
too.

Tom Ellison


"Pip''''n" <Pipn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4B40ACAA-F79D-447D-998D-62C89036AD3D@xxxxxxxxxxxxxxxx
I too am looking for a way to determine missing numbers in a
sequence.

Your solution is a wonderful way to determine the ranges
missing.
Is
there
a
way to list each missing number through this solution?

Pip''n


"Tom Ellison" wrote:

Dear Jennifer:

Step 1: find a Number where the next number is missing,
but
not
the
greatest number in the table:

SELECT Number
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)

Here, a subquery in the EXISTS clause looks for Number +
1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query
to
look
at
the
whole table using T1 without referenced to the "outer"
query
based
on
an
instance of the table called T. The WHERE clause relates
the
two
copies
of
the table. It says, "look for the next sequential value
in
Number,
relative
to the value in the outer query which is considering each
value
of
Number
that exists."

Another subquery:


.