RecordCount Issues

D

DoveArrow

I ran a series of update queries to update a table called
"PrimaryMajors." Now that the table is updated, I need to add
secondary degree notes to each non Associates Degree, but only if
there is more than one non Associates Degree.

To do this, I wrote the following sequence

'Add any 'Secondary Degree' notes from "tblCampusCatalogYearNote" to
"Notes."
Set rsMajors = db.OpenRecordset("SELECT * FROM PrimaryMajors WHERE
AssociatesDegree=False") 'Create recordset for all non Associates
Degrees.

If rsMajors.RecordCount > 1 Then 'If more than one non Associates
Degree
strSQL = "INSERT INTO Notes ( DegreeType, NoteTypeID, [Note] )
SELECT DISTINCT PrimaryMajors.DegreeType,
tblCampusCatalogYearNotes.NoteTypeID, tblCampusCatalogYearNotes.Note
FROM tblCampusCatalogYearNotes, PrimaryMajors WHERE
(((tblCampusCatalogYearNotes.NoteTypeID)=2) AND
((PrimaryMajors.AssociatesDegree)=False) AND
((tblCampusCatalogYearNotes.Campus)=[Forms]![frmProgramChange]!
[Campus]) AND ((tblCampusCatalogYearNotes.CatalogYear)=[Forms]!
[frmProgramChange]![CatalogYear]));"
Set qdf = db.QueryDefs("qupdTable")
qdf.SQL = strSQL
DoCmd.OpenQuery "qupdTable"
End If

The problem I'm running into is that the computer, for some reason,
thinks that there is only one non Associates Degree in
"PrimaryMajors." However, if I add the following sequence to figure
out which degree it's pulling, it not only displays both programs, but
the sequence above works fine.

Do Until rsMajors.EOF
MsgBox rsMajors!Program 'This is the field where the degree program
code is stored.
rsMajors.MoveNext
Loop

My question is... WHAT THE HELL IS GOING ON?
 
D

Dirk Goldgar

DoveArrow said:
I ran a series of update queries to update a table called
"PrimaryMajors." Now that the table is updated, I need to add
secondary degree notes to each non Associates Degree, but only if
there is more than one non Associates Degree.

To do this, I wrote the following sequence

'Add any 'Secondary Degree' notes from "tblCampusCatalogYearNote" to
"Notes."
Set rsMajors = db.OpenRecordset("SELECT * FROM PrimaryMajors WHERE
AssociatesDegree=False") 'Create recordset for all non Associates
Degrees.

If rsMajors.RecordCount > 1 Then 'If more than one non Associates
Degree
strSQL = "INSERT INTO Notes ( DegreeType, NoteTypeID, [Note] )
SELECT DISTINCT PrimaryMajors.DegreeType,
tblCampusCatalogYearNotes.NoteTypeID, tblCampusCatalogYearNotes.Note
FROM tblCampusCatalogYearNotes, PrimaryMajors WHERE
(((tblCampusCatalogYearNotes.NoteTypeID)=2) AND
((PrimaryMajors.AssociatesDegree)=False) AND
((tblCampusCatalogYearNotes.Campus)=[Forms]![frmProgramChange]!
[Campus]) AND ((tblCampusCatalogYearNotes.CatalogYear)=[Forms]!
[frmProgramChange]![CatalogYear]));"
Set qdf = db.QueryDefs("qupdTable")
qdf.SQL = strSQL
DoCmd.OpenQuery "qupdTable"
End If

The problem I'm running into is that the computer, for some reason,
thinks that there is only one non Associates Degree in
"PrimaryMajors." However, if I add the following sequence to figure
out which degree it's pulling, it not only displays both programs, but
the sequence above works fine.

Do Until rsMajors.EOF
MsgBox rsMajors!Program 'This is the field where the degree program
code is stored.
rsMajors.MoveNext
Loop

My question is... WHAT THE [<heck>] IS GOING ON?
Click to expand...


In a dynaset-type recordset (which is what you get by default when you open
a recordset on a query, not a local table), the RecordCount property is not
accurate until all records in the recordset have been visited. Essentially,
it reflects the number of records visited so far.

If all you need to know is whether there are more than one non-Associates
degree, you can run a counting query:

Set rsMajors = db.OpenRecordset( _
"SELECT Count(*) As N FROM PrimaryMajors " & _
"WHERE AssociatesDegree=False")

If rsMajors!N > 1 Then
' ...
End If

Or you could just use the DCount() function:

If DCount("*", "PrimaryMajors", "AssociatesDegree=False") > 1 Then
' ...
End If

If, on the other hand, you plan to actually process the records in the
recordset, but first you need to know how many there are, you can go to the
end of the recordset and back to ensure that the RecordCount property is
accurate:

Set rsMajors = db.OpenRecordset( _
"SELECT * FROM PrimaryMajors " & _
"WHERE AssociatesDegree=False")

With rsMajors
' Ensure accurate record count.
If Not .EOF Then
.MoveLast
.MoveFirst
End If
' Process records if more than one.
If .RecordCount > 1 Then

' ... do stuff with the records ...

End If
' Close the recordset.
.Close
End With
 
D

DoveArrow

I ran a series of update queries to update a table called
"PrimaryMajors." Now that the table is updated, I need to add
secondary degree notes to each non Associates Degree, but only if
there is more than one non Associates Degree.
Click to expand...
To do this, I wrote the following sequence
Click to expand...
'Add any 'Secondary Degree' notes from "tblCampusCatalogYearNote" to
"Notes."
Set rsMajors = db.OpenRecordset("SELECT * FROM PrimaryMajors WHERE
AssociatesDegree=False") 'Create recordset for all non Associates
Degrees.
Click to expand...
If rsMajors.RecordCount > 1 Then 'If more than one non Associates
Degree
   strSQL = "INSERT INTO Notes ( DegreeType, NoteTypeID, [Note] )
SELECT DISTINCT PrimaryMajors.DegreeType,
tblCampusCatalogYearNotes.NoteTypeID, tblCampusCatalogYearNotes.Note
FROM tblCampusCatalogYearNotes, PrimaryMajors WHERE
(((tblCampusCatalogYearNotes.NoteTypeID)=2) AND
((PrimaryMajors.AssociatesDegree)=False) AND
((tblCampusCatalogYearNotes.Campus)=[Forms]![frmProgramChange]!
[Campus]) AND ((tblCampusCatalogYearNotes.CatalogYear)=[Forms]!
[frmProgramChange]![CatalogYear]));"
   Set qdf = db.QueryDefs("qupdTable")
   qdf.SQL = strSQL
   DoCmd.OpenQuery "qupdTable"
End If
Click to expand...
The problem I'm running into is that the computer, for some reason,
thinks that there is only one non Associates Degree in
"PrimaryMajors." However, if I add the following sequence to figure
out which degree it's pulling, it not only displays both programs, but
the sequence above works fine.
Click to expand...
Do Until rsMajors.EOF
MsgBox rsMajors!Program 'This is the field where the degree program
code is stored.
rsMajors.MoveNext
Loop
Click to expand...
My question is... WHAT THE [<heck>] IS GOING ON?
Click to expand...

In a dynaset-type recordset (which is what you get by default when you open
a recordset on a query, not a local table), the RecordCount property is not
accurate until all records in the recordset have been visited.  Essentially,
it reflects the number of records visited so far.

If all you need to know is whether there are more than one non-Associates
degree, you can run a counting query:

    Set rsMajors = db.OpenRecordset( _
        "SELECT Count(*) As N FROM PrimaryMajors " & _
        "WHERE AssociatesDegree=False")

    If rsMajors!N > 1 Then
        ' ...
    End If

Or you could just use the DCount() function:

    If DCount("*", "PrimaryMajors", "AssociatesDegree=False") > 1 Then
        ' ...
    End If

If, on the other hand, you plan to actually process the records in the
recordset, but first you need to know how many there are, you can go to the
end of the recordset and back to ensure that the RecordCount property is
accurate:

    Set rsMajors = db.OpenRecordset( _
        "SELECT * FROM PrimaryMajors " & _
            "WHERE AssociatesDegree=False")

    With rsMajors
        ' Ensure accurate record count.
        If Not .EOF Then
            .MoveLast
            .MoveFirst
        End If
        ' Process records if more than one.
        If .RecordCount > 1 Then

            ' ... do stuff with the records ...

        End If
        ' Close the recordset.
        .Close
    End With

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...

I tried your DCount solution. Now it works like a charm. Thank you
very much.
 
Top