why might movenext not work?

T

tw

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. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the bottom of
the loop, just prior to .movenext it is still on the first record as it
should be. When it executes the .movenext statement it jumps to the last
record, when it loops the second time around the loop and gets to the
..movenext statement it is still on the last record. .movenext doesn't even
move it to eof at this point and I'm now in an infinite loop on the last
record. (I have compacted/repaired just in case it was a problem there... no
luck)

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>
 
M

mscertified

Recordcount does not always work, it depends on the type of cursor and record
locking you are using. Its all documented, google it. Movelast could be very
inefficient since yoiu may have to read every record in your recordset to get
to the end - again that depends on the type of recordset.

As to your second question, I'm not sure what the problem is but I would not
recommend doing what you are doing, i.e. reading the same table twice in
different recordsets.

Dorian

tw said:
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. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the bottom of
the loop, just prior to .movenext it is still on the first record as it
should be. When it executes the .movenext statement it jumps to the last
record, when it loops the second time around the loop and gets to the
.movenext statement it is still on the last record. .movenext doesn't even
move it to eof at this point and I'm now in an infinite loop on the last
record. (I have compacted/repaired just in case it was a problem there... no
luck)

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>
Click to expand...
 
T

tw

What would you recomment?

mscertified said:
Recordcount does not always work, it depends on the type of cursor and record
locking you are using. Its all documented, google it. Movelast could be very
inefficient since yoiu may have to read every record in your recordset to get
to the end - again that depends on the type of recordset.

As to your second question, I'm not sure what the problem is but I would not
recommend doing what you are doing, i.e. reading the same table twice in
different recordsets.

Dorian

tw said:
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. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the bottom of
the loop, just prior to .movenext it is still on the first record as it
should be. When it executes the .movenext statement it jumps to the last
record, when it loops the second time around the loop and gets to the
.movenext statement it is still on the last record. .movenext doesn't even
move it to eof at this point and I'm now in an infinite loop on the last
record. (I have compacted/repaired just in case it was a problem there... no
luck)

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>
Click to expand...
Click to expand...
 
T

tw

movelast isn't an issue with small number of records. There are only about
400. My main concern is the second problem.

I'm looping through the data. On each record that does not already have
"conflict" = true I want to see if there is a conflict. I open the table in
another recordset for some criteria that matches the original record to see
if there is more than on record in the data set matching that criteria. If
there is I want to edit the conflict field and mark it true. How should I
go about doing that if you don't recommend what I am doing?

mscertified said:
Recordcount does not always work, it depends on the type of cursor and record
locking you are using. Its all documented, google it. Movelast could be very
inefficient since yoiu may have to read every record in your recordset to get
to the end - again that depends on the type of recordset.

As to your second question, I'm not sure what the problem is but I would not
recommend doing what you are doing, i.e. reading the same table twice in
different recordsets.

Dorian

tw said:
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. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the bottom of
the loop, just prior to .movenext it is still on the first record as it
should be. When it executes the .movenext statement it jumps to the last
record, when it loops the second time around the loop and gets to the
.movenext statement it is still on the last record. .movenext doesn't even
move it to eof at this point and I'm now in an infinite loop on the last
record. (I have compacted/repaired just in case it was a problem there... no
luck)

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>
Click to expand...
Click to expand...
 
A

Alex White MCDBA MCSE

if you use the adOpenKeyset cursor option on an ado recordset

msgbox .recordcount

would give you the correct recordcount without having to go to the end of
the recordset

e.g.

dim adoTest as new adodb.recordset
with adoTest
.open "Select * from
TblTest",currentproject.connection,adOpenKeyset,adLockOptimistic
msgbox .recordcount
.close
end with

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

tw said:
movelast isn't an issue with small number of records. There are only
about
400. My main concern is the second problem.

I'm looping through the data. On each record that does not already have
"conflict" = true I want to see if there is a conflict. I open the table
in
another recordset for some criteria that matches the original record to
see
if there is more than on record in the data set matching that criteria.
If
there is I want to edit the conflict field and mark it true. How should
I
go about doing that if you don't recommend what I am doing?

mscertified said:
Recordcount does not always work, it depends on the type of cursor and
record
locking you are using. Its all documented, google it. Movelast could be
very
inefficient since yoiu may have to read every record in your recordset to
get
to the end - again that depends on the type of recordset.

