INSERT INTO - Problem with SQL Statement ?

[VBKid04]

I am using a form, that when a button is pressed, must use an INSERT INTO statement.

The form has its own related table (called Task), which is passed into a recordset (rsTask), but it must use the INSERT INTO to insert 2 values into fields in a different table called Employee.

The idea is that if the Employee completes a certain number of tasks, then they are moved up a grade (at least, according to the system they are).


The code I have used for the button is as follows:

Dim Taskrs As Recordset
Set Taskrs = New ADODB.Recordset
Set Taskrs = db.Execute("SELECT EmpID From TASK WHERE TASK.EmpID='" & txtEmpID & "'")


Dim sqlSeniorEmployee
Dim sqlStandardEmployee
Dim sqlNew Employee

sqlSeniorEmployee = "INSERT INTO Emp(EmpTitle, EmpGrade) VALUES('Senior_Employee', 'Grade_3') WHERE EMP.EmpID = '" & txtEmpID & "'"

sqlStandardEmployee = "INSERT INTO Emp(EmpTitle, EmpGrade) VALUES('Standard_Employee', 'Grade_2') WHERE EMP.EmpID = '" & txtEmpID & "'"

sqlNewEmployee = "INSERT INTO Emp(EmpTitle, EmpGrade) VALUES('New_Employee', 'Grade_1)' WHERE EMP.EmpID = '" & txtEmpID & "'"


While Taskrs.RecordCount >= 100
db.Execute sqlSeniorEmployee
Wend

While Taskrs.RecordCount >= 25
db.Execute sqlStandardEmployee
Wend 

While Taskrs.RecordCount <= 10
db.Execute sqlNewEmployee
Wend


End Sub

However, when I press the button with this code I get this error:

Run-time error '-2147217900 (80040e14)':
Missing semicolon ( ; ) at end of SQL statement


To me the SQL statement looks correct, I don't think it has anything to do with a semicolon at all, but am I missing something or constructing it wrongly?


Also when the INSERT INTO has been performed I would also like to return the updated value of the field to a textbox called txtUpdatedValue on this form, but I'm not sure how...


Can anyone please help with these probs?

[ahara]

sqlNewEmployee = "INSERT INTO Emp(EmpTitle, EmpGrade) VALUES('New_Employee', 'Grade_1)' <----single quote should be inside parenthesis end delimiting Grade_1

WHERE EMP.EmpID = '" & txtEmpID & "'"

As for the text box thing, you could simply put...

Text1.Text = [whatever] under each of your loop conditions - which by the way you do realize will perform multiple inserts on the same record(??)...should it maybe be...

Select case Taskrs.RecordCount
 
        case >=100
               db.Execute sqlSeniorEmployee
               txtEmpTitle.Text = "Senior Employee"
               txtEmpGrade.Text = "Grade 3"
        case >=25
               db.Execute sqlStandardEmployee
               txtEmpTitle.Text = "Standard Employee"
               txtEmpGrade.Text = "Grade 2"
        case <=10
               db.Execute sqlNewEmployee
               txtEmpTitle.Text = "New Employee"
               txtEmpGrade.Text = "Grade 1"
 
End Select

cheers

[VBKid04]

ahara
thanks for that, I'll try it out...

although someone has just informed me maybe I should be using UPDATE rather than Insert Into, as Insert Into is only for adding new records?

As for the text box thing...

How exactly do you mean I will be "performing multiple inserts on the same record"?

Can you please elaborate on this a little ?

Thanks.

[ahara]

yeah (didn't scroll over and see the where clauses)...your inserts make no sense as they are for new records...to update you use update statement like:

UPDATE tblEmployee set status='ACTIVE' where empID=34

the following is Microsoft's info on sql:

http://msdn.microsoft.com/library/de.../acfundsql.asp

cheers