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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|