As to your second question, I'm not sure what the problem is but I would
not
recommend doing what you are doing, i.e. reading the same table twice in
different recordsets.

Dorian

tw said:
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. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the
bottom of
the loop, just prior to .movenext it is still on the first record as it
should be. When it executes the .movenext statement it jumps to the
last
record, when it loops the second time around the loop and gets to the
.movenext statement it is still on the last record. .movenext doesn't
even
move it to eof at this point and I'm now in an infinite loop on the
last
record. (I have compacted/repaired just in case it was a problem
there... no
luck)

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>
Click to expand...
Click to expand...
Click to expand...
 
T

tw

Thanks, do you have any suggestions for the second issue and my main problem?

Alex White MCDBA MCSE said:
if you use the adOpenKeyset cursor option on an ado recordset

msgbox .recordcount

would give you the correct recordcount without having to go to the end of
the recordset

e.g.

dim adoTest as new adodb.recordset
with adoTest
.open "Select * from
TblTest",currentproject.connection,adOpenKeyset,adLockOptimistic
msgbox .recordcount
.close
end with

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

tw said:
movelast isn't an issue with small number of records. There are only
about
400. My main concern is the second problem.

I'm looping through the data. On each record that does not already have
"conflict" = true I want to see if there is a conflict. I open the table
in
another recordset for some criteria that matches the original record to
see
if there is more than on record in the data set matching that criteria.
If
there is I want to edit the conflict field and mark it true. How should
I
go about doing that if you don't recommend what I am doing?

mscertified said:
Recordcount does not always work, it depends on the type of cursor and
record
locking you are using. Its all documented, google it. Movelast could be
very
inefficient since yoiu may have to read every record in your recordset to
get
to the end - again that depends on the type of recordset.

As to your second question, I'm not sure what the problem is but I would
not
recommend doing what you are doing, i.e. reading the same table twice in
different recordsets.

Dorian

:

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. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the
bottom of
the loop, just prior to .movenext it is still on the first record as it
should be. When it executes the .movenext statement it jumps to the
last
record, when it loops the second time around the loop and gets to the
.movenext statement it is still on the last record. .movenext doesn't
even
move it to eof at this point and I'm now in an infinite loop on the
last
record. (I have compacted/repaired just in case it was a problem
there... no
luck)

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>
Click to expand...
Click to expand...
Click to expand...
 
M

MacDermott

For your second problem, here's what I'd do:

Use the wizard to build a FindDuplicates query, matching the fields that are
of concern to you.
The build an update query based on the first one, setting all of the
"conflict" fields to True.
The only code you'd need to write would be to run the second query.

HTH
 
T

tw

Cool idea, but the query window is limited to the number of characters that
it can use, and the extent of what I'm matching on... I'm afraid I will
either exceed the limit or have to write several duplicate queries and then
union them all together before the second query. Do you have any ideas about
that? My criteria is as follows...

if one employee on one day has any of 56 15minute time slots duplicated with
more than one client, than there is a conflict. I removed the cliendid in
the code below. It was incorrect and shouldn't be there, but you can see the
jist of what I'm trying to accomplish below. I'm going to see if I can work
this out with the queries you suggested. I'll post back if I have problems.

MacDermott said:
For your second problem, here's what I'd do:

Use the wizard to build a FindDuplicates query, matching the fields that are
of concern to you.
The build an update query based on the first one, setting all of the
"conflict" fields to True.
The only code you'd need to write would be to run the second query.

HTH

tw said:
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. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the bottom of
the loop, just prior to .movenext it is still on the first record as it
should be. When it executes the .movenext statement it jumps to the last
record, when it loops the second time around the loop and gets to the
.movenext statement it is still on the last record. .movenext doesn't even
move it to eof at this point and I'm now in an infinite loop on the last
record. (I have compacted/repaired just in case it was a problem there... no
luck)

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>
Click to expand...
Click to expand...
 
M

MacDermott

I'm assuming you have a table with these fields, and probably more:
EmployeeID
Date
TimeSlotID

You could prevent problems in the first place by placing a unique index on
these 3 fields (one index, containing all 3 fields). This would prevent
anybody from booking an employee for a timeslot for which he's already
booked.

