CurrentDb.Execute update syntax problem
I am having a problem with vba code to update a table.
I have two tables tblMatrix and tblMatirxTemp
When I fire the code, I want to update tblMatrix.Hours with the value tblMatrixTemp.Hours1 when the Employee, Customer and Project from both tables match and when tblMatrix.WeekEndingDate = tblMatrixTemp.WED1 match.
Using an update query, I can get the desired results.
UPDATE tblMatrix INNER JOIN tblMatrixTemp ON (tblMatrix.WeekEndingDate = tblMatrixTemp.WED1) AND (tblMatrix.HourType = tblMatrixTemp.HourType) AND (tblMatrix.Project = tblMatrixTemp.Project) AND (tblMatrix.Customer = tblMatrixTemp.Customer) AND (tblMatrix.Employee = tblMatrixTemp.Employee) SET tblMatrix.Hours = [tblMatrixTemp].[Hours1];
I do have a test to confirm that the record I want to update exists in the table.
I attached my vba code below
I have two tables tblMatrix and tblMatirxTemp
When I fire the code, I want to update tblMatrix.Hours with the value tblMatrixTemp.Hours1 when the Employee, Customer and Project from both tables match and when tblMatrix.WeekEndingDate = tblMatrixTemp.WED1 match.
Using an update query, I can get the desired results.
UPDATE tblMatrix INNER JOIN tblMatrixTemp ON (tblMatrix.WeekEndingDate = tblMatrixTemp.WED1) AND (tblMatrix.HourType = tblMatrixTemp.HourType) AND (tblMatrix.Project = tblMatrixTemp.Project) AND (tblMatrix.Customer = tblMatrixTemp.Customer) AND (tblMatrix.Employee = tblMatrixTemp.Employee) SET tblMatrix.Hours = [tblMatrixTemp].[Hours1];
I do have a test to confirm that the record I want to update exists in the table.
I attached my vba code below
CurrentDb.Execute "UPDATE tblMatrix SET tblMatrix.Hour = tblMatrixTemp.Hours1 WHERE (tblMatrix.WeekEndingDate = tblMatrixTemp.WED1) AND (tblMatrix.HourType = tblMatrixTemp.HourType) AND (tblMatrix.Project = tblMatrixTemp.Project) AND (tblMatrix.Customer = tblMatrixTemp.Customer) AND (tblMatrix.Employee = tblMatrixTemp.Employee)"
You SQL looks fine. I susepct that the issue is with differering field types between the two tables.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
use this formatting so you can easily the whole sql statement, using
debug.print sql
debug.print sql
Dim sql
sql = "UPDATE tblMatrix SET tblMatrix.Hour = tblMatrixTemp.Hours1"
sql = sql & " WHERE (tblMatrix.WeekEndingDate = tblMatrixTemp.WED1)"
sql = sql & " AND (tblMatrix.HourType = tblMatrixTemp.HourType)"
sql = sql & " AND (tblMatrix.Project = tblMatrixTemp.Project)"
sql = sql & " AND (tblMatrix.Customer = tblMatrixTemp.Customer)"
sql = sql & " AND (tblMatrix.Employee = tblMatrixTemp.Employee)"
debug.print sql
currentdb.execute sql
ASKER
Capricorn1.
I get an error:
Run-time error '3061':
Too few parameters. Expected 7.
and it hangs on
CurrentDb.Execute sql
I get an error:
Run-time error '3061':
Too few parameters. Expected 7.
and it hangs on
CurrentDb.Execute sql
check for the names of fields
hour is a reserved word, place it in []
Dim sql
sql = "UPDATE tblMatrix SET tblMatrix.[Hour] = tblMatrixTemp.Hours1"
sql = sql & " WHERE (tblMatrix.WeekEndingDate = tblMatrixTemp.WED1)"
sql = sql & " AND (tblMatrix.HourType = tblMatrixTemp.HourType)"
sql = sql & " AND (tblMatrix.Project = tblMatrixTemp.Project)"
sql = sql & " AND (tblMatrix.Customer = tblMatrixTemp.Customer)"
sql = sql & " AND (tblMatrix.Employee = tblMatrixTemp.Employee)"
debug.print sql
currentdb.execute sql
ASKER
Capricorn1.
My mistake. the field is Hours
I verified the spelling of all the field names.
Now the error is
Run-time error '3061':
Too few parameters. Expected 6
and still hangs on CurrentDb.Execute sql.
I did try matthewspatrick solution and it works. I would just like to know why this is failing. I really looks like it should work, but I don't want to wast you time. Thanks.
My mistake. the field is Hours
I verified the spelling of all the field names.
Now the error is
Run-time error '3061':
Too few parameters. Expected 6
and still hangs on CurrentDb.Execute sql.
I did try matthewspatrick solution and it works. I would just like to know why this is failing. I really looks like it should work, but I don't want to wast you time. Thanks.
ASKER
Thank you for the assist.