Parameter error whith CurrentDb.Execute

[shylock]

Thank you for your patience. I understand the concept of delimiters, having been a C programmer. I know that if you get the delimiters "unbalanced" you ill have problems. What I don't understand is why the following works and the query with delimters has problems?

This works:

SqlQry = "INSERT INTO DowntimeEventNew ([Begin Date], [Begin Time], [End Date], [End Time], [Description], Planned, ReportedBy, EnteredBy, ServerName, UsePhysicalServer, Services)" _
& "VALUES (CDate([txtBeginDate]), CDate([txtBeginTime]), CDate([txtEndDate]), CDate([txtEndTime]), [txtDescription], [cboPlanned], [txtReportedBy], [txtEnteredBy], [cboServerNm], [cboUsePhysicalServer], [txtServices]);"

DoCmd.RunSQL SqlQry

I do get the following error, although it does save the record correctly: Microsoft Access set 1 field(s) to Null due to a type conversion failure, ...

[Minty]

When you use Docmd you are invoking the access interpreter, and it understands and recognises all the form control references, and makes allowances for the datatypes as a result.

When you use CurrentDb.Execute you are bypassing the Access interpreter and passing the SQL as a True SQL query to the database (Jet?) engine.
It has no idea what the form controls or data types are so you have to explicitly make sure they are passed correctly.

The second method will almost certainly run quicker and is more portable, but there is some pain involved in creating the query.

[rpeare]

May I offer a suggestion.

Define the parameters in your SQL string (or query)

for every field that references something on a form [FIELDNAME] as an example

for instance if you were trying to append the field contents from [FIELDNAME] on your form [MYFORM]

you would have a sql statement like:

PARAMETERS [Forms]![MYFORM]![FieldName] DateTime;
INSERT INTO tblDocumentSigner ( DS_Date )
SELECT [Forms]![MYFORM]![fieldname] AS Expr1;

Sometimes Access can't interpret whether a text box is meant to be a date, a number, text and will throw that parameter error, if you define the field in your parameters it should work fine.

[shylock]

Micron:

Thanks for your suggestions. U R right, it was more educational to work it out by myself. I just needed to be pointed in the right direction. I finally got it worked out after many, many false starts. But I now do not get any dat mis-match errors. Thanks, again.