Parameter error whith CurrentDb.Execute

[shylock]

Hi, again.



I have an append query that I am getting a parameter error on. (Shown Below).

The query is : 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]);"

CurrentDb.Execute SqlQry, dbFailOnError

This gives the following error:


However, when I use DoCmd.RunSQL SqlQry, I don't receive an error and the record is correctly appended to the table. Any suggestions?

[moke123]

I believe you need to have the form field references outside the string. You also need to add delimiters depending on the datatype.
I delimited the date fields below but you'll need to add delimiters to any text fields.

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] & ");"

[Minty]

In addition to Mokes excellent answer, Description is a reserved word so it may well be worth using [Description] in the field list.

[ranman256]

You can also use a query,
or
docmd.runSql SqlQry

[shylock]

moke123:

Thanks for the reply. I copied and pasted the code you provided. There seems to be syntax error. I have been playing with the syntax and can't seem to find it.

[shylock]

ranman256:

I used your suggestion, originally and it does work but I receive the error: "Microsoft Access can't append all the records in the query. Microsoft Accesss set 1 field9s0 to Null due to a type conversion failure, and ... Do you want to run this query anyway? ... Yes, No, help" If I click "Yes" the query runs fine and saves the record w/o Nulls in any field. I want to be able to suppress this error. The CurrentDb.Execute method is supposed to suppress the error but, as you can see from my original post, it spawns a different error. Any suggestions?

[davegri]

Thanks for the reply. I copied and pasted the code you provided. There seems to be syntax error. I have been playing with the syntax and can't seem to find it.

Here's one syntax error; need space
UsePhysicalServer, Services) " & "VALUES ( #" & cdate([txtBegin

[shylock]

Went over my syntax for the query. Here is the final query as shown in the immediate window while debugginh:

INSERT INTO DowntimeEventNew ([Begin Date], [Begin Time], [End Date], [End Time], [Description], Planned, ReportedBy, EnteredBy, ServerName, UsePhysicalServer, Services) VALUES (#11/28/2018#, #12:00:00 PM#, #11/28/2018#, #12:30:00 PM#, test, True, JRW, Jack, Angular, False, AngularJS);

I now get this error: Run-time error: '3061': Too few parameters. Expected 5.

Any ideas?

[Micron]

As per post 2, your text values need to be delimited as are your dates: 'test', True, 'JRW', 'Jack', 'Angular', False, 'AngularJS'
I'm assuming true and false are not text.
When you fix this, I suspect you will be back to the write conflict error. A common oversight with that one is that it's a generic message listing several reasons why records cannot be appended or updated. For the ones that don't apply, the number of problem records is reported as '0'. Don't leave out the relevant reasons if you post back about that.

EDIT
If you're not understanding, post the new sql because I'm seeing that you've gone from 11 fields to 5.

[davegri]

It should look like this:
The 5 parameters referred to need the single quotes.
Adjust your VBA to get this in the IW.

INSERT INTO DowntimeEventNew ([Begin Date], [Begin Time], [End Date], [End Time], [Description], Planned, ReportedBy, EnteredBy, ServerName, UsePhysicalServer, Services) VALUES (#11/28/2018#, #12:00:00 PM#, #11/28/2018#, #12:30:00 PM#, 'test', True, 'JRW', 'Jack', 'Angular', False, 'AngularJS');

[shylock]

Micron:

Here is the "New" SQL:

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] & ");"
CurrentDb.Execute SqlQry, dbFailOnError

As you can see there are, in fact 11 fields, not 5.

When I run the query and look at the contents of SqlQry in the immediate window this is what I see:

INSERT INTO DowntimeEventNew ([Begin Date], [Begin Time], [End Date], [End Time], [Description], Planned, ReportedBy, EnteredBy, ServerName, UsePhysicalServer, Services) VALUES (#11/28/2018#, #12:00:00 PM#, #11/28/2018#, #12:30:00 PM#, test, True, JRW, Jack, Angular, False, AngularJS);

I'm not understanding what you men by "your text values need to be delimited as are your dates: 'test', True, 'JRW', 'Jack', 'Angular', False, 'AngularJS'"

[shylock]

Davegri:

The 5 parameters you refer to are the contents of text boxes. How can I put single quotes around them? This is my SQL:

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

[Micron]

Micron:

Here is the "New" SQL:

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] & ");"
CurrentDb.Execute SqlQry, dbFailOnError

As you can see there are, in fact 11 fields, not 5.

When I run the query and look at the contents of SqlQry in the immediate window this is what I see:

INSERT INTO DowntimeEventNew ([Begin Date], [Begin Time], [End Date], [End Time], [Description], Planned, ReportedBy, EnteredBy, ServerName, UsePhysicalServer, Services) VALUES (#11/28/2018#, #12:00:00 PM#, #11/28/2018#, #12:30:00 PM#, test, True, JRW, Jack, Angular, False, AngularJS);

I'm not understanding what you men by "your text values need to be delimited as are your dates: 'test', True, 'JRW', 'Jack', 'Angular', False, 'AngularJS'"

Probably not obvious to you, but post 2 explains it. For example, this #" & CDate([txtBeginDate]) & "# becomes a date like
#01/01/2018#
The #'s are date delimiters. ' and " are text delimiters. Since your vba expression has to start and end with ", it's common to use singles quotes within those, as in
...' " & [txtReportedBy] & " ', ' " & [txtEnteredBy] & " ',...

I have added spaces and formatting so you could identify the delimiters easier. You would not use those extra spaces. I also thought you might benefit more from trying yourself rather than simply doing it all as it can be difficult to grasp at first and requires a bit of practice. I still get it wrong from time to time.
Note that on the left, the last character in the concatenation is '. Then comes your text field value. Then it resumes with a ',' followed by your next text field value.
The result is ...'Text', 'Text2',... (where I've removed the spaces to indicate the desired result).
You don't delimit number fields.

[davegri]

Maybe it's easier to see this way, with discrete quotes.

" VALUES (" & "#" & CDate([txtBeginDate]) & "#" & ", " & "#" & CDate([txtBeginTime]) & "#" & ", " & "#" & CDate([txtEndDate]) & "#" & ", " & "#" & CDate([txtEndTime]) & "#" & ", " & "'" & [txtDescription] & "'" & ", " & [cboPlanned] & ", " & "'" & [txtReportedBy] & "'" & ", " & "'" & [txtEnteredBy] & "'" & ", " & "'" & [cboServerNm] & "'" & ", " & [cboUsePhysicalServer] & ", " & "'" & [txtServices] & "'" & ");"

Also a single quote is represented by Chr$(39)
SO
" VALUES (" & "#" & CDate([txtBeginDate]) & "#" & ", " & "#" & CDate([txtBeginTime]) & "#" & ", " & "#" & CDate([txtEndDate]) & "#" & ", " & "#" & CDate([txtEndTime]) & "#" & ", " & Chr$(39) & [txtDescription] & Chr$(39) & ", " & [cboPlanned] & ", " & Chr$(39) & [txtReportedBy] & Chr$(39) & ", " & Chr$(39) & [txtEnteredBy] & Chr$(39) & ", " & Chr$(39) & [cboServerNm] & Chr$(39) & ", " & [cboUsePhysicalServer] & ", " & Chr$(39) & [txtServices] & Chr$(39) & ");"

cboPlanned and cboUsePhysicalServer are boolean, so require no delimiters.

[moke123]

Delimeters can be a real P.I.T.A. especially when you are first learning to use them.
As suggested using a single quote ' is used a lot but, if for instance, you have a name like O'Malley you will get an error due to the single quote and will need to use a double quote.

May I suggest you develop some helper functions like below.

Public Function txtDelim(strIN as string) as string

     txtDelim = chr(34) & strIN & chr(34)

End Function

then instead of .... '" & [Some text Field] & "' ....
you would use ... & txtDelim([Some Text Field]) & ...
you can do the same for date delimiters.

you should also always insert a debug.Print SqlQry before the .execute line so you can inspect the result of the sql.

btw, heres a list of the chr() codes or Ascii codes as they are known as ...https://www.atwebresults.com/ascii-codes.php?type=2