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) & ")"

 

[Linq Adams via AccessMonster.com]

I suspect your troubles are coming from statements like these:

#yyyy\-mm\-dd\#

Access interprets the pound signs to mean that what lies bewtween them

yyyy\-mm\-dd\

is a Date literal, and obviously it isn't, which is throwing the Access off.

 

[Marshall Barton]

I suspect your troubles are coming from statements like these:

#yyyy\-mm\-dd\#

Access interprets the pound signs to mean that what lies bewtween them

yyyy\-mm\-dd\

is a Date literal, and obviously it isn't, which is throwing the Access off.

That is not the case when it is used in the Format function.
The result of the function will be something like:
#2008-03-21#
which is a perfectly valid date literal.

 

[Marshall Barton]

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) & ")"

I don't see a problem with your SQL statement's syntax.
Maybe you forgot to quote a text value or maybe the combo or
list box values are Null??

 

[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