Recordset.Recordcount Returning To Many Records

[AtlasAF]

Hello,

In the code below, I am trying to get a record count from a DAO recordset. The problem comes in when I attempt to get a recordcount of the recordset. The query only grabs the records that are open (determined by a yes/no field in the table) but the recordcount returns a count for every record in the table. Can anyone tell me why this is happening?

Private Sub btnTTClose_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intPos As Integer
Dim msg As VbMsgBoxResult
Dim strSol As String
Dim dat As Date
Dim tim As Date
Dim strSQL As String

strSol = Forms!frmTTView!TTSolution.Value
strSQL = "SELECT tblAssignment.AEndDate, tblAssignment.AClosed " & _
            "FROM tblAssignment " & _
            "WHERE ((tblAssignment.AClosed)=No) AND ((tblAssignment.TTID)=" & [Forms]![frmTTView]![TTID] & ");"
dat = Date
tim = Time

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    If rs.RecordCount > 1 Then
        msg = MsgBox("There are more than one technician currently assigned to this ticket. " & _
                        "Do you still want to close this ticket and remove thier assignments?", vbYesNoCancel, "Close Ticket")
            If msg = vbNo Then
                Exit Sub
                ElseIf msg = vbYes Then
                    For intPos = 0 To rs.Fields.Count
                        With rs
                            .Edit
                            !AEndDate = dat & " " & tim
                            !AClosed = "Yes"
                            .Update
                            rs.MoveNext
                        End With
                    Next intPos
                [Forms]![frmTTView]![TTCloseDate].Value = Date
                DoCmd.Save
            End If
    ElseIf rs.RecordCount = 1 Then
            With rs
                .Edit
                !AEndDate = dat & " " & tim
                !AClosed = "Yes"
                .Update
            End With
            [Forms]![frmTTView]![TTCloseDate].Value = Date
            DoCmd.Save
    ElseIf rs.RecordCount = 0 Then
                [Forms]![frmTTView]![TTCloseDate].Value = Date
                DoCmd.Save
    End If
       
Set db = Nothing
Set rs = Nothing
intPos = 0
strSol = ""
strSQL = ""

End Sub

Thanks in advance

AtlasAF
USAF

 

[PHV]

For intPos = 0 To rs.Fields.Count
?????

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AtlasAF]

For intPos = 0 To rs.Fields.Count

is setting the number of times the loop needs to run (if there is a cleaner way to do it, please let me know)

AtlasAF

 

[SkipVought]

For intPos = 0 To rs.Fields.Count

intPost is looping ONE MORE than the number of fields in your recordset.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

You want to loop thru the fields or thru the records ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AtlasAF]

*Smacks forhead* The records, I forgot to change that when I added the additional field to the sql query. Before it was:

SELECT tblAssignmnet.AEndDate
From tblAssignment
Where ((tblAssignmnet.AEndDate) Is Null) and ((tblAssignment.TTID)="&[FORMS]![frmTTView]![TTID]&");"

Note that the problem is showing up earlier than this line though. The recordcount on the first If statement returns 3 records even though the dbOpenDynaset shows a total of 2 records. Could the .Fields.Count be causing this?

AtlasAF

 

[PHV]

Anyway, I'd change this:
For intPos = 0 To rs.Fields.Count
with this:
For intPos = 1 To rs.RecordCount

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AtlasAF]

I updated that line but the my problem is still there. For whatever reason, the recordcount is showing up for more that the query results. I should have a recordcount of 2 as the query only returns two records. Instead in comes up with a count of three.

AtlasAF

 

[AtlasAF]

I did some further trials and found out the following. When there is ony one record in the table the record count returns 1. If that record is marked as closed the recordcount returns 0. When add new records to this table, the recordcount matches the total records in the table reguardless if it is closed or not.

I should only have 1 record in the recordset as only one record is marked as open. I need this count to be right so the IF statement executes properly.

AtlasAF

 

