Insert Into Query Help

[pat_nospam]

My SQL syntax is all messed up apparently. Access is not enjoying the line below (or the 100 other variations I've tried since). Can anyone lend an eye to see what I am doing wrong with the Insert statement?

DoCmd.RunSQL("INSERT into [Table Name] ([Field)] VALUES(' & CurrentUser() & ')")

Thank you very much in advance

 

[Mile-O]

Didn't Travis answer this in the Modules forum? Have you deleted the thread?

 

[pat_nospam]

Yeah - he answered on the debug method, which I tried to no avail.

I deleted both the other threads because I was duplicating my question over the wrong forums (I never noticed the Queries forum).

Hopefully someone's expertise here will be able to help out - I'm just not having much SQL luck today.

 

[Mile-O]

Could it be your datatype?
Does the field appending have values with apostrophes in? i.e. O'Connell

 

[pat_nospam]

Not quite sure, the latest error that I've got it down to - is it will execute the query but I get a "conversion warning error" which tells me, if I click Yes it will run the query but insert a Null value.

The names don't have any special characters in them, since they are the secured.mdw names (stuff like Bob, Pat, Chuck), so they should be passing okay.

 

[pat_nospam]

LOL - Okay, after changing the receiving field from number to text, I'm making some progress (slaps forehead, DOH!).

But now, it's inserting "CurrentUser()" as straight text and not substituting the actual function value for it. Is there SQL syntax to execute a function previous to insertion?

The other question I have is that there is a visible message that comes up (You are about to append on row, Yes/No). I would like the DoCmd.RunSQL to run automatically behind the scenes without user intervention.

Thanks again !

 

[pat_nospam]

Okay - It's working, the SQL is finally working.

However, I would still like to remove the user prompted message that pops up when the DoCmd.RunSQL is executed. Any ideas?

Thank you so much, your help has been invaluable!!

 

[Mile-O]

However, I would still like to remove the user prompted message that pops up when the DoCmd.RunSQL is executed.

With DoCmd
    .SetWarnings False
    .RunSQL [i]strSQL[/i]
    .SetWarnings True
End With