[AccessD] Increment Numbers

Add a table of locker numbers with records in them.

LockerNumber  number, integer
IsUsed        Yes/no

Get the first one not used.

SELECT TOP 1 * FROM tlkpLockerNumbers
WHERE IsUsed = 0

When you use it, change the bit column to true.

This will allow for reuse of them also.



At 12:00 PM 11/5/2009, you wrote:
>Date: Thu, 5 Nov 2009 08:13:03 -0600
>From: "Hollis, Virginia" <hollisvj at pgdp.usec.com>
>Subject: [AccessD] Increment Numbers
>To: <accessD at databaseadvisors.com>
>Message-ID:
> 
><703BDA18A87DFA4CB265A86F42E4178D091A8C80 at c2k3exchange.pgdp.corp.usec.com>
>
>Content-Type: text/plain;       charset="us-ascii"
>
>I want to increment locker numbers. I have LockerNumber which is a
>number field with a default value of 0 & format of 000. On the form I
>want new records to automatically increment the locker number. Using
>this code, I only get 000 for new records. I don't want to use the
>autonumber because there may be a case when they need to manually enter
>a number. Which will lead to another issue of duplicating numbers (I
>will ask about that in my next post).
>
>
>
>On  form:
>
>Private Sub Form_BeforeInsert(Cancel As Integer)
>
>'increment next locker number
>
>     If Me!LockerNumber = 0 Then
>
>       Me!LockerNumber = Nz(DMax("LockerNumber", "qry_MaxLocker")) + 1
>
>     End If
>
>End Sub
>
>
>
>qry_MaxLocker:
>
>SELECT Max(tbl_Locker.LockerNumber) AS MaxLocker
>
>FROM tbl_Locker;
>