consistent error on first record

[Guest]

The following code is run on two queries, in order to update a field on the
master query (rst1). It is designed to take the first record in rst1 and
determine if the ID from rst1 is matched by a single record in rst2 (in which
case the capture field will be equal to "I") or more than one record in rst2
(in which case the capture field will be equal to "R"). The program runs
fine on all rcords in rst1 except for the first. The first two records in
rst2 have the same ID as rst1. The first has capture = "I" and the second
has capture = "R". Therefore, I am expecting the JUSTI field to be updated
with a "no", but it is updated with a "yes". All other records appear to be
updated correctly. Any ideas on which the first update is erroneous.

Thanks,

LAF


Option Compare Database
Option Explicit

Sub InitialAge()

Dim db As DAO.Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim CurrentID As String
Dim sSQL As String

Set db = CurrentDb()
Set rst1 = db.OpenRecordset("qryBirds", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qryBandnumCapture", dbOpenDynaset)

rst1.MoveFirst
rst2.MoveFirst
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[Bandnum]= '" & CurrentID & "'"
rst2.FindLast sSQL
If rst2!Capture = "R" Then
rst1.Edit
rst1!JustI = "no"
rst1.Update
ElseIf rst2!Capture = "I" Then
rst1.Edit
rst1!JustI = "yes"
rst1.Update
End If
rst1.MoveNext
Loop

rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing

End Sub

 

[Alex Dybenko]

Hi,
check query qryBandnumCapture sort order, perhaps FindLast get other record
then you expect. also try to debug your code to see that actually record
being found

 

[Guest]

Thanks Alex,

I had checked the sort order and it was correct with the" I" record followed
by the "R" record for the same ID. There are many other ID's records that
had two records like these, and these ID's were updated correctly. That is
why I suspect it is something in the code.

All the best,

LAF

Hi,
check query qryBandnumCapture sort order, perhaps FindLast get other record
then you expect. also try to debug your code to see that actually record
being found

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

The following code is run on two queries, in order to update a field on
the
master query (rst1). It is designed to take the first record in rst1 and
determine if the ID from rst1 is matched by a single record in rst2 (in
which
case the capture field will be equal to "I") or more than one record in
rst2
(in which case the capture field will be equal to "R"). The program runs
fine on all rcords in rst1 except for the first. The first two records in
rst2 have the same ID as rst1. The first has capture = "I" and the second
has capture = "R". Therefore, I am expecting the JUSTI field to be
updated
with a "no", but it is updated with a "yes". All other records appear to
be
updated correctly. Any ideas on which the first update is erroneous.

Thanks,

LAF


Option Compare Database
Option Explicit

Sub InitialAge()

Dim db As DAO.Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim CurrentID As String
Dim sSQL As String

Set db = CurrentDb()
Set rst1 = db.OpenRecordset("qryBirds", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qryBandnumCapture", dbOpenDynaset)

rst1.MoveFirst
rst2.MoveFirst
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[Bandnum]= '" & CurrentID & "'"
rst2.FindLast sSQL
If rst2!Capture = "R" Then
rst1.Edit
rst1!JustI = "no"
rst1.Update
ElseIf rst2!Capture = "I" Then
rst1.Edit
rst1!JustI = "yes"
rst1.Update
End If
rst1.MoveNext
Loop

rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing

End Sub

 

[David C. Holley]

The .MoveFirst satements are not neccessary since the recordsets start
there to begin with. Why are you using "rst2.FindLast sSQL"? Also, if
you're just counting records using DCount() will work find (and YES you
can do a DCount(), DLookup(), etc. on a query.) Finally, to answer the
question, if its just the first record that's wacking out it probably
has to do with the DATA in the records. If you look, you'll probably see
that there's only 1 record in rs1 that has multiple records in rs2. If
that is the case, when you alter the data so that multiple records in
rs1 have multiple records in rs2, you'll see that the problem exists for
them as well. (I'm in a bit of a teaching mood hence why I'm trying to
get you to see the problem yourself.) Play with that and if you don't
see where the problem lies, let me know. (Hint: I gave you a clue in the
first couple of lines as to where I think the problem is.) Gee, I didn't
reallize that I'm entirely in this teaching mood thing.

David H

PS you could also use the statement

rst1.Edit
Select case rs2!Capture
Case "R"
rst1!JustI = "NO
Case "
rst1!JustI = "YES"
End select
rst1.Update

 

[Tim Ferguson]

The following code is run on two queries, in order to update a field
on the master query (rst1). It is designed to take the first record
in rst1 and determine if the ID from rst1 is matched by a single
record in rst2 (in which case the capture field will be equal to "I")
or more than one record in rst2

SELECT q1.ID, COUNT(q2.*) AS NumberOfBands
FROM q1 LEFT JOIN q2 ON q1.ID = q2.ID
GROUP BY q1.ID
HAVING COUNT(q2.*) <> 1
ORDER BY q1.ID


Hope that helps



Tim F

 

[Guest]

Hi David, Tim, and Alex,

Thanks for being patient teachers. I think I discovered the problem.
Although the first ID in qryBird is sorted correctly in qryBandnumCapture, it
is not sorted correctly in the underlying table. All other cases of ID with
correctly sorted I and R in the table had the JUSTI field with the correct
value. Perhaps there is a lesson that can be generalized: sorting a query
that is then used in a program is not the same as sorting the underlying
table on which the query is based. It looks like the sort order in the
underlying table takes precedence.

All the best,

LAF

The .MoveFirst satements are not neccessary since the recordsets start
there to begin with. Why are you using "rst2.FindLast sSQL"? Also, if
you're just counting records using DCount() will work find (and YES you
can do a DCount(), DLookup(), etc. on a query.) Finally, to answer the
question, if its just the first record that's wacking out it probably
has to do with the DATA in the records. If you look, you'll probably see
that there's only 1 record in rs1 that has multiple records in rs2. If
that is the case, when you alter the data so that multiple records in
rs1 have multiple records in rs2, you'll see that the problem exists for
them as well. (I'm in a bit of a teaching mood hence why I'm trying to
get you to see the problem yourself.) Play with that and if you don't
see where the problem lies, let me know. (Hint: I gave you a clue in the
first couple of lines as to where I think the problem is.) Gee, I didn't
reallize that I'm entirely in this teaching mood thing.

