A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

RecordCount Issues



 
 
Thread Tools Display Modes
  #1  
Old April 1st, 2010, 08:22 PM posted to microsoft.public.access.forms
DoveArrow
external usenet poster
 
Posts: 90
Default RecordCount Issues

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?
  #2  
Old April 1st, 2010, 08:56 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default RecordCount Issues

"DoveArrow" wrote in message
...
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?



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)

  #3  
Old April 1st, 2010, 09:50 PM posted to microsoft.public.access.forms
DoveArrow
external usenet poster
 
Posts: 90
Default RecordCount Issues

On Apr 1, 12:56*pm, "Dirk Goldgar"
wrote:
"DoveArrow" wrote in message

...



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?


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)


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




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.