Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2002
    Posts
    63

    Unanswered: return value of last altered record.

    I have created a form that inserts a record into a table witha promary key that is automatically generated. how do i get the value of that records primary key back into my VBA?

    thanks

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    SELECT Max(yourPrimKy) AS [Max Of IDNr]
    FROM yourTble;

  3. #3
    Join Date
    Jun 2002
    Posts
    63
    Thanks

    Now I'm gonna aska really dumb question . the SQL becomes "SELECT Max(bckID) AS lastrec FROM BackupRules;" for me. i've only ever added records from VBA using DoCmd.RunSQL - how do I run the above query and get lastrec into a variable?
    Last edited by mythix; 05-14-04 at 06:58.

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    PHP Code:

    DoCmd
    .RunSQL("SELECT Max(yourPrimKy) AS [MaxOfIDs]
        INTO yourTempTab
      FROM yourOriginalTab;" 
    This will Create the yourTempTab with only one filed called MaxOfIDs and containing one Record.

    Through a RecordSet you will be able to get it in a Variable

  5. #5
    Join Date
    Jun 2002
    Posts
    63
    Thanks, got it to work thisa way:

    Set dbs = CurrentDb()
    strSQL = "SELECT Max(bckID) AS lastrec FROM BackupRules;"
    Set rst = dbs.OpenRecordset(strSQL)
    sqltemp = rst![lastrec]

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    It is even better, because you didn't use a TempTable. But still using a RecordSet of the Query.

    Good

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    How about using Variable=DMax("bckID","BackupRules") ?

    This, and all to other suggestions assume the autonumber is Incremental and not Random !!

    MTB

  8. #8
    Join Date
    Jul 2003
    Posts
    73
    IMO the best way to do this is to use a recordset as follows:

    Code:
    Dim rsAddition as Recordset
    Dim intAddedNumber as Integer
    
    set rsAddition = CurrentDb.TableDefs("table_name").OpenRecordset
    
    rsAddition.AddNew
    ' Now you can set each of your values
    rsAddition!WhateverField = "My Data"
    ' You can also store the newly created Autonumber to a variable
    intAddedNumber = rsAddition!bckID
    rsAddition.Update
    
    rsAddition.Close
    Obviously you need to use your own table / column names.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

Posting Permissions

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