Checking an ID right after an update

wired_g
wired_g used Ask the Experts™
on
I want to check the ID of an autonumber assigned by MS Access.  The following is my current vb code:

 With rsCustomerNote
        .AddNew
        !CustomerID = frmCustomer.List1.Text
        !LastName = frmCustomer.txtCustInfoLast.Text
        !FirstName = frmCustomer.txtCustInfoFirst.Text
        !employeeID = employeeID
        !Date = Date
        !Note = frmCustomer.txtCustInfoNote.Text
        .Update
 End With

The only field no included in this update is the NoteID, which distinguishes the different notes available.  If 2 notes are exactly identical except for the NoteID, how do I determine what NoteID the note that was added here has?

Thanks

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
A rough way is to check which note id is larger, but this isn't 100% (Select Max noteID from table where {your conditions here}.  Someone can reset the autonumber field, but it requires access to the DB itself.
wait a minute, is the noteid field in the rsCustomerNote recordset?  If so, it should be in your recordset after the addnew.  If Noteid field is not in the recordset, then you will have to query like I said.  If it is in the recordset and you don't get the number back, refresh the RS.

With rsCustomerNote
       .AddNew
       !CustomerID = frmCustomer.List1.Text
       !LastName = frmCustomer.txtCustInfoLast.Text
       !FirstName = frmCustomer.txtCustInfoFirst.Text
       !employeeID = employeeID
       !Date = Date
       !Note = frmCustomer.txtCustInfoNote.Text
       .Update
       debug.print !NoteID
End With

Commented:
I recently asked this Q elsewhere and didn't really get a great answer, but one that was acceptable for me was to immediately query the database for Max(IDField).  As long as nobody else has updated the table in the short period between the update and the query, you'll get the right ID (also assuming that you're using a sequential numbering for your auto-number field.)

SELECT Max(IDField) FROM tblWhatever

This can also have performance problems if you have millions of records in the DB.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Actually, I guess in your case it's

SELECT Max(NoteID) from tblNotes

hmm..in my case I'm using SQL, but maybe in your case it's as simple as twalgrave indicated.

Author

Commented:
Hmmm, I don't like those alternatives.  So there is no way to figure out the ID of the recently added NoteID?

Author

Commented:
Reason I need this is cause I auto update a listview with the data.  But the chances of having 2 notes is really high as an employee can add multiple notes for the client on the same day (and there is nothing in the current database to distinguish notes except for NoteID).  So not knowing the noteID for the record added, I can't query the database using any other field.
How can you NOT like my solution?  I get the ID immediately after you update the database without going back to the DB for the info?  It's a single line of code change.  Well, perhaps you didn't read it.  Here it is again (by the way, I tested it and it works)

With rsCustomerNote
      .AddNew
      !CustomerID = frmCustomer.List1.Text
      !LastName = frmCustomer.txtCustInfoLast.Text
      !FirstName = frmCustomer.txtCustInfoFirst.Text
      !employeeID = employeeID
      !Date = Date
      !Note = frmCustomer.txtCustInfoNote.Text
      .Update
'*****************HERE'S THE NEW CODE*************
'****After the update, you can immediately get the NoteID
'****As long as it is in the original recordset.
      debug.print !NoteID
End With
 
try this:

With rsCustomerNote
     .AddNew
     !CustomerID = frmCustomer.List1.Text
     !LastName = frmCustomer.txtCustInfoLast.Text
     !FirstName = frmCustomer.txtCustInfoFirst.Text
     !employeeID = employeeID
     !Date = Date
     !Note = frmCustomer.txtCustInfoNote.Text
     .Update
 '++++++ ADD THIS LINE
     .MOVELAST
     debug.Print !NoteID
End With

Arthur Wood
Arthur Wood,

You don't need the movelast indicated in your repost code.  I tested this with an access connection of my own and the value is returned without the need for the data refresh that a movelast would perform.

Commented:
>How can you NOT like [twalgrave's] solution?
I agree.  It came in while I was submitting my comment so I didn't see it initially, but it certainly seems to give you everything you want and more!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial