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