David H

PS you could also use the statement

rst1.Edit
Select case rs2!Capture
Case "R"
rst1!JustI = "NO
Case "
rst1!JustI = "YES"
End select
rst1.Update

The following code is run on two queries, in order to update a field on the
master query (rst1). It is designed to take the first record in rst1 and
determine if the ID from rst1 is matched by a single record in rst2 (in which
case the capture field will be equal to "I") or more than one record in rst2
(in which case the capture field will be equal to "R"). The program runs
fine on all rcords in rst1 except for the first. The first two records in
rst2 have the same ID as rst1. The first has capture = "I" and the second
has capture = "R". Therefore, I am expecting the JUSTI field to be updated
with a "no", but it is updated with a "yes". All other records appear to be
updated correctly. Any ideas on which the first update is erroneous.

Thanks,

LAF


Option Compare Database
Option Explicit

Sub InitialAge()

Dim db As DAO.Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim CurrentID As String
Dim sSQL As String

Set db = CurrentDb()
Set rst1 = db.OpenRecordset("qryBirds", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qryBandnumCapture", dbOpenDynaset)

rst1.MoveFirst
rst2.MoveFirst
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[Bandnum]= '" & CurrentID & "'"
rst2.FindLast sSQL
If rst2!Capture = "R" Then
rst1.Edit
rst1!JustI = "no"
rst1.Update
ElseIf rst2!Capture = "I" Then
rst1.Edit
rst1!JustI = "yes"
rst1.Update
End If
rst1.MoveNext
Loop

rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing

End Sub

 

[David C. Holley]

I would still think about using DCount() or DLookUp() since I've very
cautious about relying on the sort order (and .FindFirst, .FindLast) to
grab records.

Hi David, Tim, and Alex,

Thanks for being patient teachers. I think I discovered the problem.
Although the first ID in qryBird is sorted correctly in qryBandnumCapture, it
is not sorted correctly in the underlying table. All other cases of ID with
correctly sorted I and R in the table had the JUSTI field with the correct
value. Perhaps there is a lesson that can be generalized: sorting a query
that is then used in a program is not the same as sorting the underlying
table on which the query is based. It looks like the sort order in the
underlying table takes precedence.

All the best,

LAF

:

The .MoveFirst satements are not neccessary since the recordsets start
there to begin with. Why are you using "rst2.FindLast sSQL"? Also, if
you're just counting records using DCount() will work find (and YES you
can do a DCount(), DLookup(), etc. on a query.) Finally, to answer the
question, if its just the first record that's wacking out it probably
has to do with the DATA in the records. If you look, you'll probably see
that there's only 1 record in rs1 that has multiple records in rs2. If
that is the case, when you alter the data so that multiple records in
rs1 have multiple records in rs2, you'll see that the problem exists for
them as well. (I'm in a bit of a teaching mood hence why I'm trying to
get you to see the problem yourself.) Play with that and if you don't
see where the problem lies, let me know. (Hint: I gave you a clue in the
first couple of lines as to where I think the problem is.) Gee, I didn't
reallize that I'm entirely in this teaching mood thing.

David H

PS you could also use the statement

rst1.Edit
Select case rs2!Capture
Case "R"
rst1!JustI = "NO
Case "
rst1!JustI = "YES"
End select
rst1.Update

The following code is run on two queries, in order to update a field on the
master query (rst1). It is designed to take the first record in rst1 and
determine if the ID from rst1 is matched by a single record in rst2 (in which
case the capture field will be equal to "I") or more than one record in rst2
(in which case the capture field will be equal to "R"). The program runs
fine on all rcords in rst1 except for the first. The first two records in
rst2 have the same ID as rst1. The first has capture = "I" and the second
has capture = "R". Therefore, I am expecting the JUSTI field to be updated
with a "no", but it is updated with a "yes". All other records appear to be
updated correctly. Any ideas on which the first update is erroneous.

Thanks,

LAF


