Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Movelast problem (A2K / SQL Server 2K)

    Hello All,
    I am using ODBC to connect to a SQL server DB. I am trying to return the ID number of a record I created and place that number in a text box on a form. This all works perfectly using a connection to a similar Access back end. Here is the code I am trying to use:
    rstSO.Update
    rstSO.MoveLast
    intSOID = Nz(rstSO!SO_ID, 0)
    Me.cboWONO = intSOID
    Basically I do an update to the record and then try to do a movelast to get the ID number of the record. It returns a "run time error -2147352567 record is deleted". However, when I look into the table, the record is there.

    Any ideas?

    Thanks,
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Movelast problem (A2K / SQL Server 2K)

    Why are you doing a MoveLast? That won't necessarily give you the correct ID number. It moves to the last record in the *index* , not the last record you entered, although they may sometimes be the same. Is this an unbound form? If not, wouldn't it be easier to simply bind the textbox to that field in the linked table?
    Charlotte

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

    Re: Movelast problem (A2K / SQL Server 2K)

    When we do this kind of thing, we typically create our own unique ID number in lieu of an autonumber (SQL calls it an identity) column, or we have something unique in the record so that we can do a search on the table and find the autonumber value with a query. The problem stems from the fact that in Access, you can get the value of the autonumber at the time you insert the record. However in SQL (at least with the ODBC driver, the value isn't available at time the record is saved, so you have to go look it up after the fact. That can be a pain if you are trying to insert records into multiple tables based on input from a single unbound form. Hope this helps a bit - if you want more info on creating your own ID, repost.
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Movelast problem (A2K / SQL Server 2K)

    Hi Charlotte,
    Thanks! It is bound via select query in the record source setting. I tried removing the bind and it makes no difference. If I bind the text box to the "SO_ID" field it gives only the first record of the table and will not update correctly. Any other ideas?

    Thanks,
    Mark

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Movelast problem (A2K / SQL Server 2K)

    Hi Wendell,
    Thanks for the help. Yeah, I think the problem is with the number not being created until after the update. Now how can I go back and get that number? I do some updates to other recordsets with this code however none of those are required to be updated on the form in question, just this one text box.

    Thanks,
    Mark

Posting Permissions

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