Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Value (Access 2002)

    Hi,

    I have the following code in the OnEnter event of a field in my form:

    Dim intRelNo As Integer

    intRelNo = DMax("[RelRev]", "Table1", "[JobNo] = '" & Forms!SubFormName!JobNo & "'") + 1

    Me!RelRev = intRelNo


    It works just fine. However, I would like to know of a way to set the Value to Zero if the JobNo is a new number in the JobNo field.

    How do I need to modify the code to accept "0" if the JobNo is new or Run if the JobNo is already a number in the JobNo field?

    Thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Default Value (Access 2002)

    >>It works just fine. However, I would like to know of a way to set the Value to Zero if the JobNo is a new number in the JobNo field.

    How do I need to modify the code to accept "0" if the JobNo is new or Run if the JobNo is already a number in the JobNo field?<<


    You need to use the NZ function (Null-to-zero). Depending on what you want, you can change your formula to either of these (I've abbreviated):

    intRelNo = NZ( DMax(....yada, yada...),0) +1
    intRelNo = NZ( DMax(....yada, yada...) + 1), 0)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Value (Access 2002)

    Thanks for the quick response. However, I must have made a mistake in my post.

    If a JobNo is entered in a new record that ISN'T in the JobNo field then I want the RevRel to be zero(0), if the JobNo entered in the new record IS in the list then I want the DMax+1 expression to run .... i.e. 1st record for 1st JobNo entered is 0, the next time that JobNo is entered it would be 1 and so on.

    I put the code in the AfterUpdate Event of JobNo because it didn't work in the OnEnter Event either....should I put it somewhere else?

    Again, Thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Default Value (Access 2002)

    >>If a JobNo is entered in a new record that ISN'T in the JobNo field then I want the RevRel to be zero(0), if the JobNo entered in the new record IS in the list then I want the DMax+1 expression to run .... i.e. 1st record for 1st JobNo entered is 0, the next time that JobNo is entered it would be 1 and so on.<<

    Okay, so then use the format:
    Nz( Dmax(......)+1, 0)
    This way, if there is no JobNo, then DMax returns Null, and Null + 1 = Null, so the NZ function will return the 0.

    -----------------------------------------------------
    >>I put the code in the AfterUpdate Event of JobNo because it didn't work in the OnEnter Event either....should I put it somewhere else?<<

    Any time I'm going to create a number like that, I usually try to put it in the Form's BeforeUpdate event. This may not apply to this situation, but I want to minimize the chance someone else could try to use same number.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •