VBA / SQL Syntax error 1

[Stevehewitt]

Hi Everyone,

I've come a little stuck. I've got a table called tblTransLog with 5 fields:

AuditID(auto no.), OrderRef, EventDesc, DateTime, Username

In this particular example, there is no OrderRef so it can be null. Also as AuditID is an autonumber I guess I can leave it alone and not try to put any input in. Oh, all fields are text (at the moment)

Code i've done is:

strsql = "Insert into tblTransactionLog (AuditID, OrderRef, EventDesc, DateTime, Username) values ("", "", "Global settings updated", #" & Now() & "#, '" & Environ("UserName") & "')"

However I keep on getting a syntax error.

Any ideas?

Cheers,




Steve.

"They have the internet on computers now!" - Homer Simpson

 

[snyperx3]

try

strsql = "Insert into tblTransactionLog (EventDesc, DateTime, Username) values ('Global settings updated', '" & Now() & "', '" & Environ("UserName") & "')"

i fixed some quotation placements, removed the fields that are going to either be null or have an autonumber, and i removed the surrounding #'s from Now() since you said all fields are text.

-Pete

 

[Stevehewitt]

Hi Pete,

Thanks for your help and quick reply.

Just tried the code again and same error - syntax error. (What a useless error - at least give me something to work on rather than a error for the whole sodding statement!)

Any other ideas appreciated!

Cheers




Steve.

"They have the internet on computers now!" - Homer Simpson

 

[snyperx3]

it appears to not like "DateTime"

I tried doing this...and it accepted it for me:

strsql = "Insert into tblTransactionLog (EventDesc, [DateTime], Username) values ('Global settings updated', '" & Now() & "', '" & Environ("UserName") & "')"

-Pete

 

[Stevehewitt]

Hi Pete,

That's perfect - thank you.

Thanks again, really is appreciated!

Cheers,




Steve.

"They have the internet on computers now!" - Homer Simpson

 

[abaldwin]

FYI DateTime would not be a good field name since it is a data type in Access and could be considered a reserved (even though truely it is not.) word. Just for sake of future queries etc I would suggest you rename the field to EventTime. Any time you name a field with a special word such as Date tor Time or DateTime etc or putting spaces in your field namesyou can always get around it by using the [] brackets.

Later

Andy Baldwin

"Testing is the most overlooked programming language on the books!