New Record Doesn't display after Update

[pademo57]

Hi There,
Yes, still a newbie AND trying to change somebody else work. I am working with Access 2003. I have a new record button on a form and I can get it to add a new record but I can't get it to display. However, if I close the form and re-open it there is the new record, what am I doing wrong? Here is the code (part mine part somebody else):

Private Sub cboAddRecord_Click()

On Error GoTo Error_Handler
   Dim db As Database
    Set db = CurrentDb
    
   Dim rs As DAO.Recordset
   Dim lngDlyRptID As Long
   
Set rs = db.OpenRecordset("tblDailyReport", dbOpenDynaset)
    
With rs
            .AddNew
                !txtName = Me.Name
                !txtInit = Me.txtInit
                !txtInstructor = Me.txtInstructor
                !datReportDate = Me.datReportDate
                !TrainingCode = Me.TrainingCode
                !TrainingDay = Nz(Me.TrainingDay, 0) + 1
                !TrainingDayOfCycle = Nz(Me.TrainingDayOfCycle, 0) + 1
                !TrainingCycle = Me.TrainingCycle
                !TrainingPhase = Me.TrainingPhase
             .Update
             rs.Bookmark = rs.LastModified
             'DRID = Daily Report ID - Primary Key
             lngDlyRptID = Me.DRID
             Set rs = Me.RecordsetClone
             
             'DRID = Daily Report ID - Primary Key
             rs.FindFirst "DRID = " & lngDlyRptID
             If Not rs.EOF Then
                Me.Bookmark = rs.Bookmark
             End If
             rs.Close
             Set rs = Nothing
End With
Exit_Procedure:
    Exit Sub

Error_Handler:
MsgBox "An error has occurred in this application. " _
& "Please contact your technical support person and " _
& "tell them this information:" _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
& Err.Description, _
Buttons:=vbCritical, Title:="UQS Unit Qualification Software V.111"
Resume Exit_Procedure

 

[JHB]

You are missing a Me.Requery.

    ......
    rs.Bookmark = rs.LastModified
    'DRID = Daily Report ID - Primary Key
    lngDlyRptID = Me.DRID
[COLOR=Red][B]    'I think here is the best place for it 
    Me.Requery
[/B][/COLOR]    Set rs = Me.RecordsetClone
    
    'DRID = Daily Report ID - Primary Key
    rs.FindFirst "DRID = " & lngDlyRptID
    ......

 

[pademo57]

Hi JHB,
I tried as you suggested but it still goes back to the previous record. When I stepped through using F8, I noticed that when I got to the line:

rs.Bookmark = rs.LastModified

It was the previous record and not the one I had just added. What am I doing wrong?

 

[pademo57]

Oh Brother, never mind!!!
I realize what I did wrong. I got the record information after the update instead of the other way around. Such a newbie move.

Thanks JHB for your help.

 

[pademo57]

ARGH! I thought I had it but I still can't get the form to update to the new record.
What happens now is that when I click the "Add Record" button it does create the new record and the program pauses for a second but goes back to the record I was at instead of moving to the new one.

E.g. I on record 101 and click the "Add Record" button. It creates the new record but it stays on record 101 and the record indicator at the bottom now says "record 101 of 102".

Why can't I add a new record and then have it move there with the click of a button?

Here is my code:

Private Sub butAddRecord_Click()
On Error GoTo Error_Handler
   Dim db As Database
    Set db = CurrentDb
    
   Dim rs As DAO.Recordset
   Dim lngDlyRptID As Long
   
Set rs = db.OpenRecordset("tblDailyReport", dbOpenDynaset)
    
With rs
            .AddNew
                !txtName = Me.Name
                !txtInit = Me.txtInit
                !txtInstructor = Me.txtInstructor
                !datReportDate = Me.datReportDate
                !TrainingCode = Me.TrainingCode
                !TrainingDay = Nz(Me.TrainingDay, 0) + 1
                !TrainingDayOfCycle = Nz(Me.TrainingDayOfCycle, 0) + 1
                !TrainingCycle = Me.TrainingCycle
                !TrainingPhase = Me.TrainingPhase
                lngDlyRptID = Me.DRID
             .Update
             
             'DRID = Daily Report ID - Primary Key
             
              Me.Requery
               
               rs.Bookmark = rs.LastModified
               
               
             Set rs = Me.RecordsetClone
             
             'DRID = Daily Report ID - Primary Key
             rs.FindFirst "DRID = " & lngDlyRptID
             If Not rs.EOF Then
                Me.Bookmark = rs.Bookmark
             End If
             rs.Close
             Set rs = Nothing
End With
Exit_Procedure:
    Exit Sub

Error_Handler:
MsgBox "An error has occurred in this application. " _
& "Please contact your technical support person and " _
& "tell them this information:" _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
& Err.Description, _

 

[eblieb]

You may want to rs.MoveLast if you aren't at the EOF...

 

[pademo57]

Thanks to everyone, a REAL NEWBIE move, I just realized what I did. In my code I put:

Me.DRID - which will give me the current record instead of the new one.

When I changed it to:

!DRID - THEN it gave me the new record.

LOL. I am laughing at myself, but I do wish to thank everyone for putting up with my lack of understanding.