Get autonumber value for last modified record?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is probably simpler than I'm trying to make it:

A "Save" button on my form adds a new record to the table called
"tblCommunicationsSent". I would now like to have that same "Save" button
UPDATE the record the user just added, instead of ADDING another record to
the table.

I think if I could get the "ID" value (which is an autonumber field in the
table) for the last modified record I would be on the right track, but I
can't figure out how to get it. Any suggestions? Thank you in advance for
any help!

Here is my code for adding the record:
Dim db As Database
Dim rstCommunications As Recordset
Set db = CurrentDb()
Set rstCommunications = db.openrecordset("tblCommunicationsSent",
dbopendynaset)

With rstCommunications
.AddNew
!CaseID = txtCaseID
!CommunicationType = "Letter"
!CommunicationName = txtLetterName
!Communication = txtLetterText
!CommunicationDate = Date
!CommunicationSentBy = Forms!fmnuMainMenu!txtAgentFirstName
.Update
End With
rstCommunications.Close
 
You can move the recordset to the last modified record and then store the ID
value (see MyIDValue line):

Dim db As Database
Dim rstCommunications As Recordset
Set db = CurrentDb()
Set rstCommunications = db.openrecordset("tblCommunicationsSent",
dbopendynaset)

With rstCommunications
.AddNew
!CaseID = txtCaseID
!CommunicationType = "Letter"
!CommunicationName = txtLetterName
!Communication = txtLetterText
!CommunicationDate = Date
!CommunicationSentBy = Forms!fmnuMainMenu!txtAgentFirstName
.Update
.Bookmark = .LastModified
MyIDValue = !ID
End With
rstCommunications.Close
 
Ken said:
You can move the recordset to the last modified record and then store the ID
value (see MyIDValue line):

Dim db As Database
Dim rstCommunications As Recordset
Set db = CurrentDb()
Set rstCommunications = db.openrecordset("tblCommunicationsSent",
dbopendynaset)

With rstCommunications
.AddNew
!CaseID = txtCaseID
!CommunicationType = "Letter"
!CommunicationName = txtLetterName
!Communication = txtLetterText
!CommunicationDate = Date
!CommunicationSentBy = Forms!fmnuMainMenu!txtAgentFirstName
.Update
.Bookmark = .LastModified
MyIDValue = !ID
End With
rstCommunications.Close
Click to expand...


Taking it one step further, for a Jet db AutoNumber field,
you don't even have to move to the new record. The ID field
is available as soon as you dirty the new record.
 
Back
Top