Results 1 to 6 of 6
  1. #1
    Santonin is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2018
    Location
    USA
    Posts
    18

    Problem resorting a recordset after editing

    Hi All,
    I'm a novice user of Access 2003. I've written a single-user DB for myself which is working fine except for one new strange behaviour pattern which started happening after I added a command to a button to resort records in a form when I was done editing data. In the header of a subform, I have a label with some code attached to the OnClick event. The label is named "btnToggleData", and the code that I have in the OnClick event of the label is as follows:

    Private Sub btnToggleData_Click()
    ' A button to toggle the form between read-only and edit mode.
    On Error GoTo ErrorHandler
    If Me.AllowAdditions = False Then ' If form data is locked, then unlock form data and set visuals
    Me.AllowAdditions = True
    Me.AllowDeletions = True
    Me.AllowEdits = True
    Me.btnToggleData.BackColor = 128 ' Red
    Me.btnToggleData.BorderColor = 255 ' Bright Red
    Me.btnToggleData.ForeColor = 16777215 ' White
    Me.btnToggleData.Caption = "Lock Data" ' Tell user data is unlocked
    Me.btnToggleData.ControlTipText = "Click to lock data" ' Reword the button control tip
    Me.StepID.SetFocus
    ElseIf Me.AllowAdditions = True Then ' Otherwise lock the form data and set safe visuals
    Me.AllowAdditions = False
    Me.AllowDeletions = False
    Me.AllowEdits = False
    Me.btnToggleData.BackColor = 32768 ' Green
    Me.btnToggleData.BorderColor = 65280 ' Bright Green
    Me.btnToggleData.ForeColor = 16777215 ' White
    Me.btnToggleData.Caption = "Edit Data" ' Tell user data is locked
    Me.btnToggleData.ControlTipText = "Click to edit data" ' Reword the button instruction
    Me.Refresh ' Make sure all edited data is written
    DoCmd.RunCommand acCmdSortAscending ' Resort the displayed list of records
    End If
    Exit_Sub:
    Exit Sub
    ErrorHandler:
    If Err.Number = 2103 Then ' If the form which normally calls this form isn't open, then
    Resume Exit_Sub ' I'm working on the design, so don't bother me.
    Else ' Otherwise let me know if there's a problem
    MsgBox "Error: " & Err.Number & ": " & Err.Description
    Resume Exit_Sub
    End If
    End Sub


    The problem is with the sort command on the last line in the IF block. Before I added that command, I used the button for a long time with no problems. The reason I added the command to the button arose from my occasional need to change the StepIDs (PK) of procedural steps in a sequence in order to change the displayed order of the steps in the sequence. Under that condition, the data needs to be resorted immediately to show the changes to the user - not by making the user close and then reopen the form to see the properly sorted records. With the sort command added, the button works fine - sometimes.


    Initially, before I do any editing, when the forms first opens all the records in the subform are displayed in the order of the StepIDs - just as they should be. If I click the btnToggleData to unlock form data for editing, and then immediately click it again to lock the form data (without actually editing), the records are sorted and displayed correctly - in order of the StepIDs as they already were.


    However, if I click btnToggleData to unlock the form data for editing, and then actually edit any line of text - even just to add the word TEST to the end of a line - and then click btnToggleData to lock the form data again, the records are "sorted" and redisplayed in a jumbled order which is always exactly the same. This makes no sense to me. What makes even less sense to me is if I then click btnToggleData again to unlock the form data for editing, and then immediately click it again to lock the form data (which resorts the records again), then the records are resorted in their proper order by StepID as they should have been the first time when the records were jumbled up instead.


    In trying to find the problem, I looked at the form OrderBy property and I have seen something that i think is strange. I created the form with nothing in the form OrderBy property at all, and I have never put a line of code in my DB that intentionally sets the OrderBy property, yet I have seen two different criteria settings written there. All I know for certain is that the OrderBy property is getting set after I use btnToggleData the second time to resort the jumbled records from the first use. I haven't seen anything in the help files that says that the DoCmd.RunCommand modifies the OrderBy property of the form, so as far as I kow, it doesn't - even if it does make a kind of sense that it would. But the real problem is why the records are being jumbled in the first place. That also makes no sense to me.




    The OrderBy property of the form sometimes - not always - gets one of the following two criteria specifications written to the OrderBy property - however that happens:
    - tblZoneTaskDetails.StepID
    - tblZoneTaskDetails.StepDetail


    If anyone can give me some insight as to what causes this and how to stop it from happening, I would be grateful. I've attached a compacted and repaired and zipped copy of the DB in case someone wants to see this. To see it, open the DB. The first form will load automatically for you. Click to open any zone (up to New Reno) for Fallout 2 (the only game in the DB so far). On the Zone form that opens, click on the Tasks button, and then on the New Reno Prizefighter task. A Task form opens with an embedded subform for the steps in the Task. In the subform header is a green label which says "Edit Data". Click that and then edit any line of text in the subform. Add the word "TEST" to the end of a line and then click the label again to lock data. You will see the problem. Click the label twice more to see it work properly.

    Thanks in advance to anyone willing to take the time to figure this out. I've tried, and I've searched here, but I don't know how to search for a problem as odd as this. Also, as a novice, I wouldn't mind a few tips on what I need to do to make this DB less "Novice".
    Attached Files Attached Files

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Right before the docmd.runcommand accmdsortascending, set the focus to me.stepid

  3. #3
    Santonin is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2018
    Location
    USA
    Posts
    18
    Quote Originally Posted by davegri View Post
    Right before the docmd.runcommand accmdsortascending, set the focus to me.stepid
    Thank you. I'll try that.

  4. #4
    Santonin is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2018
    Location
    USA
    Posts
    18
    Quote Originally Posted by davegri View Post
    Right before the docmd.runcommand accmdsortascending, set the focus to me.stepid
    Yes. Thank you kindly. That worked. I'm guessing that I need to know more about the sort command. Much appreciate your help.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Glad it worked for you. Good luck with the project!

  6. #6
    Santonin is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2018
    Location
    USA
    Posts
    18
    Quote Originally Posted by davegri View Post
    Glad it worked for you. Good luck with the project!
    Actually, your solution does works to resort the records properly, which is the first step in the right direction, however the root cause of the problem is still there. I have been trying to figure this out and have some further information but still no answers. As I mentioned in the first post, I have a single-form "frmTask" with an embedded continuous subform "sfrmTaskSteps". Each of these forms has a ToggleData button as described above, with the only difference between them now being that the one in the subform now has the added commands to set the focus to the StepID and then to resort the records.

    When "frmTask" first opens, the three Allow properties are set to False in both the single form and its embedded subform (sfrmTaskSteps) and I use the toggle button to toggle forms between "locked" and "edit" modes. If I click the button on frmTask to toggle that form only into edit mode (i.e. to edit task notes), then only the code above the ElseIf line executes. There is nothing in that section of code that has anything to do with the data in the subform, yet the first time after opening the form, when I click the toggle button in the single form to unlock the data on the single-form only, the display of records in the continuous subform gets changed. Something is changing it, and I don't know what it is - but I have an idea.

    I have noticed that this is only happening in one data thread at the moment - Tasks - and I think I may have an idea why. When I designed the DB, in most cases I used composite primary keys. One reason was to have the ability to edit a sequential primary key field in a continuous subform and have the database resort the records in the subform based on the new ID sequence. This was my (probably novice) way of inserting a new records in the middle of an existing set of records (I don't understand ADO recordsets). By changing the record IDs to create a gap (i.e. between step 4 and step 5), I could then add new records with the IDs needed and then simply resort the records to display the new record where it needed to be in the sequence. I'm suspicious now that this methodology is somehow the root cause of the problem. I've only changed IDs in the TaskDetails subform so far, and the problem I'm having has not shown up anywhere else in the DB as yet (i.e. in Quests, or Features). This is what leads me to believe that my novice skills have yielded yet another DB with bad design, despite doing the best I know how, and this is why part of what I asked for above was some non-novice hints on how to upgrade my DB. I'm desperate for suggestions. So what I really need is someone who is curious and has the time to open the attached DB and see what I'm doing wrong. The problem could be originating at the form level, the relationship level, or maybe even at the table level. And again, the problem only shows up in the Task thread so far, and only the first time the toggle button on the single form is clicked. But my current skill level is insufficient to find this problem, and only tips from someone are going to help. My books don't respond to questions.

    Thanks in advance for any further assistance.
    Last edited by Santonin; 06-05-2020 at 01:35 AM. Reason: Additional information

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Resorting Combo Box In Alphabetical Order
    By DigitalAdrenaline in forum Access
    Replies: 4
    Last Post: 09-06-2016, 03:09 PM
  2. Replies: 12
    Last Post: 11-07-2014, 01:45 PM
  3. Problem editing Memo field in a subform
    By hertfordkc in forum Forms
    Replies: 2
    Last Post: 11-04-2014, 04:12 PM
  4. Editing with RecordSet Type = Dynaset
    By Access_Novice in forum Forms
    Replies: 4
    Last Post: 01-21-2014, 01:45 PM
  5. problem in editing records with visual basic
    By davidvhp in forum Programming
    Replies: 31
    Last Post: 12-05-2011, 07:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums