Insert & Syntax Error

You didn't close out the encapsulation of the date field with #'s.  If you look at the part that reads:

 "',#" & Format(Date, "m\/d\/yyyy") & "'****APPENDED Dnb*****: "

You will notice that you are missing a closing # just before the APPEND text string.  Try adding the closing #:

 "',#" & Format(Date, "m\/d\/yyyy") & "#," & "'****APPENDED Dnb*****: "

I added the part that is in bold/underlined text.

try this


strSQL = "INSERT INTO Projects ([Status],[Currency],[Created By],[ProjectNo], [Project Name], [Start Date], Notes) " _
             & "VALUES ('7'," & DLookup("CurrencyID", "tblCurrencyExchange", "[Currency] = '" & Me.txtCur & "'") & ",'" & [TempVars]![CurrentUserID] & "','?','LC Ref: (but not needed so delete it after appending if want): ' & '" & Me.txtLCRef & "','" & Format(Date, "m\/d\/yyyy") & "'****APPENDED Dnb*****: " & Format(Date, "m\/d\/yyyy") & "')"

fyed:
I get a datatype mismatch


here is the debug print sql:
INSERT INTO Projects ([Status],[Currency],[Created By],[ProjectNo], [Project Name], [Start Date], Notes) VALUES ('7',1,'','?','LC Ref: (but not needed so delete it after appending if want): ' & '00440020001844x',#8/26/2013#,'****APPENDED Dnb*****: 8/26/2013')

maybe it is clear what is wrong.

thanks

Capricorn:  I got the same 3075 syntax error.

not sure if it makes a difference but
Projects.[Status] is Number property with row source:
SELECT tblStatus.ID, tblStatus.Status FROM tblStatus ORDER BY tblStatus.Status;

The Insert did work until I added what was underlined above in original question.
([Status],[Currency]  are the 2 additonal fields I added.

Projects.Currency is also number

i think you are missing a value..

specify which value should go to which field

[Status], --???
[Currency],--???
[Created By], --???
[ProjectNo], --???
[Project Name],--???
 [Start Date], --???
[Notes] --???

If [Status] is number use 7 instead of '7'
Currency is OK with 1

Capricorn:
<specify which value should go to which field

1.

[Status], --???            answer: 7

[Currency],--???         answer: the Dlookup

[Created By], --???     answer: TempVars

[ProjectNo], --???         answer: ?  (just a question mark)...it is text format

[Project Name],--???  answer: 'LC Ref: (but not needed so delete it after appending if want): ' & '" & Me.txtLCRef & "',

[Start Date], --???      #" & Format(Date, "m\/d\/yyyy") & "#,

[Notes] --???           answer: " & "'****APPENDED Dnb*****: " & Format(Date, "m\/d\/yyyy") & "')"
I know that must be hard to read.  I have used fyed's suggestion of closing # just before the APPEND text string.  

Actually I said the Insert worked but maybe now it didnt work.

this is what I have now:
changed '7' to just 7 and  used fyed's suggestion of closing # just before the APPEND text string.  

     strSQL = "INSERT INTO Projects ([Status2],[Currency],[Created By],[ProjectNo], [Project Name], [Start Date], Notes) " _
             & "VALUES (7," & DLookup("CurrencyID", "tblCurrencyExchange", "[Currency] = '" & Me.txtCur & "'") & ",'" & [TempVars]![CurrentUserID] & "','?','LC Ref: (but not needed so delete it after appending if want): ' & '" & Me.txtLCRef & "',#" & Format(Date, "m\/d\/yyyy") & "#," & "'****APPENDED Dnb*****: " & Format(Date, "m\/d\/yyyy") & "')"

here is the debug print :

INSERT INTO Projects ([Status2],[Currency],[Created By],[ProjectNo], [Project Name], [Start Date], Notes) VALUES (7,1,'','?','LC Ref: (but not needed so delete it after appending if want): ' & '00440020001844x',#8/26/2013#,'****APPENDED Dnb*****: 8/26/2013')

I do get datatype mismatch still

properties
 
Projects.[Status2] = Number
Projects.[Currency] = Number
Projects.[Created By] = Number
Projects.[ProjectNo] = Text
Projects.[Project Name] = Text
Projects.[Start Date] = Date / Time
Projects.[Notes]  = Memo

MAYBE THIS is easier to read:

 strSQL = "INSERT INTO Projects ([Created By],[Status2],[Currency],[ProjectNo],[Project Name],[Start Date],[Notes]) " _
             & "VALUES ('" & [TempVars]![CurrentUserID] & "'," _
             & 7 & "," _
             & DLookup("CurrencyID", "tblCurrencyExchange", "[Currency] = '" & Me.txtCur & "'") & "," _
             & '?' & "," _
             & 'LC Ref: (but not needed so delete it after appending if want): ' & '" & Me.txtLCRef & "'," _
             & #" & Format(Date, "m\/d\/yyyy") & "#," _
             & "'****APPENDED Dnb*****: " & Format(Date, "m\/d\/yyyy") & "')"

Select allOpen in new window

I get a compile errror line 5

dang I thought this was going to be easy.  Sorry.

I get error 3421 - Data type conversion error

let me know what is next.

Not sure if makes a difference:
Status2 is  Number with rowsource:  SELECT tblStatus.ID, tblStatus.Status FROM tblStatus ORDER BY tblStatus.Status;

which line?

It doesnt really say but I put a break line currency line and hovered over [Created By] and it says Null.
    ![Created By] = [TempVars]![CurrentUserID]

Maybe there needs to be a test for Null?

where did you add the values for [TempVars]![CurrentUserID] ?

Values for [TempVars]![CurrentUserID] is set in the login screen at startup:

Private Sub cmdLogin_Click()
On Error GoTo cmdLogin_Click_Err

    If IsNull(cboCurrentEmployee) Then
     Beep
    MsgBox "You must first select an employee.", vbOKOnly, ""
   
    Exit Sub
   
    End If
   
    TempVars.Add "CurrentUserID", Me.cboCurrentEmployee.Value

do the Login first, before running the codes...

I tried....
I did this in the immediate window and gives Error:

?[CurrentUserID]
Error 2029

I dont know if I lose the temp variable at a certain point because it is "temp".  ON another form I have, the [currentuserID] is correct.

try

? [TempVars]![CurrentUserID]

well I do have code in the same form that inserts the correct value for  [TempVars]![CurrentUserID]
I am inserting on tblLetterOfCredit and not [Projects] but [Created By] has the same row source on tblLetterOfCredit as it does on [Projects].

here is a part of the code on another cmdbutton on same form:
strSQL = "INSERT INTO tblLetterOfCredit ([Created By],[LCNo] , [DateOfIssueSB], [FinalMaturity], " _
                & "[Currency], [Amount], [Comments]) " _
                & "VALUES ('" & [TempVars]![CurrentUserID] & "', '" & Me.txtLCRef & "'," _

? [TempVars]![CurrentUserID]
138

138 is correct

fyed:  that was the solution.  Tempvars worked too.

I cant say what specifically the issue was.  I know of the confusion with using the Lookup feature in the table but I dont think that was the issue since I had it working before with no issue and I simply copied that dlookup that we worked on couple days ago.  

I think it was either with item 2 or 3 above [project name], [start date]

Thought this was going to be a little easier.  I dont like my questions to be like this...remember I am far from your level.  thanks for hanging in with me.  

Maybe I should split points?

capricorn hung with you for most of this thread, while just stuck my head in at the beginning and the end.  Award points accordingly.

Whenever I want to build a SQL string for an INSERT , UPDATE, or even a SELECT statement to be used in code, I use the function below:

Public Function fnWrap(WrapWhat As Variant, Optional WrapWith As Variant = """") As String

    fnWrap = WrapWith _
           & Replace(WrapWhat, WrapWith, WrapWith & WrapWith) _
           & WrapWith
    
End Function

Select allOpen in new window

to wrap text strings in quotes and dates in the # sign.  It is a little easier to read than the method you were trying above, because you don't have all of the embedded single quotes in your text.  Given what we now know about the data types of each of your fields, from the recordset solution above, I think the following SQL string would also work:

strCriteria = "[Currency] = '" & Me.txtCur & "'"
strSQL = "INSERT INTO Projects ([Created By],[Status2],[Currency],[ProjectNo]" _
                             & "[Project Name],[Start Date],[Notes]) " _
      & "VALUES (" & [TempVars]![CurrentUserID] & "," _
                      & 7 & "," _
                      & DLookup("CurrencyID", "tblCurrencyExchange", strCriteria) & "," _
                      & "?" & "," _
                      & fnWrap("LC Ref: (but not needed so delete it after appending " _
                             & "if want): " & Me.txtLCRef) & "," _
                      & fnWrap(Date(), "#") & ", " _
                      & fnWrap("****APPENDED Dnb*****:" & Format(Date, "m\/d\/yyyy")) _
             & ")"

Select allOpen in new window

thank you for the expert help.   Im sticking with the With statement. it works.  the fnWrap and all is getting a little techy for me.  :)