I've no hair left...

[1jet]

Hi all, please have a quick look at my form below.

http://img515.imageshack.us/img515/8040/timesheetcommentswq8.jpg

Right now, I have an INSERT query inside a loop for Days 1 - 7 of Project 1
(will probably create another loops outside for Projects 2 - 5).
The INSERT query is currently output onto a MSGBOX and looks fine to me.
After being happy with the query, I've changed the MSGBOX to DoCmd.RunSQL and
when I execute this from Access, apparently I have an "INSERT INTO syntax
error."

My code is below can anyone find what's wrong?

Function vbaTestAppend()

Dim strSQL As String
Dim project_count As Integer
Dim day_count As Integer
Dim projectcbo As String
Dim datebox As String
Dim daybox As String

project_count = 1
day_count = 0

projectcbo = "Forms!frmEmployeeTimesheet!cboSelectProject" & project_count
Do
day_count = day_count + 1
datebox = "Forms!frmEmployeeTimesheet!txtDay" & day_count
daybox = "Forms!frmEmployeeTimesheet!txtProj" & project_count & "Day" &
day_count
strSQL = "INSERT INTO tblHours ( [Project ID], [Employee ID], Date, Hours
) VALUES " & projectcbo & ", Forms!frmEmployeeTimesheet!cboSelectName, " &
datebox & ", " & daybox & ";"
DoCmd.RunSQL strSQL
Loop Until day_count = 7

End Function

 

[Dirk Goldgar]

Hi all, please have a quick look at my form below.

http://img515.imageshack.us/img515/8040/timesheetcommentswq8.jpg

Right now, I have an INSERT query inside a loop for Days 1 - 7 of Project
1
(will probably create another loops outside for Projects 2 - 5).
The INSERT query is currently output onto a MSGBOX and looks fine to me.
After being happy with the query, I've changed the MSGBOX to DoCmd.RunSQL
and
when I execute this from Access, apparently I have an "INSERT INTO syntax
error."

My code is below can anyone find what's wrong?

Function vbaTestAppend()

Dim strSQL As String
Dim project_count As Integer
Dim day_count As Integer
Dim projectcbo As String
Dim datebox As String
Dim daybox As String

project_count = 1
day_count = 0

projectcbo = "Forms!frmEmployeeTimesheet!cboSelectProject" & project_count
Do
day_count = day_count + 1
datebox = "Forms!frmEmployeeTimesheet!txtDay" & day_count
daybox = "Forms!frmEmployeeTimesheet!txtProj" & project_count & "Day" &
day_count
strSQL = "INSERT INTO tblHours ( [Project ID], [Employee ID], Date,
Hours
) VALUES " & projectcbo & ", Forms!frmEmployeeTimesheet!cboSelectName, " &
datebox & ", " & daybox & ";"
DoCmd.RunSQL strSQL
Loop Until day_count = 7

End Function

Without looking at anything else, I can tell you that the value list in your
SQL statement must be enclosed in parentheses:

strSQL = _
"INSERT INTO tblHours (" & _
"[Project ID], [Employee ID], [Date], Hours" & _
") VALUES (" & _
projectcbo & _
", Forms!frmEmployeeTimesheet!cboSelectName, " & _
datebox & ", " & daybox & _
");"

 

[1jet via AccessMonster.com]

Thank you.
It took me and another forum hours to realise this simple mistake.
Thanks

Hi all, please have a quick look at my form below.

[quoted text clipped - 37 lines]

End Function

Without looking at anything else, I can tell you that the value list in your
SQL statement must be enclosed in parentheses:

strSQL = _
"INSERT INTO tblHours (" & _
"[Project ID], [Employee ID], [Date], Hours" & _
") VALUES (" & _
projectcbo & _
", Forms!frmEmployeeTimesheet!cboSelectName, " & _
datebox & ", " & daybox & _
");"