Error in "Insert Into" Query

[WickidWe]

Hi, I have the following SQL Statment in VBA that is coming up with the debugger (image below)

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

Can someone please tell me where I'm going wrong.
When I tried this query in the query builder it worked fine


but where I have changed it for vba something has gone wrong.

[RuralGuy]

Try changing this:
"ORDER BY tblFunctions.ReliefCode;"
...to...
" ORDER BY tblFunctions.ReliefCode;"
Notice the leading " ".

[WickidWe]

I tried this and got the following error now.

[WickidWe]

please note that "tbl36hrWeekRosters" only contains dates and is not joined to anything.
the only reason this is used is so I could update the date column with the date contained in the form "txtdate" box

[RuralGuy]

All we did is eliminate the 1st error the parser came to. I honestly do not know if you can contruct SQL that way. Try using the Query builder to construct this.

[June7]

Is Date_ field a date or text type? You show date criteria in dd/mm/yyyy structure. This will likely cause issues with date field type. Review: http://allenbrowne.com/ser-36.html

When constructing SQL in VBA, criteria for a date field need # delimiters, for a text field need apostrophe.

"WHERE tbl36hrWeekRosters.Date_ = #" & Me.txtDate & "#" & _

However, date field and criteria values need to be in mm/dd/yyyy structure. If you have allowed input as dd/mm/yyyy, you will probably get inconsistent results. See the Allen Browne article.

Table tbl36hrWeekRosters is not joined to other tables in the query. This will result in a Cartesian join of records. I am also not sure if the query will work with that arrangement.

[WickidWe]

Is there another way to insert into without using tbl36hrWeekRosters?
All I need to add is the date that is from the listbox txtDate on the form

[WickidWe]

Maybe I am asking the wrong question...

How can I "INSERT INTO" from Tables and forms at the same time?

"INSERT INTO tblRosterRec ( Name, Date_, FctnID, EmpId )

Name, FctnID and EmpID come from tables and Date_ will come from the form.

[June7]

Concatenate variables. Reference to control on form is a variable.

Maybe:

sSQL = "INSERT INTO tblRosterRec ( Name, FctnID, EmpId, Date_ ) " & _
"SELECT qryAllocated.Name, tblFunctions.ReliefCode, qryAllocated.EmpId, " & Me.textbox & " AS Date_ " & _
"FROM qryAllocated INNER JOIN tblFunctions ON qryAllocated.FctnDesc = tblFunctions.FctnDesc ;"

[WickidWe]

this is my query now, I am now getting a different error

sSQL = "INSERT INTO tblRosterRec ( Name, Date_, FctnID, EmpId ) " & _
"SELECT qryAllocated.Name, " & Me.txtDate & " AS Date_&, Functions.ReliefCode, qryAllocated.EmpId" & _
"FROM qryAllocated INNER JOIN tblFunctions ON qryAllocated.FctnDesc = tblFunctions.FctnDesc;"

[June7]

Remove the &:

AS Date_&,

Need a space after EmpID:

.EmpID "
"FROM

[WickidWe]

I am still getting an error "Too Few Parameters. Expected 4".

this SQL below is straight out of a query in Access itself and is the original I used.
Note that I have still included the tbl36hrWeekRosters table with no joins.
when I run this query, it does exactly what it needs to do and adds the data correctly to the tblRosterRec table.

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];

Maybe you might be able to translate it for use with vba better than I did.

[June7]

Assuming the code is behind Form1:

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 & "#";

[WickidWe]

I am still getting the error "too few parameters. Expected 3" with this.
Is there a way to just command the access query to run if I save the query as qryRosterLog?

[June7]

Ooops! The semi-colon should be within quote marks or remove it because it isn't really needed with action sql.

However, that still doesn't explain why the other sql fails. The debugger actually stops on the sql construct?

Then check out DoCmd.OpenQuery