Check Code Please

[Afrosheen via AccessMonster.com]

Here's what I did John, First I copied and pasted the sql6 in the new query.
I had about 3-4 records [super1] set to the sdr555. When I looked at it in
datasheet view it only showed 1 record. When I ran the query ! it did clear
out the first record. There were no more records in the query. When I looked
at the rest of the records in the table, the other records still had the
sdr555.

This was the results of the ?sql6
Update tblMain set super1 = Null where super1 ="sdr555" And Not (tblMain.
SupMan) = true

I put the extra comma in.
dtm.Execute sql6, , adCmdText + adExecuteNoRecords

[quoted text clipped - 4 lines]

When it did execute I checked the superid of an employee and the superid was
still there in table view.

Perplexing!

Try typing

?sql6

in the immediate window. Copy and paste the SQL string into the SQL window of
a new query. Open it in datasheet view; does it display the records you
expect? If you execute it by clicking the ! icon, does it give an error
message?

 

[John W. Vinson]

Here's what I did John, First I copied and pasted the sql6 in the new query.
I had about 3-4 records [super1] set to the sdr555. When I looked at it in
datasheet view it only showed 1 record. When I ran the query ! it did clear
out the first record. There were no more records in the query. When I looked
at the rest of the records in the table, the other records still had the
sdr555.

This was the results of the ?sql6
Update tblMain set super1 = Null where super1 ="sdr555" And Not (tblMain.
SupMan) = true

Well, evidently there was only one record in tblMain for which Super1 was
equal to "sdr555" and SupMan was True.

If you filter or query tblMain to find all records with Super1 = "sdr555" what
do you get? What's SupMan in these records?

I suspect the problem is in the data, not in the query!

 

[Afrosheen via AccessMonster.com]

I checked out the query and there are 3 records with Super1 = "sdr555" and
the SupMan field is true{check marks in the box} in all three.

The SupMan field is a check box where when checked you are a supervisor.

Thanks for all your help John...

Here's what I did John, First I copied and pasted the sql6 in the new query.
I had about 3-4 records [super1] set to the sdr555. When I looked at it in

[quoted text clipped - 6 lines]

Update tblMain set super1 = Null where super1 ="sdr555" And Not (tblMain.
SupMan) = true

Well, evidently there was only one record in tblMain for which Super1 was
equal to "sdr555" and SupMan was True.

If you filter or query tblMain to find all records with Super1 = "sdr555" what
do you get? What's SupMan in these records?

I suspect the problem is in the data, not in the query!

 

[Afrosheen via AccessMonster.com]

I did some more tests based on your information from the previous post and it
did show the three records. I did a paste and copy of sql6 on a new query
again and it showed the three records with the sdr555. When I ran the query
this time I changed the supman field to false. And It seemed to work. So I
tried it on the form and checked the table and the records were updated. So I
guess it is working.

sql6 = "Update tblMain set super1 = Null where super1 =""" & txtAssist1 & """
And (tblMain.SupMan) = false"

The only thing I'm wondering about is the [SupMan] field. The sql6 statement
is based on the record having a false [SupMan] field I wanted for just the
supervisor not the employee. The sql6 statement is for both. Maybe I should
just use the first part of the sql6 statement.

Thanks again John. You've been a great help. See you on the next problem if
you so choose to read it.

Here's what I did John, First I copied and pasted the sql6 in the new query.
I had about 3-4 records [super1] set to the sdr555. When I looked at it in
datasheet view it only showed 1 record. When I ran the query ! it did clear
out the first record. There were no more records in the query. When I looked
at the rest of the records in the table, the other records still had the
sdr555.

This was the results of the ?sql6
Update tblMain set super1 = Null where super1 ="sdr555" And Not (tblMain.
SupMan) = true
[quoted text clipped - 12 lines]

expect? If you execute it by clicking the ! icon, does it give an error
message?

 

[John W. Vinson]

sql6 = "Update tblMain set super1 = Null where super1 =""" & txtAssist1 & """
And (tblMain.SupMan) = false"

The only thing I'm wondering about is the [SupMan] field. The sql6 statement
is based on the record having a false [SupMan] field I wanted for just the
supervisor not the employee. The sql6 statement is for both. Maybe I should
just use the first part of the sql6 statement.

I would guess that sql6 shouldn't be looking at SupMan *at all*; the records
that you want to update are those who have sdr555 as a supervisor, and I'm
guessing that you want to do so whether the employee whose record you're
updating is a supervisor or not.

 

[Afrosheen via AccessMonster.com]

True. I did change it and it now works perfectly. Again, I thank your for
your help with in the last week or two. Your help has been valuable. I've
learned a lot. Now I hope I don't get the CRS syndrome.

Talk to you on the next problem..

sql6 = "Update tblMain set super1 = Null where super1 =""" & txtAssist1 & """
And (tblMain.SupMan) = false"

[quoted text clipped - 3 lines]

supervisor not the employee. The sql6 statement is for both. Maybe I should
just use the first part of the sql6 statement.

I would guess that sql6 shouldn't be looking at SupMan *at all*; the records
that you want to update are those who have sdr555 as a supervisor, and I'm
guessing that you want to do so whether the employee whose record you're
updating is a supervisor or not.