But for cleaning up existing data, you can use a single duplicates query,
where you define that all 3 of these fields should match. You'd then want
to show at least the ClientID in the query as well.

HTH

tw said:
Cool idea, but the query window is limited to the number of characters that
it can use, and the extent of what I'm matching on... I'm afraid I will
either exceed the limit or have to write several duplicate queries and then
union them all together before the second query. Do you have any ideas about
that? My criteria is as follows...

if one employee on one day has any of 56 15minute time slots duplicated with
more than one client, than there is a conflict. I removed the cliendid in
the code below. It was incorrect and shouldn't be there, but you can see the
jist of what I'm trying to accomplish below. I'm going to see if I can work
this out with the queries you suggested. I'll post back if I have problems.

MacDermott said:
For your second problem, here's what I'd do:

Use the wizard to build a FindDuplicates query, matching the fields that are
of concern to you.
The build an update query based on the first one, setting all of the
"conflict" fields to True.
The only code you'd need to write would be to run the second query.

HTH

tw said:
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
Click to expand...
loop I
am
opening another record set of the same table (different object) and am
editing in the second table if necessary. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the
Click to expand...
bottom
of
the loop, just prior to .movenext it is still on the first record as it
should be. When it executes the .movenext statement it jumps to the last
record, when it loops the second time around the loop and gets to the
.movenext statement it is still on the last record. .movenext doesn't even
move it to eof at this point and I'm now in an infinite loop on the last
record. (I have compacted/repaired just in case it was a problem
Click to expand...
there...
no
luck)

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>
Click to expand...
Click to expand...
Click to expand...
 
T

tw

Actually in this case there are sometimes when a conflict is acceptable.
They just want to know about it. So I'm flagging conflicts instead of
preventing them. My workspace table is just used for the report and filled
during the running of the report. It is not normalized, the tables used for
data entry are, and I do have thoses fields. The problem was with the tables
normalized, I could not create layout of the report that the users require.
So I created this table and am filling it in on the fly from the normalized
data. I had to make several duplicate queries. I unioned them all together,
but the update query won't work because the query is not updateable. Do I
have to update each duplicate query because the union query is not updateable
or is it the duplicate queries that are not updateable because they use group
in the query created in the criteria section?

MacDermott said:
I'm assuming you have a table with these fields, and probably more:
EmployeeID
Date
TimeSlotID

You could prevent problems in the first place by placing a unique index on
these 3 fields (one index, containing all 3 fields). This would prevent
anybody from booking an employee for a timeslot for which he's already
booked.

But for cleaning up existing data, you can use a single duplicates query,
where you define that all 3 of these fields should match. You'd then want
to show at least the ClientID in the query as well.

HTH

tw said:
Cool idea, but the query window is limited to the number of characters that
it can use, and the extent of what I'm matching on... I'm afraid I will
either exceed the limit or have to write several duplicate queries and then
union them all together before the second query. Do you have any ideas about
that? My criteria is as follows...

if one employee on one day has any of 56 15minute time slots duplicated with
more than one client, than there is a conflict. I removed the cliendid in
the code below. It was incorrect and shouldn't be there, but you can see the
jist of what I'm trying to accomplish below. I'm going to see if I can work
this out with the queries you suggested. I'll post back if I have problems.

MacDermott said:
For your second problem, here's what I'd do:

Use the wizard to build a FindDuplicates query, matching the fields that are
of concern to you.
The build an update query based on the first one, setting all of the
"conflict" fields to True.
The only code you'd need to write would be to run the second query.

HTH

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. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the bottom
of
the loop, just prior to .movenext it is still on the first record as it
should be. When it executes the .movenext statement it jumps to the last
record, when it loops the second time around the loop and gets to the
.movenext statement it is still on the last record. .movenext doesn't even
move it to eof at this point and I'm now in an infinite loop on the last
record. (I have compacted/repaired just in case it was a problem there...
no
luck)

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>
Click to expand...
Click to expand...
Click to expand...
 
M

MacDermott

Using Group in a query will make it non-updatable.

tw said:
Actually in this case there are sometimes when a conflict is acceptable.
They just want to know about it. So I'm flagging conflicts instead of
preventing them. My workspace table is just used for the report and filled
during the running of the report. It is not normalized, the tables used for
data entry are, and I do have thoses fields. The problem was with the tables
normalized, I could not create layout of the report that the users require.
So I created this table and am filling it in on the fly from the normalized
data. I had to make several duplicate queries. I unioned them all together,
but the update query won't work because the query is not updateable. Do I
have to update each duplicate query because the union query is not updateable
or is it the duplicate queries that are not updateable because they use group
in the query created in the criteria section?

MacDermott said:
I'm assuming you have a table with these fields, and probably more:
EmployeeID
Date
TimeSlotID

You could prevent problems in the first place by placing a unique index on
these 3 fields (one index, containing all 3 fields). This would prevent
anybody from booking an employee for a timeslot for which he's already
booked.

But for cleaning up existing data, you can use a single duplicates query,
where you define that all 3 of these fields should match. You'd then want
to show at least the ClientID in the query as well.

HTH

tw said:
Cool idea, but the query window is limited to the number of characters that
it can use, and the extent of what I'm matching on... I'm afraid I will
either exceed the limit or have to write several duplicate queries and then
union them all together before the second query. Do you have any
Click to expand...
ideas
about
that? My criteria is as follows...

if one employee on one day has any of 56 15minute time slots
Click to expand...
duplicated
with
more than one client, than there is a conflict. I removed the cliendid in
the code below. It was incorrect and shouldn't be there, but you can
Click to expand...
see
the
jist of what I'm trying to accomplish below. I'm going to see if I
Click to expand...
can
work
this out with the queries you suggested. I'll post back if I have problems.

:

For your second problem, here's what I'd do:

Use the wizard to build a FindDuplicates query, matching the fields
Click to expand...
that
are
of concern to you.
The build an update query based on the first one, setting all of the
"conflict" fields to True.
The only code you'd need to write would be to run the second query.

HTH

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
Click to expand...
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. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the bottom
of
the loop, just prior to .movenext it is still on the first record
Click to expand...
as
it
should be. When it executes the .movenext statement it jumps to
Click to expand...
the
last
record, when it loops the second time around the loop and gets to the
.movenext statement it is still on the last record. .movenext
Click to expand...
doesn't
even
move it to eof at this point and I'm now in an infinite loop on
Click to expand...
the
last
record. (I have compacted/repaired just in case it was a problem there...
no
luck)

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>
Click to expand...
Click to expand...
Click to expand...
 
T

tw

Then I don't understand what you suggested below. It is the FindDuplicates
query that is using group by

For your second problem, here's what I'd do:

Use the wizard to build a FindDuplicates query, matching the fields that are
of concern to you.
The build an update query based on the first one, setting all of the
"conflict" fields to True.
The only code you'd need to write would be to run the second query.

HTH
It is the FindDuplicates query that is using the group by.


MacDermott said:
Using Group in a query will make it non-updatable.

tw said:
Actually in this case there are sometimes when a conflict is acceptable.
They just want to know about it. So I'm flagging conflicts instead of
preventing them. My workspace table is just used for the report and filled
during the running of the report. It is not normalized, the tables used for
data entry are, and I do have thoses fields. The problem was with the tables
normalized, I could not create layout of the report that the users require.
So I created this table and am filling it in on the fly from the normalized
data. I had to make several duplicate queries. I unioned them all together,
but the update query won't work because the query is not updateable. Do I
have to update each duplicate query because the union query is not updateable
or is it the duplicate queries that are not updateable because they use group
in the query created in the criteria section?

MacDermott said:
I'm assuming you have a table with these fields, and probably more:
EmployeeID
Date
TimeSlotID

You could prevent problems in the first place by placing a unique index on
these 3 fields (one index, containing all 3 fields). This would prevent
anybody from booking an employee for a timeslot for which he's already
booked.

But for cleaning up existing data, you can use a single duplicates query,
where you define that all 3 of these fields should match. You'd then want
to show at least the ClientID in the query as well.

HTH

Cool idea, but the query window is limited to the number of characters
that
it can use, and the extent of what I'm matching on... I'm afraid I will
either exceed the limit or have to write several duplicate queries and
then
union them all together before the second query. Do you have any ideas
about
that? My criteria is as follows...

