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".