Results 1 to 10 of 10
  1. #1
    mercapto is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Spain
    Posts
    91

    Better way to get Id when you add new record by code

    I wonder what is the better way to get the Id of a new record that you have added by code to use it next.



    Is it better to do it as a function and return the Id? Or should I do it as a sub and get it from another function that gets last record from the table?
    Is it better to use rs.addnew instead of SQL (to get it from the opened recordset)?

    What do you thing?

    Thanks in advance.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if

    a) you create a db object
    b) use db.execute to insert the new record

    you can use a query to get the primary key

    e.g.

    Code:
    dim db as dao.database
    dim rst as dao.recordset
    
    set db=currentdb
    db.execute "my insert query"
    set rst=db.openrecordset ("SELECT @@Identity")
    msgbox "latest PK is " & rst.fields(0)
    rst.close
    set rst=nothing
    set db=nothing
    Last edited by CJ_London; 04-11-2018 at 12:33 PM.

  3. #3
    mercapto is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Spain
    Posts
    91
    Thanks, Ajax. Did this:

    Code:
    Function CreaRegistroLibro(ByVal IdPiscina As Integer, ByVal strReferencia As String, ByVal strFecha As String, ByVal intMeses As Integer) As Integer
    'Creates new record and returns its id
    
    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    
    
    strFecha = Format(strFecha, "mm/dd/yyyy")
    
    
    strSQL = "INSERT INTO tblLibrosPiscina ( IdPiscina, RefPeticion, FechaPeticion, MesesLibro ) SELECT " & IdPiscina & " AS IdPiscina, '" & strReferencia & "' AS Referencia, #" & strFecha & "# AS FechaPeticion, " & intMeses & " AS MesesLibro"
    
    
    DBEngine(0)(0).Execute strSQL, dbFailOnError
    
    
    strSQL = "SELECT Last(tblLibrosPiscina.IdLibroPiscina) AS UltimoDeIdLibroPiscina FROM tblLibrosPiscina"
    
    
    CreaRegistroLibro = rs!UltimoDeIdLibroPiscina
    
    
    Set rs = Nothing
    Set db = Nothing
    End Function
    Fine?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Fine?
    I've not tried it that way but I doubt it - and you are not executing the second sql string anyway.

    There are reasons for using the db alias - my code doesn't work using currentdb for example (all documented if you want to google/bing), so suspect it won't work for DBEngine(0)(0). either

    And Last is not reliable, Last does not mean last entered, it just means the last in the recordset. At least order it by IdLibroPiscina to make sure it is. But it still won't be reliable in a multi user environment if two users insert a record at the same time

  5. #5
    mercapto is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Spain
    Posts
    91
    Code:
    I've not tried it that way but I doubt it - and you are not executing the second sql string anyway.
    
    There are reasons for using the db alias - my code doesn't work using currentdb for example (all documented if you want to google/bing), so suspect it won't work for DBEngine(0)(0). either
    It's true I posted fast and forgot a couple of lines. I'm not concerned about that though... I could make it work.

    Code:
    And Last is not reliable, Last does not mean last entered, it just means the last in the recordset. At least order it by IdLibroPiscina to make sure it is. But it still won't be reliable in a multi user environment if two users insert a record at the same time
    That's the point: what is the better way to get the inserted PK. "Last" is not reliable, "max" could have the same problem... Perhaps is better to use rs.addnew instead of SQL.
    This way I only execute 1 sql sentence.

    Don't you think so?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You asked how it should be done, I've provided the answer.

    Not sure I can help anymore since you are ignoring the answer, and seem determined to do it a different way

    good luck with your project

  7. #7
    mercapto is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Spain
    Posts
    91
    Quote Originally Posted by Ajax View Post
    You asked how it should be done, I've provided the answer.

    Not sure I can help anymore since you are ignoring the answer, and seem determined to do it a different way

    good luck with your project
    First of all, thank you very much for trying to help me... but you never answered my question.

    You told me to use a query to get the PK but you never said what sort of query and that is important.

    I suggested "last" and you said "no", I also suggested "max"... what query would you use?

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You told me to use a query to get the PK but you never said what sort of query and that is important
    "... what query would you use?
    it is here

    set rst=db.openrecordset ("SELECT @@Identity")

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If using a recordset I use this method:

    http://allenbrowne.com/ser-57.html

    specifically the bookmark bit.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    mercapto is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Spain
    Posts
    91
    it is here
    Sorry.... I thought it was just sample code, just googled for it... Never heard about that instance before and -in what I read- I didn't understand how to use it . Need to study it....

    Thx

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-29-2017, 11:22 AM
  2. Record not edited by using the code
    By sukhjinder in forum Programming
    Replies: 2
    Last Post: 03-06-2017, 08:10 PM
  3. Replies: 2
    Last Post: 06-28-2013, 12:58 PM
  4. Replies: 5
    Last Post: 06-16-2013, 05:25 PM
  5. Add record using VBA code
    By nick.h in forum Programming
    Replies: 9
    Last Post: 12-20-2011, 01:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums