General discussion

Locked

Access 2k Changes record not viewed.

By brent.brewer ·
I have a database that was developed in Access 2.0, converted to Access 97 with no problems. Now when I converted it to Access 2K a very peculiar thing happens.

After pressing one of the ?sort? buttons I have created at the top of the form, any record changes that are made actually change the record that is exactly 405 records above it. The change is to the record that is 405 above it in the current sort order. So attempting to change the record sorted by Firstname will actually change a different record than changing the same recorded when sorted by Lastname. Note: sorting by using the toolbar sort ascend/descend

Note:
---This does NOT happen when sorting in the table itself, just when in the form.
---The Form is based on the table with no filters applied.
---The table has 58459 records.

This is the code that I use for the 'sort' button on the form.
--------
Private Sub Firstnamesort_Click()

If IsNull(Me![Firstname]) Then
previousloc = ""
Else
previousloc = Me![Firstname]
End If

On Error GoTo Firstnamesort_sort_err

'Me.OrderBy = "Firstname"
Me.OrderBy = "Firstname, Lastname, City"
If previousloc = "" Then Exit Sub
On Error GoTo Firstnamesort_find_err

Me![Firstname].SetFocus
DoCmd.FindRecord previousloc, acStart, False

Firstnamesort_exit:
Exit Sub

Firstnamesort_sort_err:
MsgBox "Could not sort"
Resume Firstnamesort_exit

Firstnamesort_find_err:
MsgBox "Could not find previous record"
Resume Firstnamesort_exit

End Sub

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 2k Changes record not viewed.

by brent.brewer In reply to Access 2k Changes record ...

Sorting by using the Toolbar 'Sort Ascending' or 'Sort Descending' does not cause this problem.

Collapse -

Access 2k Changes record not viewed.

by brent.brewer In reply to Access 2k Changes record ...

Point value changed by question poster.

Collapse -

Access 2k Changes record not viewed.

There was a similar problem a couple of years ago which caused this type of problem. It occurred after a record had been deleted and I believe that there was a fix released.
The provisional solution was to requery the recordset after a delete operation. Perhaps if you requery after the sort it would have the same effect.

Also, you are using the first name field to find the previous record. You might try using the recordset bookmark property instead.
Hope this helps
John

Collapse -

Access 2k Changes record not viewed.

Article Q191883 on the MSKB (http://support.microsoft.com/directory/default.asp and enter the article number) discusses this problem.

Collapse -

Access 2k Changes record not viewed.

by brent.brewer In reply to Access 2k Changes record ...

Form does not allow adding or deleting records.
Requery has no effect on the problem.
Using recordset bookmark has no effect on the problem.

Collapse -

Access 2k Changes record not viewed.

by brent.brewer In reply to Access 2k Changes record ...

Problem appears to be related to the attached table indexes. If record count in the table is reduced to 10102, the problem does not exist, at 10103 records the problem exists.

If certain indexes are deleted the problem does not exist.

It appears that an index on lastname and an index on lastname and firstname conflict when there are more that 10102 unique records.

Collapse -

Access 2k Changes record not viewed.

by brent.brewer In reply to Access 2k Changes record ...

I have resolved this.

When a form that is based on a table containing 10103 records or more
And the form is sorted using Me!Orderby = "fieldname, fieldname2"
And the table has an index that is identical to the Orderby on field list (upto 10 fields)
And a docmd.Findrecord is executed
The recorded that is actually changed is 405 records higher sequentialy in the current sort order.

Resolution: Make sure that your sort order is not an index in the table.

Collapse -

Access 2k Changes record not viewed.

by brent.brewer In reply to Access 2k Changes record ...

This question was closed by the author

Back to Web Development Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums