Bookmark not working on subform
in [Forms]
|
Prev: How to calculate diffrence between two dates
Next: Recognising the user switch between Form view and Data sheet v
From: gsnidow via AccessMonster.com on 2 Mar 2010 15:44 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 -- Message posted via http://www.accessmonster.com
From: Jeanette Cunningham on 2 Mar 2010 16:27 '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" <u33475(a)uwe> wrote in message news:a46c354cb005c(a)uwe... > 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 > > -- > Message posted via http://www.accessmonster.com >
From: gsnidow via AccessMonster.com on 2 Mar 2010 16:41 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 Jeanette Cunningham wrote: >'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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1
From: gsnidow via AccessMonster.com on 2 Mar 2010 17:01 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. gsnidow wrote: >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] >>> >>> Greg -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1
From: Jeanette Cunningham on 3 Mar 2010 02:49
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 "gsnidow via AccessMonster.com" <u33475(a)uwe> wrote in message news:a46cb5a907df7(a)uwe... > 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 > > Jeanette Cunningham wrote: >>'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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 > |