[AccessD] Increment Numbers

Virginia,
 
The reason you always get a zero returned is that DMax("LockerNumber", "qry_MaxLocker") is looking for the field named "LockerNumber", but your query has given the field the alias "MaxLocker". So change the code to...

 Me!LockerNumber = Nz(DlookUp("MaxLocker", "qry_MaxLocker"),0) + 1

You don't need to use Dmax as the query only returns one record anyway. (though Dmax will still work)

Note also that I have changed the Nz() function call slightly. 

	Nz(Null) returns an empty string, whereas Nz(Null,0) returns the value zero.

HTH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis, Virginia
Sent: Thursday, November 05, 2009 9:13 AM
To: accessD at databaseadvisors.com
Subject: [AccessD] Increment Numbers

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;

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com