Hi all,

Please have a quick look at my awesome VBA code...


strSQL = "INSERT INTO tblHours ( [Project ID], [Employee ID], TS_Date, TS_Hours ) VALUES (" & selectproject & ", Forms!frmEmployeeTimesheet!cboSelectName, " & CStr(datebox) & ", " & CStr(Box) & ");"
DoCmd.RunSQL strSQL
I'd like to be able insert another value into tblHours. The field name has already been setup as "Payment." The value is calculated as follows...
CStr(Box) * ( tblEmployee.[Hourly Rate] WHERE [Employee ID] = tblEmployee.[Hourly Rate] )

Now I'm not sure of the correct syntax but the below is the best I was able to come up with.


strSQL = "INSERT INTO tblHours ( [Project ID], [Employee ID], TS_Date, TS_Hours, Payment ) VALUES (" & selectproject & ", Forms!frmEmployeeTimesheet!cboSelectName, " & CStr(datebox) & ", " & CStr(Box) & ", tblEmployee.[Hourly Rate] * " & Box & ") WHERE ( tblHours.[Employee ID] = Forms!frmEmployeeTimesheet!cboSelectName) ;"
Now when I run this, the error is "Query input must contain at least one table or query"

Help?

1jet,

The Values clause implies that it is a list of values,
The Where clause implies that the data is from a table.
It can't be both.

Change:


strSQL = "INSERT INTO tblHours ( [Project ID],
[Employee ID],
TS_Date,
TS_Hours,
Payment )
VALUES (" & selectproject & ",
Forms!frmEmployeeTimesheet!cboSelectName, " &
CStr(datebox) & ", " &
CStr(Box) & ",
tblEmployee.[Hourly Rate] * " &
Box & ")
WHERE ( tblHours.[Employee ID] = Forms!frmEmployeeTimesheet!cboSelectName) ;"


To:


strSQL = "INSERT INTO tblHours ( [Project ID],
[Employee ID],
TS_Date,
TS_Hours,
Payment )
VALUES (" & selectproject & ",
Forms!frmEmployeeTimesheet!cboSelectName, " &
CStr(datebox) & ", " &
CStr(Box) & ",
Nz(DLookUp("[Hourly Rate]", "tblEmployee", "[Employee ID] = " & Forms!frmEmployeeTimesheet!cboSelectName), 0)


But I don't see any "punctuation" in your values clause.
Dates should be surrounded by # -->


strSQL = "INSERT INTO tblHours ( [Project ID], " & _
" [Employee ID], " & _
" TS_Date, " & _
" TS_Hours, " & _
" Payment ) " & _
"VALUES (" & selectproject & ", " & _
Forms!frmEmployeeTimesheet!cboSelectName, #" &
CStr(datebox) & "#, " & _
CStr(Box) & ", " & _
Nz(DLookUp("[Hourly Rate]", "tblEmployee", "[Employee ID] = " & Forms!frmEmployeeTimesheet!cboSelectName), 0) & ")"


hth
Wayne

sorry wayne...u've misread my question

in my query, the 5th value going into tblHours.Payment is not tblEmployee.[Hourly Rate]
instead, its actually tblEmployee.[Hourly Rate] * Box (where Box is a textbox with number of hours worked)

Sorry about my messy code...but I've tidied it up as follow making some changes since my first post. I think the problem could be that I need to JOIN tblHours and tblEmployee to get [Hourly Rate].


strSQL = "INSERT INTO tblHours ( [Project ID], [Employee ID], TS_Date, TS_Hours, Payment ) " & _
" SELECT (" & selectproject & ", Forms!frmEmployeeTimesheet!cboSelectName, " & CStr(datebox) & ", " & CStr(Box) & ", (tblEmployee.[Hourly Rate] * " & Box & " )) " & _
" FROM ( tblHours INNER JOIN tblEmployee ) " & _
" ON ( tblHours.[Employee ID] = tblEmployee.[Employee ID] ) " & _
" WHERE ( tblEmployee.[Employee ID] = Forms!frmEmployeeTimesheet!cboSelectName ) ; "
DoCmd.RunSQL strSQL