need help with gotorecord

[congngo]

Below is my code. What I want do is ,after running the insert
statement into tblarchive, I want to go to the subform and set focus to
the record that I just added in
Confirm = "'" + CONF + "'"
usr = "'" + CurrentUser() + "'"
strSQL = "INSERT INTO tblarchive ( conf, csrID, csdate, comments )
SELECT " & Confirm & " AS Expr1, " & usr & " AS Expr2, Date() AS
Expr3, 'cxl' AS Expr4;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL (running the insert sql statement)
DoCmd.SetWarnings True
DoCmd.Close acForm, "cancel", acSaveYes
Forms![MAIN-members]![Child135].SetFocus (set focus to the
subform)
Forms![MAIN-members]![Child135]!comments.SetFocus
DoCmd.GoToRecord (need help here go to the record that just ran
above)

thank

[Allen Browne]

You might be able to Requery the subform, and then go to the last record.
But if the table does not have an AutoNumber, or if the AutoNumber is not
sequential, or if other users are adding records, this might not take you to
to desired one.

Would you consider an AddNew on the RecordsetClone of the subform instead of
running the update query? That avoids the Requery, and you know for certain
that you are ending up with the correct record.

This kind of thing (assuming the code goes in the main form's module):
Dim rs As DAO.Recordset
With Me.Child135.Form
Set rs = .RecordsetClone
rs.AddNew
rs!conf = Confirm
rs!csrID = use
rs!csdate = Date()
rs!comments = "cxl"
rs.Update
.Bookmark = rs.LastModified
End With
Set rs = Nothing

The alternative would be to Execute the query statement instead of RunSQL,
and ask Access for the new identity value, requery the subform, and then
FindFirst in its RecordsetClone. That's a long way round for a shortcut, and
works in JET 4 only (Access 2000 and later.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<co*****@yahoo.com> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.com...

Below is my code. What I want do is ,after running the insert
statement into tblarchive, I want to go to the subform and set focus to
the record that I just added in
Confirm = "'" + CONF + "'"
usr = "'" + CurrentUser() + "'"
strSQL = "INSERT INTO tblarchive ( conf, csrID, csdate, comments )
SELECT " & Confirm & " AS Expr1, " & usr & " AS Expr2, Date() AS
Expr3, 'cxl' AS Expr4;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL (running the insert sql statement)
DoCmd.SetWarnings True
DoCmd.Close acForm, "cancel", acSaveYes
Forms![MAIN-members]![Child135].SetFocus (set focus to the
subform)
Forms![MAIN-members]![Child135]!comments.SetFocus
DoCmd.GoToRecord (need help here go to the record that just ran
above)

[congngo]

Thank you for the code but it's keep on jumping back the first record
on the subform
child135. it does not stay at the record that it just modified.

just curious if I set
Set rst = CurrentDb.OpenRecordset("tblarchive", dbOpenDynaset)
this should works as well. is there a preference of RecordsetClone
over
OpenRecordset?

Dim rst As DAO.Recordset
Dim strSQL, Confirm, usr As String

Confirm = CONF
usr = CurrentUser()
DoCmd.Close acForm, "cancel", acSaveYes

Forms![MAIN-members]![Child135].SetFocus
Forms![MAIN-members]![Child135]!comments.SetFocus

With Forms![MAIN-members].Child135.Form
Set rst = .RecordsetClone
rst.AddNew
rst!CONF = Confirm
rst!csrID = usr
rst!csdate = DATE
rst!comments = "cxl"
rst.Update
.Bookmark = rst.LastModified
End With
Set rst = Nothing

[Allen Browne]

Jumping back to the first record after setting the form bookmark is not the
normal behavior, so something else must be going on as well.

It could be a Requery, or it might be related to something that happens in
the main form. Access reloads the subform when the main form moves record
(or is requeried.)

If you OpenRecordset on the table and add the record there, the new record
will not be in the form's recordset until a requery. You will then need to
FindFirst in the RecrodsetClone of the of the form, i.e. you cannot match
the Bookmark of the table's recordset against that of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<co*****@yahoo.com> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...

Thank you for the code but it's keep on jumping back the first record
on the subform
child135. it does not stay at the record that it just modified.

just curious if I set
Set rst = CurrentDb.OpenRecordset("tblarchive", dbOpenDynaset)
this should works as well. is there a preference of RecordsetClone
over
OpenRecordset?

Dim rst As DAO.Recordset
Dim strSQL, Confirm, usr As String

Confirm = CONF
usr = CurrentUser()
DoCmd.Close acForm, "cancel", acSaveYes

Forms![MAIN-members]![Child135].SetFocus
Forms![MAIN-members]![Child135]!comments.SetFocus

With Forms![MAIN-members].Child135.Form
Set rst = .RecordsetClone
rst.AddNew
rst!CONF = Confirm
rst!csrID = usr
rst!csdate = DATE
rst!comments = "cxl"
rst.Update
.Bookmark = rst.LastModified
End With
Set rst = Nothing