Erratic results from code

[hartster]

Using Access 2002 SP3 on XP SP2.

Database is an underground mine mapping tool. A rock face is sampled across its width, the geologist enters the Face_ID (primary key of Face table). Each face has multiple samples in the Samples table (tblSamples), linked to the Face table with a one-to-many relationship.

Each sample has a width. Starting at 0.0m the sample widths (![Length]) are entered and the code calculates the start (![From_Depth]) and end (![To_Depth]). A counter records the order in which the samples are entered.

Some samples have widths of 0.0m as they are check samples, i.e. there are two samples taken from the same interval. These are usually entered at the end of the face. It is the 0.0m entries that are problematic, sometimes but not all the time, the From_Depth and To_Depths are scrambled.

The form OrderBy property is set to: Counter, Face_ID, Sample_No, From_Depth, To_Depth, exactly the same as the underlying query that creates the dynaset, and the table underlying the query.

In the attached screen dump of the tables, the record F20080304-02 has the first sample entered displaying what should be the last sample interval, i.e. 5.0m to 5.7m.

But the counter says it was the first record entered (0) and with the code and indexes in mind, how do I end up with the highest From_Depth and To_Depth values first???

In the test record F20090101-01 exactly the same data is entered but the sample intervals are correct, i.e. not scrambled.

This is an erratic problem, i.e. sometimes it happens, sometimes not. The people doing the data entry swear that they are entering data in a consistent manner.

Any advice greatly appreciated.

Sub is initiated by a form after update event procedure.

Public Sub UpdateFromTo()

Dim strCut As String
Dim dbs As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset
Dim nLastTo As Single


On Error GoTo ErrHandle
strCut = Parent![Face_ID]

'Refresh from-to
Set dbs = CurrentDb
strSQL = "Select * from tblSamples where Face_ID = '" & strCut & "';"
Set rst = dbs.OpenRecordset(strSQL)
With rst
If .EOF Then
Else
.MoveFirst
.Edit
![From_Depth] = 0
![To_Depth] = ![Length]
nLastTo = ![To_Depth]
.Update
.MoveNext
Do Until .EOF
.Edit
![From_Depth] = nLastTo
![To_Depth] = ![Length] + nLastTo
nLastTo = ![To_Depth]
.Update
.MoveNext
Loop
End If
End With

Exit_Now:
On Error GoTo 0
Set dbs = Nothing
Set rst = Nothing
Exit Sub

ErrHandle:
GoTo Exit_Now

End Sub

 

[WayneRyan]

Hartster,

Without going into too much depth,

strSQL = "Select * from tblSamples where Face_ID = '" & strCut & "';"

There is no ORDER BY clause in the above Select statement, the
records will be in arbitrary order.

Wayne

 

[hartster]

Thanks Wayne. I've added an ORDER BY clause:

strSQL = "Select * from tblSamples where Face_ID = '" & strCut & "' ORDER BY Counter;"

Do the indexes in the subform and underlying query not sort the data selected by the SQL string? Or can the Select string produce a set of records that somehow avoid getting sorted on an occasional basis. IE the subform is losing its sort order erratically during the data entry process.

 

[WayneRyan]

Hartster,

You should always explicitly include an Order By clause.

Forms, Reports, and Queries all have the means to to this.

Otherwise, the order of the data presented is not predictable.
In your case, the detached recordset had no predictable order.

hth,
Wayne

 

[hartster]

Thanks Wayne, that's solved the problem. Cheers.