Code Error

[alfiajamel]

Hi..

Would someone please tell me what is wrong with the code below. I get the
error message of "there is an invalid use of the .(dot) or ! operator or
invalid parentheses. Thanks in advance!

CurrentDb.Execute "INSERT INTO tblProjectAssignment (EmpID, SupID, " & _
"ProjectID, ProjectSupID, InsertDate, EffectiveDate,
Logon) " & _
"VALUES (" & lstSelectEmp.Column(0) & ", " & _
cboCurrentSupervisor.Column(0) & ", " & _
Chr$(34) & txtProjectID & Chr$(34) & ", " & _
Chr$(34) & txtProjectSupID & Chr$(34) & ", " & _
Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & "," & _
Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ", " & _
Chr$(34) & txtLogon & Chr$(34) & ")"

 

[Steve Sanford]

Are the fields "ProjectID" and "ProjectSupID" of data type Long Int?
It looks to me like there are double quotes (Chr$(34)) around them:

Fields Values
-------------------------------------------
EmpID, lstSelectEmp.Column(0)
SupID, cboCurrentSupervisor.Column(0)
ProjectID, Chr$(34) & txtProjectID & Chr$(34) <---------
ProjectSupID, Chr$(34) & txtProjectSupID & Chr$(34) <------
InsertDate, Format(txtInsertDate, "\#yyyy\-mm\-dd\#")
EffectiveDate, Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#")
Logon Chr$(34) & txtLogon & Chr$(34)


What happens if you change the code to this:

Dim strSQL as string

strSQL = "INSERT INTO tblProjectAssignment (EmpID, SupID, " & _
"ProjectID, ProjectSupID, InsertDate, EffectiveDate, Logon) " & _
"VALUES (" & lstSelectEmp.Column(0) & ", " & _
cboCurrentSupervisor.Column(0) & ", " & _
Chr$(34) & txtProjectID & Chr$(34) & ", " & _
Chr$(34) & txtProjectSupID & Chr$(34) & ", " & _
Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & "," & _
Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ", " & _
Chr$(34) & txtLogon & Chr$(34) & ")"

' ******* for debugging *********

Debug.Print strSQL
STOP

' ******* for debugging *********

CurrentDb.Execute strSQL, dbfailonerror


Then look at the strSQL string in the immediate window?


HTH

 

[alfiajamel]

Greetings...

This what I get in the immediate window:
INSERT INTO tblProjectAssignment (EmpID, SupID, ProjectID, ProjectSupID,
InsertDate, EffectiveDate) VALUES (451118, 454644, "1", "123456",
#2008-03-24#,#2008-03-23#

The values are correct, however, they just won't insert into the table.

 

[alfiajamel]

OOPS!! I forgot to answer your question! The fields are just regular text
fields.

 

[Steve Sanford]

If this is copied (not retyped) from the immediate window:

INSERT INTO tblProjectAssignment (EmpID, SupID, ProjectID, ProjectSupID,
InsertDate, EffectiveDate) VALUES (451118, 454644, "1", "123456",
#2008-03-24#,#2008-03-23#

you've changed the INSERT statement. Where is [txtLogon] and the closing
parenthesis?


From your original post, I made a table, form, added controls and added your
code. The insert statement ran without a problem.

I'm at a loss ....

HTH

 

[alfiajamel]

Hi Steve,
I copied the statement from the immediate window. I also copied the
suggested code just as it was posted. I took out the logon because it wasn't
returning a value for me and I thought maybe that was the problem. I have
recopied the suggested coding based on my original post, and I am still
getting that error message. This is what I have in my code:

strSQL = "INSERT INTO tblProjectAssignment (EmpID, SupID, " & _
"ProjectID, ProjectSupID, InsertDate, EffectiveDate, Logon) " & _
"VALUES (" & lstSelectEmp.Column(0) & ", " & _
cboCurrentSupervisor.Column(0) & ", " & _
Chr$(34) & txtProjectID & Chr$(34) & ", " & _
Chr$(34) & txtProjectSupID & Chr$(34) & ", " & _
Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & "," & _
Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ", " & _
Chr$(34) & txtLogon & Chr$(34) & ")"

 

[Steve Sanford]

I would check the spelling of the controls and field names. The code run for
me, but I used the names from your posts/code to create the names for the
fileds and controls.

Next thing to try is to start with one field and see if it inserts a record.

(watch for line wrap)
strSQL = "INSERT INTO tblProjectAssignment (EmpID) VALUES (" &
lstSelectEmp.Column(0) & ")"



Then add in another field. Keep adding fields until you get the error.

HTH

 

[alfiajamel]

Thank you for all of your help. I had inserted and extra ) on the first line!

 

[Steve Sanford]

You're welcome