Results 1 to 8 of 8

Thread: Nz Function

  1. #1
    Join Date
    Jan 2009
    Posts
    13

    Unanswered: Nz Function

    Got a form with 2 fields; 1 is an autonumber and the other one is a number field. Recalling, do i need to use the nz function to duplicate the autonumber number into the number field automatically? I tried it my own way but didnt succeeded. Any ideas?

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    First question, of course, is why you would want to duplicate the Autonumber field in a second field, presumably in the same form, since you make no mention of anything else.

    Secondly, Nz() has nothing to do with duplicating anything! It's purpose to to convert a Null value for a field into something else, such as 0 (zero) or "" (a zero-lengthl string) to avoid errors generated by Null values. It can actually be used to substitute any value for a Null value.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jan 2009
    Posts
    13
    I'm confused... what do i need to write in the after update of the autonumber field?

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Nothing! An autonumber is filled in automatically by Access and as such, will never fire the AfterUpdate event. You can use something like this
    Code:
    Private Sub Form_Current()
    If Me.NewRecord Then
     Me.AutoNumberClone = Me.YourAutoNumber
    End If
    End Sub
    But you still haven't said why you want to do something as strange as duplicating a field.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Jan 2009
    Posts
    13
    Quote Originally Posted by Missinglinq
    Nothing! An autonumber is filled in automatically by Access and as such, will never fire the AfterUpdate event. You can use something like this
    Code:
    Private Sub Form_Current()
    If Me.NewRecord Then
     Me.AutoNumberClone = Me.YourAutoNumber
    End If
    End Sub
    But you still haven't said why you want to do something as strange as duplicating a field.
    nope, this code aint working...

    i had to make a field where the user manually re-types the autonumber given by access. This is becuase I made a code which on double click on Order ID (the autonumber field name), access opens another form that lists all the current open standing orders. the fact is that when i did this code on the autonumber field, a problem was causing the list not to load up. It took me very long to figure out that double clicking on a autonumber field was the main problem (very strange though). so the solution was simple; i had to make another field to be filled by the user with the autonumber that access labels the record.

    inow, i need the system to fill the number field automatically without the need to be re-typed by the user.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    whoa
    are you thinking of using an autonumber column to have meaning outside the database.. if so baaaaad idea, you cannot EVER rely on an autonumber field if you must have contiguous numbers. if you must have that then you will have to roll your own number generator
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2009
    Posts
    13
    Quote Originally Posted by healdem
    whoa
    are you thinking of using an autonumber column to have meaning outside the database.. if so baaaaad idea, you cannot EVER rely on an autonumber field if you must have contiguous numbers. if you must have that then you will have to roll your own number generator
    thats why i made the field to be filled by the user

    any ideas guys?

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As healdem said, Autonumbers are really not for human consumption, but the code I gave you is quite valid, assuming you changed the names of YourAutoNumber and AutoNumberClone to the actual names of your controls.

    Of course, there's absolutely no reason that double-clicking on an autonumber field shouldn't work, either. I suspect that the problem lies elsewhere in your code for loading the your list.

    When you double-click on the Order ID is the list brought up in the second based on the Order Id (auto) number? If so, the field in the related records on the second form that matches the autonumber has to be defined as a Number datatype with it further defined as Long Integer. It cannot be defined on the related records as an Autonumber.

    What version of Access are you using? Do you have any code in the form's code module that works?

    You have placed the code given in the form's code module, haven't you? It won't work simply placed in the Property box in the properties sheet.
    Last edited by Missinglinq; 07-28-09 at 19:08.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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