Option Compare Database
Option Explicit

Sub InitialAge()

Dim db As DAO.Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim CurrentID As String
Dim sSQL As String

Set db = CurrentDb()
Set rst1 = db.OpenRecordset("qryBirds", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qryBandnumCapture", dbOpenDynaset)

rst1.MoveFirst
rst2.MoveFirst
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[Bandnum]= '" & CurrentID & "'"
rst2.FindLast sSQL
If rst2!Capture = "R" Then
rst1.Edit
rst1!JustI = "no"
rst1.Update
ElseIf rst2!Capture = "I" Then
rst1.Edit
rst1!JustI = "yes"
rst1.Update
End If
rst1.MoveNext
Loop

rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing

End Sub

 

[Tim Ferguson]

sorting a query
that is then used in a program is not the same as sorting the underlying
table on which the query is based. It looks like the sort order in the
underlying table takes precedence.

There is no sort order in a table. The only way to process records in some
desired order is to specify an ORDER BY clause.

B wishes


Tim F

 

[Guest]

Hi Alex, David, and Tim,

At last I have solved the problem, and hopefully the solution will assist
other access programmers. The problem is limited to the first ID. In the
underlying table the order of entry of the two records for the same
individual is R then I. If I sort the table before creating the queries, I
still get the error on the first record. If I sort the qryBandnumCapture
established from the table, I still get the error. However, if I use a make
table query from the underlying table, with sorting on capture, the first two
records in the new table are I then R, as desired. Then the code works fine.

So, the lesson learned, for me, is that the structure of the underlying
table is all important. Sorting a table or sorting a query appears to just
be for visual appearance. The underlying structure is what the program deals
with.

Is my interpretation correct?

Thanks,

LAF

 

[David C. Holley]

NEVER, NEVER, NEVER rely on the order of records in a table.

There are always solutions that can grab the same information without
relying on record order. Typically, these solutions will end up being
much more robust and require less long term maintenance. What happens if
you add a field to the underlying queries and select SORT DESCENDING,
forgetting that there are other queries that rely the query you
modified? Additionally, it should never be neccessary to use a
make-table when looking up data unless the queries to grab the data are
inordinately complex with multiple JOINS and subqueries and such.

Hi Alex, David, and Tim,

At last I have solved the problem, and hopefully the solution will assist
other access programmers. The problem is limited to the first ID. In the
underlying table the order of entry of the two records for the same
individual is R then I. If I sort the table before creating the queries, I
still get the error on the first record. If I sort the qryBandnumCapture
established from the table, I still get the error. However, if I use a make
table query from the underlying table, with sorting on capture, the first two
records in the new table are I then R, as desired. Then the code works fine.

So, the lesson learned, for me, is that the structure of the underlying
table is all important. Sorting a table or sorting a query appears to just
be for visual appearance. The underlying structure is what the program deals
with.

Is my interpretation correct?

Thanks,

LAF



:

The following code is run on two queries, in order to update a field on the
master query (rst1). It is designed to take the first record in rst1 and
determine if the ID from rst1 is matched by a single record in rst2 (in which
case the capture field will be equal to "I") or more than one record in rst2
(in which case the capture field will be equal to "R"). The program runs
fine on all rcords in rst1 except for the first. The first two records in
rst2 have the same ID as rst1. The first has capture = "I" and the second
has capture = "R". Therefore, I am expecting the JUSTI field to be updated
with a "no", but it is updated with a "yes". All other records appear to be
updated correctly. Any ideas on which the first update is erroneous.

Thanks,

LAF


Option Compare Database
Option Explicit

Sub InitialAge()

Dim db As DAO.Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim CurrentID As String
Dim sSQL As String

Set db = CurrentDb()
Set rst1 = db.OpenRecordset("qryBirds", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qryBandnumCapture", dbOpenDynaset)

rst1.MoveFirst
rst2.MoveFirst
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[Bandnum]= '" & CurrentID & "'"
rst2.FindLast sSQL
If rst2!Capture = "R" Then
rst1.Edit
rst1!JustI = "no"
rst1.Update
ElseIf rst2!Capture = "I" Then
rst1.Edit
rst1!JustI = "yes"
rst1.Update
End If
rst1.MoveNext
Loop

rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing

End Sub

 

[Tim Ferguson]

So, the lesson learned, for me, is that the structure of the
underlying table is all important. Sorting a table or sorting a query
appears to just be for visual appearance. The underlying structure is
what the program deals with.

Is my interpretation correct?

No: it's about 180 degrees off. The order in a query is the only thing you
can guarantee, and then only if you specify an ORDER BY clause. The order a
table is held on the disk is (to all intents and purposes) unknowable. If
you request a recordset based on a table without specifying a sort order
it's likely to be in PK order, but NOT necessarily -- it depends on things
like paging and clustering on disk over which you don't have control. If
your program relies on records being read in a specific order, then specify
an ORDER BY. Every time.

Hope that helps


Tim F

 

[David C. Holley]

And to reiterate, I would *NEVER* rely build any code that the does
record-processing that relys on those records being in *ANY* ordered -
sorted or not. There is *NEVER* a *NEED* to do so.