G
Guest
I have a couple of issues.
The first is I need to know the record count of a record set. I looked in
the help and found this
"RecordCount Property
Returns the number of records accessed in a Recordset object"
It does not seem to return the number of records, but rather the current
positon.
I can get around that by using rs.movelast then saving the count then moving
back to the first record.
My next issue is a little more confusing...
Code below, I snipped some of it to condense. Basically I have a do while
not rs.eof, at the end of the loop I have rs.movenext. Inside the loop I am
opening another record set of the same table (different object) and am
editing in the second table if necessary. When the loop gets to the bottom
it is on the last record after the first iteration which tells me that the
second recordset opened is somehow manipulating the current record in the
first record set, but the kicker is that the rs.movenext won't even put it on
the eof it stays on the last record when it loops back to the top. I've
tried using a variable to save the current record at the top of the loop and
going back to it at the bottom, but I'm not sure how. I tried to figure out
how to use a bookmark but I'm not sure if it's just not working or I don't
know how to use it. I even tried a rs.movefirst at the bottom of the loop to
see if I could get the record pointer to budge, but it won't.
What's wrong with my code?
Dim rs as dao.recordset
Dim rsW as dao.recordset
<snip>
Set rs = CurrentDb.OpenRecordset("Select * from [WorkSpaceSchedule]")
Dim intRecCount As Integer
Do While Not rs.EOF
If rs![ss conflict] = False Then
lngEmpID = rs![SS-FK Emp ID]
lngClientID = rs![SS-FK Client ID]
strDay = rs![SS Day]
'Check 6:00-6:14 AM block
strSQLw = "Select * from [WorkSpaceSchedule] "
strSQLw = strSQLw & "where [SS-FK Emp ID] = " & lngEmpID
strSQLw = strSQLw & " and [SS-FK Client ID] = " & lngClientID
strSQLw = strSQLw & " and [SS Day] = '" & strDay & "'"
strSQLw = strSQLw & " and [SS 6AM1] = true"
Set rsW = CurrentDb.OpenRecordset(strSQLw)
If Not rsW.EOF Then
rsW.MoveLast
intRecCount = rsW.RecordCount
If intRecCount > 1 Then
'there are conflicts
rsW.MoveFirst
Do While Not rsW.EOF
'mark each record that has a conflict
With rsW
.Edit
![ss conflict] = True
.Update
End With
rsW.MoveNext
Loop
End If
End If
rsW.Close
Set rsW = Nothing
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
<snip>
The first is I need to know the record count of a record set. I looked in
the help and found this
"RecordCount Property
Returns the number of records accessed in a Recordset object"
It does not seem to return the number of records, but rather the current
positon.
I can get around that by using rs.movelast then saving the count then moving
back to the first record.
My next issue is a little more confusing...
Code below, I snipped some of it to condense. Basically I have a do while
not rs.eof, at the end of the loop I have rs.movenext. Inside the loop I am
opening another record set of the same table (different object) and am
editing in the second table if necessary. When the loop gets to the bottom
it is on the last record after the first iteration which tells me that the
second recordset opened is somehow manipulating the current record in the
first record set, but the kicker is that the rs.movenext won't even put it on
the eof it stays on the last record when it loops back to the top. I've
tried using a variable to save the current record at the top of the loop and
going back to it at the bottom, but I'm not sure how. I tried to figure out
how to use a bookmark but I'm not sure if it's just not working or I don't
know how to use it. I even tried a rs.movefirst at the bottom of the loop to
see if I could get the record pointer to budge, but it won't.
What's wrong with my code?
Dim rs as dao.recordset
Dim rsW as dao.recordset
<snip>
Set rs = CurrentDb.OpenRecordset("Select * from [WorkSpaceSchedule]")
Dim intRecCount As Integer
Do While Not rs.EOF
If rs![ss conflict] = False Then
lngEmpID = rs![SS-FK Emp ID]
lngClientID = rs![SS-FK Client ID]
strDay = rs![SS Day]
'Check 6:00-6:14 AM block
strSQLw = "Select * from [WorkSpaceSchedule] "
strSQLw = strSQLw & "where [SS-FK Emp ID] = " & lngEmpID
strSQLw = strSQLw & " and [SS-FK Client ID] = " & lngClientID
strSQLw = strSQLw & " and [SS Day] = '" & strDay & "'"
strSQLw = strSQLw & " and [SS 6AM1] = true"
Set rsW = CurrentDb.OpenRecordset(strSQLw)
If Not rsW.EOF Then
rsW.MoveLast
intRecCount = rsW.RecordCount
If intRecCount > 1 Then
'there are conflicts
rsW.MoveFirst
Do While Not rsW.EOF
'mark each record that has a conflict
With rsW
.Edit
![ss conflict] = True
.Update
End With
rsW.MoveNext
Loop
End If
End If
rsW.Close
Set rsW = Nothing
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
<snip>