Query Working in Access but not VBA

[wickidwe]

I have the following Query from an Access Query Builder and it works fine.
please note that the tbl36hrWeekRoster table is a table that contains only dates and is joined to nothing. (I only included to be able to give me a Date Field).
I need to run from a button on a form.

Working Query:

INSERT INTO tblRosterRec ( Date_, FctnID, EmpId, Name )
SELECT tbl36hrWeekRosters.Date_, tblFunctions.ReliefCode, qryAllocated.EmpId, qryAllocated.Name
FROM tbl36hrWeekRosters, qryAllocated INNER JOIN tblFunctions ON qryAllocated.FctnDesc = tblFunctions.FctnDesc
WHERE tbl36hrWeekRosters.Date_=[Forms]![Form1]![txtDate];

Non Working VBA Verson:

Set dbs = CurrentDb
Dim sSQL As String
strSQL = "INSERT INTO tblRosterRec ( Date_, FctnID, EmpId, Name ) " & _
"SELECT tbl36hrWeekRosters.Date_, tblFunctions.ReliefCode, qryAllocated.EmpId, qryAllocated.Name " & _
"FROM tbl36hrWeekRosters, qryAllocated INNER JOIN tblFunctions ON qryAllocated.FctnDesc = tblFunctions.FctnDesc " & _
"WHERE tbl36hrWeekRosters.Date_=#" & Me.txtDate & "#;"
dbs.Execute (sSQL)

I am getting the following error:

Is there another way to run the Access Query from the form

 

[pbaldy]

For starters, you set one variable but execute another.

 

[wickidwe]

Error:

Run-time error '3078':
The Microsoft Access Database engine cannot find the input table or
query". Make sure it exsists and the name is spelt correctly

 

[pbaldy]

By the way, you can execute the saved query with DoCmd.OpenQuery. Execute won't work because of the form reference.

 

[wickidwe]

Oops, That Wasnt the main Issue though.

after fixing that up I am getting

"too few Parameters. Expected 3"

Any Ideas?

 

[pbaldy]

If I'm a betting man, I'm betting that there are 3 form references in qryAllocated.