Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2005
    Posts
    36

    Question Unanswered: Get last ID value (was "Help")

    I'm tying to write code within VBA that will open a table, move to last record and get ID. this is what I have:

    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Set MyDB = CurrentDb()
    Set rst = MyDB.OpenRecordset("Finished Goods BOM Transactions", dbOpenDynaset)
    If rst.RecordCount <> 0 Then
    rst.MoveLast
    Me!Control = rst!FGID
    rst.Close
    Set MyDB = Nothing
    Set rst = Nothing
    End If

    I keep getting "Object variable or with block variable not set" error. I'm not using the "with" statemen so, I'm lost here.

    thanks in advance
    Carlos

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There isn't a much more inefficient way to get the information you want. Have you looked at the DMAX function?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2005
    Posts
    36
    pootle thanks for your replay.
    Yes, but what this function does "function returns the maximum value in a specified set of records (or domain)" is not what I'm trying to do. Just need to get the last record key data value so I can use it for other purpose.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    'last' has quite limited meaning in a database.
    do you mean 'the most recently inserted'?

    maybe something like this approach to sequential IDs might help - you know the most recently entered ID without all the (slow) recordset stuff.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jun 2005
    Posts
    36
    Yes izy, that is exaclty what I mean. I know I'm not using the most effective way, I can't use the Dlookup function as I do not have a valid criteria to extract the Key value from the last inserted record. Now remember I'm trying to extract it from a different table no the current/active table
    I'm fairly new to access,but I'm learning.

    The attached zip has errors, can't open it

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oh woe!

    i have abandoned Access and just visit here for nostalgia - i dont have Access on this machine so
    a- cant check the zip
    b- cant check my sourcecode and post a new zip.

    gameplan was a one-record table something like:
    seqID, int, 1 (not autonumber - there will only ever be 1 record in this table)
    SeqLast, int (is the last ID consumed)

    and some SQL to get SeqLast into intLastUsed along the lines of:
    SELECT SeqLast FROM daTable WHERE seqID = 1
    ...that gets you an integer that is supposedly the last used sequential ID

    and some more SQL:
    UPDATE daTable
    SET daTable.SeqLast = intLastUsed + 1
    WHERE datTable.SeqLast = intLastUsed
    AND daTable.SeqID = 1

    and then check if the update happened (e.g. RecordsAffected) - if not, intLastUsed is NOT the current SeqLast in the one-record-table (i.e. someone else used a later number since you started the process)
    ...so you retry.

    ...sorry that i can't do more that this hint for now.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jun 2005
    Posts
    36
    thanks Izy, will give a try.

  8. #8
    Join Date
    Jun 2005
    Posts
    36
    Finally got it to work
    Dim strName As String
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    strName = "Finished Goods BOM Transactions"
    Set MyDB = CurrentDb()
    Set rst = MyDB.OpenRecordset(strName)
    If rst.RecordCount <> 0 Then
    Do While Not rst.EOF
    Me!tmpControl = rst![FGID]
    rst.MoveNext
    Loop
    rst.Close
    Set MyDB = Nothing
    Set rst = Nothing
    End If

    For sure this is not the most efective way as I have to got through the entire table sequentially, (not a big table anyway) but it worked just fine for now. If anybody has a more efficient way to do it, please le me know.

    thanks for you input anyway
    Carlos

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    "Last" actually has no meaning in a relational database, but Access is jaw droppingly "irrelational" when it comes to this fundamental aspect of set theory.

    It is unusual that the greatest ID is not the most recently entered too, however DLAST() will work just fine according to your criteria. You merely enter the column name and the table name as criteria, ignore the third parameter.

    Hey Izy - great to see you on the forums again
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2005
    Posts
    36
    It worked!!!, definitely a better way to do it.
    Thanks a lot pootle


    Carlos

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a word of caution you dont' explain why you want the 'last' value
    in a multi user db all you can get is the last that the system knows. there is a possibility that when you come to write a record that may no longer hold true
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jun 2005
    Posts
    36
    True Healdem,
    In my particular case the process of writting a record is done with an update query and not by a user, then the DLast() functions is perfomed. The ID will then be used to update another table based on the value.

    thanks for your input anyway.
    Carlos

Posting Permissions

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