Form field displays wrong record information

Question

Our database is about 55mb in size and with just over 135 thousand records in one particular table.

When we do a find on our form for a particular record with Bookmarks/Clone everything looks fine except when you tab over the form fields of the found record the values in the field then change to details of another record - one by one as you tab over.

This will not happen all the time and happens randomly.  Sometime you can be data entering for an hour or longer, other times less and then you will start to encounter this problem.

We have found that if we reduce the number of records in the database then the problem goes away till the number of records start climbing up again.

We are using Access 2010 in a terminal server environment.

Answer 1

What's the datatype of the ID field? If it's Long Integer (which I'm guessing it is, since you don't need quotemarks delimiting it) what's the point of using Str() - converting the Number to String and then forcing Access to implicitly convert back? 135K records is certainly not trivial, but neither should it be making Access crack a sweat with searching - I've worked with tables over twice that size routinely without needing to go to SQL.

Answer 2

Hi John

No problems. Best to ask and be clear.

When it happens its at a random time and record, but interestingly the record that is affected can then be replicated on another machine.

I have not tried using filters to find the record and see if the issue is resolved.

There is a relationship of the problem to the number of records and volume of information per record as I have found that by splitting the table into smaller chunks (currently 2) the problem goes away for a while till we start adding a lot more records.  The tables contain information that cannot be normalised.  Memory issue perhaps and related to the way we are find the record?

The code we are using is as follows:

    Dim sTD_ID As String

    Dim rs As DAO.Recordset

    Set rs = Me.RecordsetClone

    sTD_ID = Str(Nz(Me![cboID], 0))

    rs.FindFirst "[ClientId]=" & Str(Nz(Me![cboID], 0))

    If rs.NoMatch Then

        MsgBox "Sorry, no such record '" & Str(Nz(Me![cboID], 0)) & "' was found.", _

               vbOKOnly + vbInformation

        Me.Combo5.SetFocus

        Me.Combo5.Value = ""

    Else

        Me.Recordset.Bookmark = rs.Bookmark

        If ClientId = 0 Or Str(Me.TD_ID) <> sTD_ID Then

            MsgBox "WARNING - record not found or incorrect client record may be displayed"

            Me.cboID.SetFocus

            Me.cboID.Value = ""

        ElseIf IsNull(DateCalled) Then

            DateCalled.SetFocus

        Else

            MsgBox "Date already exists - please check"

            Me.SetFocus

            Me.cboID.Value = ""

        End If

    End If

    rs.Close

    Set rs = Nothing

Cheers

Mike

Answer 3

Hrm. Sounds like you're doing all the right things - had to ask, but you're clearly ahead of me there!

Is there any rationale for which control causes the effect? Or for which record it brings up? Does filtering the form (rather than using the bookmark) work? Could you post your recordsetclone code?

Answer 4

Hi John

We do have multiple users but have had the problem when there is only one user.

DB is split into front and backend, with multiple front ends for each user.

We have tried with an SQL backend and still get the same issue.  Compact repair and decompile options doesnt help.

The recordset of the form is defined as the table with fetch default option off.  Tab order is fine and we do not use the option to cycle to the next record when the last field is tabbed.

When tabbing through the form fields of the record found, the values of each form field change individually as you tab over.

Our steps are:

1. Search for record required by entering an ID into a form field and doing a bookmark/clone to find the required record
2. Record required is found and displayed in the form (everything looks great at this stage)
3. Start tabbing or clicking directly onto a field and the display information gets changed to details from another record.

Answer 5

Do you have multiple users sharing the same database? If so the database MUST be split into a Backend containing the tables, and a separate copy of a Frontend for each user, linked to the shared backend.

If that's not the issue, does Compacting the database resolve the issue? What's the Recordsource for the form? What about the form's Tab Order, and its Cycle property? If you tab through the controls on a form and get to the last control in the form's tab order, it will silently jump to the first field in the tab order in the next record in the table.