Check Code Please
in [Forms]
|
From: Afrosheen via AccessMonster.com on 1 Feb 2010 18:26 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 John W. Vinson wrote: >>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? -- Message posted via http://www.accessmonster.com
From: John W. Vinson on 1 Feb 2010 20:17 On Mon, 01 Feb 2010 23:26:43 GMT, "Afrosheen via AccessMonster.com" <u46942(a)uwe> wrote: >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! -- John W. Vinson [MVP]
From: Afrosheen via AccessMonster.com on 2 Feb 2010 06:17 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... John W. Vinson wrote: >>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! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201002/1
From: Afrosheen via AccessMonster.com on 2 Feb 2010 07:09 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. Afrosheen wrote: >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 - 12 lines] >>expect? If you execute it by clicking the ! icon, does it give an error >>message? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201002/1
From: John W. Vinson on 2 Feb 2010 12:57
On Tue, 02 Feb 2010 12:09:40 GMT, "Afrosheen via AccessMonster.com" <u46942(a)uwe> wrote: >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. -- John W. Vinson [MVP] |