[AtlasAF]

Maybe this will be helpful in figuring out my error: I removed all the if statements and added debug.print to see what the query is returning. Code modified to:

strSQL = "SELECT tblAssignment.AEndDate, tblAssignment.AClosed, tblAssignment.TTID, tblAssignment.AssignID " & _
            "FROM tblAssignment " & _
            "WHERE (((tblAssignment.AClosed)=No) AND ((tblAssignment.TTID)=" & [Forms]![frmTTView]![TTID] & "));"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

For intPos = 0 To rs.RecordCount - 1
    Debug.Print rs!AClosed & " " & rs!AssignID & " " & rs!TTID
    rs.MoveNext
Next intPos

The results of the debug.print were:

False 6 100219001
False 7 100219001
False 8 100219001


The Debug.print errored out though becuase the recordcount was 5 but the recordset only contained three records. Anyone know what is going on?

AtlasAF

 

[AtlasAF]

I figured out how to execute this code successfully. Not sure why, but it works. When I added in the DISTINCT line to the SQL code, the recordcount returned the correct number, however using the DISTINCT call I obviously couldn't edit the table. To combat this, I added another SQL string with the DISTINCT statement to get me an accurate recordcount and used the original to update the table. See below for updated code.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsD As DAO.Recordset
Dim intPos As Integer
Dim msg As VbMsgBoxResult
Dim strSol As String
Dim dat As Date
Dim tim As Date
Dim strSQL As String
Dim strDSQL As String

'strSol = Forms!frmTTView!TTSolution.Value
strSQL = "SELECT tblAssignment.AEndDate, tblAssignment.AClosed, tblAssignment.TTID, tblAssignment.AssignID " & _
            "FROM tblAssignment " & _
            "WHERE (((tblAssignment.AClosed)=No) AND ((tblAssignment.TTID)=" & [Forms]![frmTTView]![TTID] & "));"
strDSQL = "SELECT DISTINCT tblAssignment.AEndDate, tblAssignment.AClosed, tblAssignment.TTID, tblAssignment.AssignID " & _
            "FROM tblAssignment " & _
            "WHERE (((tblAssignment.AClosed)=No) AND ((tblAssignment.TTID)=" & [Forms]![frmTTView]![TTID] & "));"

dat = Date
tim = Time

Set db = CurrentDb
Set rsD = db.OpenRecordset(strDSQL, dbOpenDynaset)
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

For intPos = 0 To rsD.RecordCount - 1
    Debug.Print rs!AClosed & " " & rs!AssignID & " " & rs!TTID
    rs.MoveNext
Next intPos

    If rsD.RecordCount > 1 Then
        msg = MsgBox("There are more than one technician currently assigned to this ticket. " & _
                        "Do you still want to close this ticket and remove thier assignments?", vbYesNoCancel, "Close Ticket")
            If msg = vbNo Then
                Exit Sub
                ElseIf msg = vbYes Then
                    rs.MoveFirst
                    For intPos = 0 To rs.RecordCount - 1
                        With rs
                            .Edit
                            !AEndDate = dat & " " & tim
                            !AClosed = True
                            .Update
                            rs.MoveNext
                        End With
                    Next intPos
                [Forms]![frmTTView]![TTCloseDate].Value = Date
                DoCmd.Save
            End If
    ElseIf rsD.RecordCount = 1 Then
            rs.MoveFirst
            With rs
                .Edit
                !AEndDate = dat & " " & tim
                !AClosed = True
                .Update
            End With
            [Forms]![frmTTView]![TTCloseDate].Value = Date
            DoCmd.Save
    ElseIf rsD.RecordCount = 0 Then
                [Forms]![frmTTView]![TTCloseDate].Value = Date
                DoCmd.Save
    End If
       
Set db = Nothing
Set rs = Nothing
Set rsD = Nothing
intPos = 0
strSol = ""
strSQL = ""
strDSQL = ""

AtlasAF