[AccessD] Need help with Dmax when source is empty - SOLVED

Thank you so much Stuart - that was exactly what I needed!!

Bob 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Tuesday, April 29, 2008 21:57 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Need help with Dmax when source is empty

If there are no existing records, DMax() will return a Null.  Use the NZ()
function to convert the Null to a Zero.

EquipmentSequence = NZ(DMax("EquipmentSequence", "tblEquipment",
"[EquipmentTypeCode] = '" & EquipmentTypeCode & "'"),0) + 1


On 29 Apr 2008 at 21:43, Bob Gajewski wrote:

> Hello Friends
> 
> I am putting together a quick table and form for equipment. The index 
> consists of two fields: EquipmentTypeCode (which is a two-character 
> text
> field) and EquipmentSequence (which is a long integer field).
> 
> I have got the code so that after the user selects the type code, the 
> program will automatically assign the next sequence. This works fine 
> as long as there is already at least one record for that type code. My 
> problem is when there is no record for that type code - how do I check 
> for that, so that I can set the sequence number to 1 ?
> 
> I appreciate any suggestions or guidance.
> 
> Best regards,
> Bob Gajewski
> 
> 
> CODE
> -----------------------------------------------------------
> Private Sub EquipmentTypeCode_AfterUpdate() EquipmentSequence = 
> DMax("EquipmentSequence", "tblEquipment", "[EquipmentTypeCode] = '" & 
> EquipmentTypeCode & "'") + 1 If EquipmentSequence < 1 Then
>     EquipmentSequence = 1
> End If
> EquipmentSerialNumber.SetFocus
> LastUpdated = Date
> End Sub


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