Displaying new record that was just added via .AddNew

[Guest]

I have a form bound to a query. That consist of two tables.

There's a field being displayed called COMMENTS. The comments table and the
customer's table are tied together by the Customer# and CMCSTR fields.

When the user clicks on the "ADD Comments" command button, I creat a new
record in the Comments table.

Here's my code:

Private Sub Command27_Click()
Dim custno As String
Dim rs As Object

custno = Str(Me![Customer#])
DoCmd.OpenForm "Enter_Comments", acNormal, , "Comments![Customer#] = " &
custno, acFormEdit

End Sub

On the comment's form, I have:

Private Sub Form_Current()
Dim rs As Object
Dim dDate As Date

Set rs = Me.Recordset.Clone
rs.AddNew
rs.[Customer#] = Me![Customer#]
rs.[CMCSTR] = Me![CMCSTR]
dDate = Date & " " & Time
rs.[Comments] = Str(dDate) & " " & vbCrLf
rs.Update
Me.Bookmark = rs.Bookmark
rs.Requery
End Sub

This creates the new record and puts in the current time/date.
After the user enters their comments, and closes the form, I have:

Private Sub Form_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Edit
rs.[Comments] = Me![Comments] & vbCrLf
rs.Update
rs.Requery
End Sub

When it goes back to the original form, I would like for the comment field
to show the new record. Instead it's showing the original record and I
haven't been able to have it display the new one.

I do have:

Private Sub Command27_Exit(cancel As Integer)
Me.Recordset.Requery
End Sub

And there's where I have tried putting in my logic to try to move to the new
record.

I know this is probably not the way to add a new record but being my first
attempt at doing this, I managed to get this to add new records. I think I
have the rs.Update and rs.Requery in the right place but I don't know
when/where I should have the Me.Bookmark = rs.Bookmark.

Thanks in advance for any help including how I should have coded this in the
first place. Like maybe putting the custno and sending that to the Add
comment routine:

custno = Str(Me![Customer#])
DoCmd.OpenForm "Enter_Comments", acNormal, , "Comments![Customer#] = " &
custno, acFormEdit, custno

 

[PC Datasheet]

You need to requery the form - not the recordset.
Me.Requery

 

[Guest]

Sorry for the late reply, but I left for vacation before I saw your response.

That didn't work either but at least you made me see that maybe I should be
working on the form in trying to display the new record and not on the
recordset.

What happens is that it stills displays the current record but when I
page-up/page-down, it goes to the very first record in the table. Whereas
before, it would go to the previous/next record where it would then show the
new record.

Again, thanks for your help.

You need to requery the form - not the recordset.
Me.Requery

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I have a form bound to a query. That consist of two tables.

There's a field being displayed called COMMENTS. The comments table and the
customer's table are tied together by the Customer# and CMCSTR fields.

When the user clicks on the "ADD Comments" command button, I creat a new
record in the Comments table.

Here's my code:

Private Sub Command27_Click()
Dim custno As String
Dim rs As Object

custno = Str(Me![Customer#])
DoCmd.OpenForm "Enter_Comments", acNormal, , "Comments![Customer#] = " &
custno, acFormEdit

End Sub

On the comment's form, I have:

Private Sub Form_Current()
Dim rs As Object
Dim dDate As Date

Set rs = Me.Recordset.Clone
rs.AddNew
rs.[Customer#] = Me![Customer#]
rs.[CMCSTR] = Me![CMCSTR]
dDate = Date & " " & Time
rs.[Comments] = Str(dDate) & " " & vbCrLf
rs.Update
Me.Bookmark = rs.Bookmark
rs.Requery
End Sub

This creates the new record and puts in the current time/date.
After the user enters their comments, and closes the form, I have:

Private Sub Form_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Edit
rs.[Comments] = Me![Comments] & vbCrLf
rs.Update
rs.Requery
End Sub

When it goes back to the original form, I would like for the comment field
to show the new record. Instead it's showing the original record and I
haven't been able to have it display the new one.

I do have:

Private Sub Command27_Exit(cancel As Integer)
Me.Recordset.Requery
End Sub

And there's where I have tried putting in my logic to try to move to the new
record.

I know this is probably not the way to add a new record but being my first
attempt at doing this, I managed to get this to add new records. I think I
have the rs.Update and rs.Requery in the right place but I don't know
when/where I should have the Me.Bookmark = rs.Bookmark.

Thanks in advance for any help including how I should have coded this in the
first place. Like maybe putting the custno and sending that to the Add
comment routine:

custno = Str(Me![Customer#])
DoCmd.OpenForm "Enter_Comments", acNormal, , "Comments![Customer#] = " &
custno, acFormEdit, custno