How to set focus on record just added

[GraeagleBill]

On a continuous form, how can I set focus on the record just added to the RecordSource recordset.

Here's the code that adds the record:

strSQL = "INSERT INTO PAYMENTLEDGER (LedID,RetYear,LedDate) VALUES ("
strSQL = strSQL & UserID & ",'" & gblRetreatYear & "',#" & DATE & "#);"
CurrentDb.Execute strSQL
Me.Requery

I could probably use DoCmd.FindRecord "LedID" if I knew the newly assigned autonumber'd LedID? Maybe use DMax to find the LedID, but all of that seems a bit convoluted so I thought I'd ask here for the simplest approach.

Thanks,


Bill

[pbaldy]

Hi Bill!

I'd use a recordset to add the record, grab the ID, then use a bookmark to go to the record. Here's how to get the id (the lines in red are getting the ID):

        With rsMaster
          .AddNew
          !FieldName = Whatever
          .Update
          .Bookmark = .LastModified
          Variable = !KeyFieldName
        End With

[GraeagleBill]

RESET!!!!!!

Sorry, LedID IS NOT an autonumber field. Rather is the ID of the current entry within the "current ledgers collection" table "PAYMENTLEDGER".

It is RecID that would be the newly assigned record ID of the new record for "current ledger collection" LedID

[GraeagleBill]

Paul,
You mean like this:

Private Sub cmdAddPmt_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Command to trigger the insertion of a new payment record.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strSQL As String
Dim UserID As Integer
Dim intNewID As Integer

UserID = Forms!frmRosterDetail!tbOpenID      'Parent stored current ID in an invisible text box for our use here

Dim rs As DAO.Recordset
  
    Set rs = DBEngine(0)(0).OpenRecordset("QPaymentLedger")
    
    rs.AddNew
    
    rs!LedID = UserID
    rs.Update
    
    rs!RetYear = gblRetreatYear
    rs.Update
    
    rs!LedDate = DATE
    rs.Update
    
    rs.Bookmark = .LastModified
    intNewID = !RecID

    rs.Close
    Set rs = Nothing

Me.Requery

End Sub

If so, then what? DoCmd.FindRecord intNewID?

[GraeagleBill]

The ".Edit"s are missing and rs.Bookmark needs to be rs.LastModified

[pbaldy]

More like

rs.AddNew
rs!LedID = UserID
rs!RetYear = gblRetreatYear
rs!LedDate = DATE
rs.Update

rs.Bookmark = .LastModified
intNewID = !RecID

Then adapting this to use intNewID after the requery:

http://www.baldyweb.com/Requery.htm

[GraeagleBill]

Paul,
Revisions work perfectly as you've suggested. I like your method of adding new records. I've had other instances where the SQL statements with mixed data types have been a bugger to debug because of syntax among the values.

Private Sub cmdAddPmt_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Command to trigger the insertion of a new payment record.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strSQL As String
Dim UserID As Integer
Dim lngNewID As Integer

UserID = Forms!frmRosterDetail!tbOpenID      'Parent stored current ID in an invisible text box for our use here

Dim rs As DAO.Recordset
  
    Set rs = DBEngine(0)(0).OpenRecordset("QPaymentLedger")
    
    rs.AddNew
    rs!LedID = UserID
    rs!RetYear = gblRetreatYear
    rs!LedDate = DATE
    rs.Update
    
    rs.Bookmark = rs.LastModified
    lngNewID = rs!RecID

    rs.Close
    Set rs = Nothing

Me.Requery

    With Me.RecordsetClone
      .FindFirst "RecID = " & lngNewID
      Me.Bookmark = .Bookmark
    End With

End Sub

[pbaldy]

Happy to help Bill! Let's hope we get some snow in this upcoming storm.

[GraeagleBill]

We got between 9 and 10 inches here Sunday night............... really wet stuff.

[GraeagleBill]

The Feather river looks like the Mississippi from all the rain from the previous days. California has a long way to go, but moisture much improved compared to the previous 3 or 4 years.

[pbaldy]

Great! We need to fill the lakes.