Simple 'Insert Into'

[gfcaim]

Can someone help me with this syntax problem...

dim D$
D=Date
Insert INTO tbl_AutomatedEmail VALUES (D); <--access dont like this!

the table in question only has one text field to hold the inserted date.

 

[scottn]

first, it would probably be a better idea to use a real date field in your table, rather than a text field. also, you can't use straight SQL in the middle of your VB code -- you have to concatenate your SQL together into a string and then send the string as a command to the database. there's plenty of ways to do that... here's one quick way:

Dim D as Date
D=Date
CurrentProject.Connection.Execute "Insert INTO tbl_AutomatedEmail (FieldName) VALUES (#" & D & "#)"

note that you have to surround your date value with the # delimiter. also, just by the way, it's a good idea when composing INSERT INTO statements to always specify the fields you're writing to, even if you don't strictly need to. this way, if the table structure changes later, the statement will still be valid.

 

[gfcaim]

Thanks a lot for your reply - i have altered my work as you recommend.

 

[gfcaim]

Er... one last thing - how do i supppress the confirmation message?

 

[Mod]

DoCmd.SetWarnings False
CurrentProject.Connection.Execute SqlStr
DoCmd.SetWarnings True