if one employee on one day has any of 56 15minute time slots duplicated
with
more than one client, than there is a conflict. I removed the cliendid in
the code below. It was incorrect and shouldn't be there, but you can see
the
jist of what I'm trying to accomplish below. I'm going to see if I can
work
this out with the queries you suggested. I'll post back if I have
problems.

:

For your second problem, here's what I'd do:

Use the wizard to build a FindDuplicates query, matching the fields that
are
of concern to you.
The build an update query based on the first one, setting all of the
"conflict" fields to True.
The only code you'd need to write would be to run the second query.

HTH

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. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the
bottom
of
the loop, just prior to .movenext it is still on the first record as
it
should be. When it executes the .movenext statement it jumps to the
last
record, when it loops the second time around the loop and gets to the
.movenext statement it is still on the last record. .movenext doesn't
even
move it to eof at this point and I'm now in an infinite loop on the
last
record. (I have compacted/repaired just in case it was a problem
there...
no
luck)

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>
Click to expand...
Click to expand...
Click to expand...
 
T

tw

I got it. It was the union query that was making this not updatable. I
changed to run the update query on each of the FindDuplicate queries I
created and now it does what I want. Thanks for all the help.

tw said:
Then I don't understand what you suggested below. It is the FindDuplicates
query that is using group by

For your second problem, here's what I'd do:

Use the wizard to build a FindDuplicates query, matching the fields that are
of concern to you.
The build an update query based on the first one, setting all of the
"conflict" fields to True.
The only code you'd need to write would be to run the second query.

HTH
It is the FindDuplicates query that is using the group by.


MacDermott said:
Using Group in a query will make it non-updatable.

tw said:
Actually in this case there are sometimes when a conflict is acceptable.
They just want to know about it. So I'm flagging conflicts instead of
preventing them. My workspace table is just used for the report and filled
during the running of the report. It is not normalized, the tables used for
data entry are, and I do have thoses fields. The problem was with the tables
normalized, I could not create layout of the report that the users require.
So I created this table and am filling it in on the fly from the normalized
data. I had to make several duplicate queries. I unioned them all together,
but the update query won't work because the query is not updateable. Do I
have to update each duplicate query because the union query is not updateable
or is it the duplicate queries that are not updateable because they use group
in the query created in the criteria section?

:

I'm assuming you have a table with these fields, and probably more:
EmployeeID
Date
TimeSlotID

You could prevent problems in the first place by placing a unique index on
these 3 fields (one index, containing all 3 fields). This would prevent
anybody from booking an employee for a timeslot for which he's already
booked.

But for cleaning up existing data, you can use a single duplicates query,
where you define that all 3 of these fields should match. You'd then want
to show at least the ClientID in the query as well.

HTH

Cool idea, but the query window is limited to the number of characters
that
it can use, and the extent of what I'm matching on... I'm afraid I will
either exceed the limit or have to write several duplicate queries and
then
union them all together before the second query. Do you have any ideas
about
that? My criteria is as follows...

if one employee on one day has any of 56 15minute time slots duplicated
with
more than one client, than there is a conflict. I removed the cliendid in
the code below. It was incorrect and shouldn't be there, but you can see
the
jist of what I'm trying to accomplish below. I'm going to see if I can
work
this out with the queries you suggested. I'll post back if I have
problems.

:

For your second problem, here's what I'd do:

Use the wizard to build a FindDuplicates query, matching the fields that
are
of concern to you.
The build an update query based on the first one, setting all of the
"conflict" fields to True.
The only code you'd need to write would be to run the second query.

HTH

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. (Which actually hasn't been
necessary yet, but it needs to check anyway) When it gets to the
bottom
of
the loop, just prior to .movenext it is still on the first record as
it
should be. When it executes the .movenext statement it jumps to the
last
record, when it loops the second time around the loop and gets to the
.movenext statement it is still on the last record. .movenext doesn't
even
move it to eof at this point and I'm now in an infinite loop on the
last
record. (I have compacted/repaired just in case it was a problem
there...
no
luck)

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>
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

rs.movenext not working properly 1
looping through records 4
newbee needs help 15
Help editing record set 8
compile error: For control variable already in use 1
Form data not refreshing after editing 1
Please help to email reports in snp files 0
VBA works in orignal but fails in copy of database 1

Top