Check Code Please
in [Forms]
|
From: John W. Vinson on 31 Jan 2010 21:41 On Mon, 01 Feb 2010 00:03:58 GMT, "Afrosheen via AccessMonster.com" <u46942(a)uwe> wrote: >Ok. Thanks for all your replies. Now to get back to the problem at hand. >First I want to say that I messed up. It is NOT a yes/no field. It is a text >field that needs to change [Super1]. The yes/no field [SupMan]is a check box >to basically say that if it is checked then this person is a supervisor. If >it is not checked then the person is not a supervisor and then supposed to >find all employees that has the supervisor id and change them to Null or make >it empty. And It still does not work. It leaves the information in the >[Super1] field. Afrosheen, are you still storing the supervisor's *NAME* in the Super1 field in the Employee table? You really should not be doing so! The Employee table should have a SuperID field - a *numeric* field, linked to the EmployeeID of the supervisor. I'm confused about what you're trying to accomplish by running this update query. -- John W. Vinson [MVP]
From: Afrosheen via AccessMonster.com on 1 Feb 2010 06:37 No John. The SuperId I don't believe can be a number because it contains his StaffId a text and number for example. John Joseph Tetter would be TJJ01 as his Staff Id. They are assigned from HR. Then there's a checkbox that when true says that he's a supervisor and I can select his name from a combo box that will associate an employee to that supervisor. Then whom ever his employees are would have his TJJ01 as SuperId. I'm trying to clear the SuperId field out all of the employees SuperId field associated with TJJ01 if John Joseph Tetter supervisor check box is false (not a supervisor any more). I hope that will clarify what I'm trying to do. John W. Vinson wrote: >>Ok. Thanks for all your replies. Now to get back to the problem at hand. >>First I want to say that I messed up. It is NOT a yes/no field. It is a text >[quoted text clipped - 4 lines] >>it empty. And It still does not work. It leaves the information in the >>[Super1] field. > >Afrosheen, are you still storing the supervisor's *NAME* in the Super1 field >in the Employee table? > >You really should not be doing so! > >The Employee table should have a SuperID field - a *numeric* field, linked to >the EmployeeID of the supervisor. > >I'm confused about what you're trying to accomplish by running this update >query. -- Message posted via http://www.accessmonster.com
From: John W. Vinson on 1 Feb 2010 12:29 On Mon, 01 Feb 2010 11:37:00 GMT, "Afrosheen via AccessMonster.com" <u46942(a)uwe> wrote: >No John. The SuperId I don't believe can be a number because it contains his >StaffId a text and number for example. John Joseph Tetter would be TJJ01 as >his Staff Id. They are assigned from HR. Then there's a checkbox that when >true says that he's a supervisor and I can select his name from a combo box >that will associate an employee to that supervisor. Then whom ever his >employees are would have his TJJ01 as SuperId. Ahhhh... sorry, I'd missed that SuperID was a Text datatype. >I'm trying to clear the SuperId field out all of the employees SuperId field >associated with TJJ01 if John Joseph Tetter supervisor check box is false >(not a supervisor any more). Ok... so your current code is: 20 If SupMan = False Then txtAssist1 = StaffId 60 Set dtm = CurrentProject.AccessConnection sql6 = "Update tblMain set super1 = Null where super1 =""" & txtAssist1 & """ And Not (tblMain.SupMan) = true" '70 sql6 = "Update tblMain set super1 = False where super1 =" & txtAssist 80 dtm.Execute sql6, adCmdText + adExecuteNoRecords 90 Set dtm = Nothing 100 End If I *think* you're just missing a comma! The second operand to the Execute method returns the number of records affected; the *third* argument is the Options, which should be the adCmdText + adExecuteNoRecords values. Try adding one more comma after sql6. You may want to step through the code in debug mode and see what sql6 contains prior to executing the query. -- John W. Vinson [MVP]
From: Afrosheen via AccessMonster.com on 1 Feb 2010 12:59 I put the extra comma in. dtm.Execute sql6, , adCmdText + adExecuteNoRecords I put a break point in and looked at the sql string and it was correct. I also hovered over the execute and that looked correct. When it did execute I checked the superid of an employee and the superid was still there in table view. Thanks again for your help.. John W. Vinson wrote: >>No John. The SuperId I don't believe can be a number because it contains his >>StaffId a text and number for example. John Joseph Tetter would be TJJ01 as >>his Staff Id. They are assigned from HR. Then there's a checkbox that when >>true says that he's a supervisor and I can select his name from a combo box >>that will associate an employee to that supervisor. Then whom ever his >>employees are would have his TJJ01 as SuperId. > >Ahhhh... sorry, I'd missed that SuperID was a Text datatype. > >>I'm trying to clear the SuperId field out all of the employees SuperId field >>associated with TJJ01 if John Joseph Tetter supervisor check box is false >>(not a supervisor any more). > >Ok... so your current code is: > >20 If SupMan = False Then > txtAssist1 = StaffId >60 Set dtm = CurrentProject.AccessConnection > sql6 = "Update tblMain set super1 = Null where super1 =""" & >txtAssist1 & """ And Not (tblMain.SupMan) = true" > >'70 sql6 = "Update tblMain set super1 = False where super1 =" & >txtAssist >80 dtm.Execute sql6, adCmdText + adExecuteNoRecords >90 Set dtm = Nothing > >100 End If > >I *think* you're just missing a comma! The second operand to the Execute >method returns the number of records affected; the *third* argument is the >Options, which should be the adCmdText + adExecuteNoRecords values. Try adding >one more comma after sql6. > >You may want to step through the code in debug mode and see what sql6 contains >prior to executing the query. -- Message posted via http://www.accessmonster.com
From: John W. Vinson on 1 Feb 2010 15:55
On Mon, 01 Feb 2010 17:59:29 GMT, "Afrosheen via AccessMonster.com" <u46942(a)uwe> wrote: >I put the extra comma in. >dtm.Execute sql6, , adCmdText + adExecuteNoRecords > >I put a break point in and looked at the sql string and it was correct. >I also hovered over the execute and that looked correct. > >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 [MVP] |