Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show New Autonumber (2002)

    I'm working with an access database that contains tables linked to a SQL Server 2000 database. I have one data entry form linked to one of the linked tables on which the ID field is an identity seed field in SQL Server (the equivalent of the Access autonumber). Right now, the value for the ID field is ony filled when the user closes the form. How can I get it to fill when he opens the form, before adding any other information? Do I have to write code for this or can I get it through setting some sort of property on the form or the ID field?

  2. #2
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show New Autonumber (2002)

    I found a solution! I'm doing a refresh on the form in the after_update event procedure of one of the bound controls. This causes SQL Server to assign the id and shows it on the form.

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

    Re: Show New Autonumber (2002)

    The refresh is actually causing the record to be written to the SQL server. Apparently SQL server works slightly different than Access when assigning an autonumber; Access assigns it as soon as a new record is dirtied. SQL apparently assigns it when the new record is written.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show New Autonumber (2002)

    It certainly seems like that's what's going on. Thanks! [img]/forums/images/smilies/smile.gif[/img]

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Show New Autonumber (2002)

    Mark is correct - SQL Server only assigns the autonumber value when the record is written. That can be an issue if you want to do something from VBA using DAO where you need the autonumber value as a foreign key. It also rears it's head occasionally with subforms.
    Wendell

Posting Permissions

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