none
Bookmark giving inconsistent results

    Question

  • I'm in the process of designing an .accdb solution for some of my business partners that involves a form and subform.  When they change a Network ID value on the subform, it needs to analyze each existing ID in the recordsource of the subform, and if it finds one that is on a specified list, it needs to change the value of the 2nd QR Required field on the parent form to Yes and change the Rowsource of the 2nd QR Required field to Yes.  If the recordsource does not contain any networks from the specified list the value of 2nd QR required is changed to No and the Rowsource is changed to No.  This all occurs on the AfterUpdate event of the Network ID field.

    It appears, however, that when the line of code to change the Rowsource of the 2nd QR Required field executes, it sets the subform back to the 1st record.  I don't want this to happen.  The subform needs to remain on the record that just had it's network ID updated.  In order to accomplish this, I've been playing around with the Bookmark property but have been noticing inconsistent results.

    I've tried 2 different approaches to the use of Bookmark and seem to get similar results.  If I step the code, it works as expected; Me.Bookmark is set to either the variable I set to store the bookmark before the subform flipped back to the first record or to myRecords.Bookmark of the RecordsetClone of my subform's recordsource, and that triggers the Form_Current event.  However, if I just let the code execute, the Form_Current event triggers, but it's for the first record...as if the Me.Bookmark = varBookmark line did not execute.

    Approach 1:
    *NOTE: this approach had a unique effect that if I put a breakpoint @Set recNetworks and stepped from there, it worked fine, but if I put my breakpoint @Me.Bookmark = recNetworks.Bookmark and tried to step from that point, it gave a run-time error 3021.  I had to execute recNetworks.FindFirst "TempNRID = " & lngNRID a second time for it to recognize the current record.

                lngRLID = Me.Parent.RecordLocatorID           
                lngNRID = Me.TempNRID
                
                If 2ndQRNetworkFound(lngRLID) = True Then
                    'at least 1 network listed for record is in xSettings as a 2nd QR Required Network, must be Yes (-1)
                    Me.Parent.2ndQRRequired = -1
                    Me.Parent.2ndQRRequired .RowSource = "Yes"
                Else
                    'value should be no; business will want to update requirements if any other networks need 2nd QR
                    Me.Parent.2ndQRRequired  = 0
                    Me.Parent.2ndQRRequired .RowSource = "No"
                End If
                
                Set recNetworks = Me.RecordsetClone

                recNetworks.FindFirst "TempNRID = " & lngNRID
                Me.Bookmark = recNetworks.Bookmark

    Approach 2:
                lngRLID = Me.Parent.RecordLocatorID           
                varBookmark = Me.Bookmark

                If 2ndQRNetworkFound(lngRLID) = True Then
                    'at least 1 network listed for record is in xSettings as a 2nd QR Required Network, must be Yes (-1)
                    Me.Parent.2ndQRRequired = -1
                    Me.Parent.2ndQRRequired .RowSource = "Yes"
                Else
                    'value should be no; business will want to update requirements if any other networks need 2nd QR
                    Me.Parent.2ndQRRequired  = 0
                    Me.Parent.2ndQRRequired .RowSource = "No"
                End If

                Me.Bookmark = varBookmark

    Any suggestions?

     

    Monday, December 27, 2010 10:40 PM

Answers

  • Hello.

    All this doesn't seem to be suspect for me.
    I have never checked what the RecordsetClone does if your curent record is dirty or new , but I suggest to set the form's bookmark onliy if FindFirst was successful, somewhat like this (in approach1):

    If Me.TempNRID <> lngNRID Then
     With Me.RecordsetClone
      .FindFirst "TempNRID = " & lngNRID
      If Not .NoMatch Then Me.Bookmark = .Bookmark
     End With
    End If
    

    If indeed the subform jumps to the first record, this will most likely be caused by a requery (caused by whatever), therefore approach2 (which stores a bookmark before manipulating the data) can't work (after a requery, all bookmarks become invalid).

    Also, after setting the value in the main form it gets "dirty". When is the record saved and what happens in the Before/AfterUpdate events in the main form?

    "How dioes the function work" means "what does the code of the function 2ndQRNetworkFound look like".

    When the Network ID field is updated in the subfom, you change the rowsource of the ComboBox to only contain one value. How should the business then be able to choose a different value? I suggest not to change the rowsource of the combo. If you don't wamt the value to be changed, disable the combobox instead.

    --
    Regards,
    Wolfgang

    • Proposed as answer by Bruce Song Thursday, January 06, 2011 10:05 AM
    • Marked as answer by DinosRose Thursday, January 06, 2011 2:53 PM
    Wednesday, December 29, 2010 8:37 AM

All replies

  • Hello DinosRose.

    In all my tests in Acces 2003, I wasn't able to reproduce the problem that updating the RowSource moved to the first record of the subform. Changing the record in the main form does of course requery the sbform (as expected).  Some questions arose:

    Is the subform linked to the main form using (maybe among others) the 2ndQRRequired field?
    What does the code do in the current event?
    How does your function work?
    Why on earth would you swap the RowSource between "Yes" and "No"? Can't you base the ComboBox (i suppose that it is a ComboBox) on the list containing both entries? If you don't want the user to be able the change the value, then lock the control. But if so, why not use a simple TextBox?

    --
    Regards,
    Wolfgang

    Tuesday, December 28, 2010 8:11 AM
  • Thanks for your additional inquiry....

    The subform is linked to the main form, but it is linked on a field called  RecordLocatorID.
    Current event, determines if parent form is locked and shades all controls on subform to visually see they are "locked"; if parent not locked, it sets which controls are locked/enabled based on the parent form's Type of Request.
    Not sure what you mean by "how does your function work".
    The control is a ComboBox because in rare situations (Types of Request), the business asked to be able to set that value to either Yes or No.  In other cases, it has to be Yes, and in other cases, it has to be No.  I could leave the RowSource to be Yes or No for all Types of Requests, but then would require validation on the back end to make sure the users chose the right value before they can "save".

    Hope that answers your questions...

    Tuesday, December 28, 2010 3:28 PM
  • Hello.

    All this doesn't seem to be suspect for me.
    I have never checked what the RecordsetClone does if your curent record is dirty or new , but I suggest to set the form's bookmark onliy if FindFirst was successful, somewhat like this (in approach1):

    If Me.TempNRID <> lngNRID Then
     With Me.RecordsetClone
      .FindFirst "TempNRID = " & lngNRID
      If Not .NoMatch Then Me.Bookmark = .Bookmark
     End With
    End If
    

    If indeed the subform jumps to the first record, this will most likely be caused by a requery (caused by whatever), therefore approach2 (which stores a bookmark before manipulating the data) can't work (after a requery, all bookmarks become invalid).

    Also, after setting the value in the main form it gets "dirty". When is the record saved and what happens in the Before/AfterUpdate events in the main form?

    "How dioes the function work" means "what does the code of the function 2ndQRNetworkFound look like".

    When the Network ID field is updated in the subfom, you change the rowsource of the ComboBox to only contain one value. How should the business then be able to choose a different value? I suggest not to change the rowsource of the combo. If you don't wamt the value to be changed, disable the combobox instead.

    --
    Regards,
    Wolfgang

    • Proposed as answer by Bruce Song Thursday, January 06, 2011 10:05 AM
    • Marked as answer by DinosRose Thursday, January 06, 2011 2:53 PM
    Wednesday, December 29, 2010 8:37 AM