Bookmark not working on subform

[gsnidow via AccessMonster.com]

Greetings all. I have a main form/sub form set up, where the subform
contains possibly hundreds of records, depending on filtering. On the
subform, there is a button to open a popup to add a note. After the note is
saved, I need to requery the subform to display the note. I am trying to use
a bookmark, but it only works sometimes. If I add a note to a record that
already has a note, the bookmark works. However, if I add a note to a record
that does not currently have a note, it does not work, and returns to the
first record upon the requery. Below is the sub, and I can not figure out
why it is only working some of the time. Maybe it is something simple.
Thank you.

Private Sub cmdAddNote_Click()
On Error GoTo Err_cmdAddNote_Click

Dim rs As New ADODB.Recordset

Dim strSQL As String
Dim COMP_KEY As String 'this is the primary key of the record set

'Get the key prior to inserting the note
COMP_KEY = Forms!frmZMMR41_ZZMB52_Main!Child1.Form!txtCompKey.Value

strSQL = "INSERT INTO tblstorekeeper_notes(comp_key,note) " & _
"SELECT '" & COMP_KEY & "', '" & Replace(Me.txtAddNote, "'",
"''") & "'"

'Insert the note
DoCmd.RunSQL strSQL

'create the recordset clone
Set rs = Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Recordset.Clone
'Requery the form to display the new note
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Requery
'Go back to the record
rs.MoveFirst
rs.Find "comp_key = " & COMP_KEY
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Bookmark = rs.Bookmark

DoCmd.Close acForm, "frmAddNote"


Exit_cmdAddNote_Click:
Exit Sub

Err_cmdAddNote_Click:
MsgBox Err.Description
Resume Exit_cmdAddNote_Click
End Sub

Greg

 

[Jeanette Cunningham]

'create the recordset clone
Set rs = Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Recordset.Clone
'Requery the form to display the new note
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Requery

rs.FindFirst "comp_key = " & COMP_KEY
If Not rs.NoMatch Then
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Bookmark = rs.Bookmark
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[gsnidow via AccessMonster.com]

Jeanette,
I have read several posts indicating the rs.FindFirst method, but it
always errors for me. I probably should have noted I am using Access 2003 .
ADP with SQL Server 2000. One more thing, I put a message box in to display
the rs.bookmark value, and in every case it returns the correct value, even
on the records where the bookmark does not work. I also tried wrapping the
comp_key value in single quotes, and changing the requery to a refresh, and
that did not help either.

Set rs = Forms!frmZMMR41_ZZMB52_Main!Child1.Form.RecordsetClone

rs.Find "comp_key = '" & COMP_KEY & "'"
MsgBox (rs.Bookmark) '<<== always displays correct value
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Refresh
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Bookmark = rs.Bookmark

'create the recordset clone
Set rs = Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Recordset.Clone
'Requery the form to display the new note
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Requery

rs.FindFirst "comp_key = " & COMP_KEY
If Not rs.NoMatch Then
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Bookmark = rs.Bookmark
End If

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Greetings all. I have a main form/sub form set up, where the subform
contains possibly hundreds of records, depending on filtering. On the

[quoted text clipped - 48 lines]

Greg

 

[gsnidow via AccessMonster.com]

GRRRR.... I thought I was on to something by changing rs.close to Set rs =
Nothing. I went down the subform and added a note to the first 20 records,
and the bookmark worked every time. However, on the 21st try, back to the
top of the form.

Jeanette,
I have read several posts indicating the rs.FindFirst method, but it
always errors for me. I probably should have noted I am using Access 2003 .
ADP with SQL Server 2000. One more thing, I put a message box in to display
the rs.bookmark value, and in every case it returns the correct value, even
on the records where the bookmark does not work. I also tried wrapping the
comp_key value in single quotes, and changing the requery to a refresh, and
that did not help either.

Set rs = Forms!frmZMMR41_ZZMB52_Main!Child1.Form.RecordsetClone

rs.Find "comp_key = '" & COMP_KEY & "'"
MsgBox (rs.Bookmark) '<<== always displays correct value
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Refresh
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Bookmark = rs.Bookmark

'create the recordset clone
Set rs = Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Recordset.Clone

[quoted text clipped - 13 lines]

 

[Jeanette Cunningham]

I probably should have noted I am using Access 2003 .
ADP with SQL Server 2000.

Yes, a good idea to mention it. I have to bow out here.
I don't have experience with Access 2003 .
ADP with SQL Server 2000.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,
I have read several posts indicating the rs.FindFirst method, but it
always errors for me. I probably should have noted I am using Access 2003
.
ADP with SQL Server 2000. One more thing, I put a message box in to
display
the rs.bookmark value, and in every case it returns the correct value,
even
on the records where the bookmark does not work. I also tried wrapping
the
comp_key value in single quotes, and changing the requery to a refresh,
and
that did not help either.

Set rs = Forms!frmZMMR41_ZZMB52_Main!Child1.Form.RecordsetClone

rs.Find "comp_key = '" & COMP_KEY & "'"
MsgBox (rs.Bookmark) '<<== always displays correct value
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Refresh
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Bookmark = rs.Bookmark

'create the recordset clone
Set rs = Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Recordset.Clone
'Requery the form to display the new note
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Requery

rs.FindFirst "comp_key = " & COMP_KEY
If Not rs.NoMatch Then
Forms!frmZMMR41_ZZMB52_Main!Child1.Form.Bookmark = rs.Bookmark
End If

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Greetings all. I have a main form/sub form set up, where the subform
contains possibly hundreds of records, depending on filtering. On the

[quoted text clipped - 48 lines]

Greg

 

[gsnidow via AccessMonster.com]

Ok, I have simplified things a bit. I took out all of the SQL and insert
stuff, so now the only thing the sub is doing is showing the current rs.
bookmark then doing a requery on the sub form, then attempting to apply the
bookmark. I have added an INT primary key to the table, thinking my long
VARCHAR key was the problem. Still the same thing. Below is the new
simplified code, and, again, the bookmark works some of the time, but not
others. In all cases, the rs.bookmark shown by the msgbox is the same as the
ID field of the table. Thank you for any advice.


Private Sub cmdAddNote_Click()
On Error GoTo Err_cmdAddNote_Click

'There is a button on the sub form that opens up
'the popup form "frmAddNote", which has only one text box
'and one button, cmdAddNote which, when cliked, fires
'this sub.
Dim rs As New ADODB.Recordset

'declare variable for subform to simplify typing
Dim frm As Form
Set frm = Forms!frmZMMR41_ZZMB52_Main!Child1.Form

Dim ID As Long 'this is the new primary key of the record set

'Get the key prior to inserting the note
ID = frm!ID.Value

Set rs = frm.RecordsetClone
rs.MoveFirst
rs.Find "id = " & ID
'Display the rs.bookmark. It should always be equal
'to the value displayed in the ID text box, which is
'the new primary key of the table. In all cases, the
'bookmark displayed is correct.
MsgBox (rs.Bookmark)
frm.Requery
frm.Bookmark = rs.Bookmark

rs.Close

DoCmd.Close acForm, "frmAddNote"

Exit_cmdAddNote_Click:
Exit Sub

Err_cmdAddNote_Click:
MsgBox Err.Description
Resume Exit_cmdAddNote_Click
End Sub

I probably should have noted I am using Access 2003 .
ADP with SQL Server 2000.

Yes, a good idea to mention it. I have to bow out here.
I don't have experience with Access 2003 .
ADP with SQL Server 2000.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,
I have read several posts indicating the rs.FindFirst method, but it

[quoted text clipped - 34 lines]