Required Parameters for a command object

[Jenni]

Hi, my simple solution is not so simple. I have done a bit of VB
programming, and am now tasked with trying to make access work using
VBA, and battling my butt off. This probably has a really simple
solution, which right now eludes me.

The code is below, and the error message I get is this "No Value given
for one or more required parameters". sClinic is picking up the string
from a combo box, and this does work cos the message box tosses out
the right value.

Private Sub cmdUpdateReport_Click()
On Error GoTo Err_cmdUpdateReport_Click

Dim stDocName As String
Dim AppCmd1 As ADODB.Command

stDocName = "Macro1"
DoCmd.RunMacro stDocName

On Error GoTo Err_cmdUpdateReport_Click

Set AppCmd1 = New ADODB.Command
AppCmd1.ActiveConnection = CurrentProject.Connection
AppCmd1.CommandType = adCmdText
AppCmd1.CommandText = _
"INSERT INTO New_Complete_Data (Facility_Code, ColA, ColB,
ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL, ColM,
ColN, ColO, ColP, ColQ, ColR)" & _
"SELECT M1C.Facility_Code AS Facility_Code, M1C.C11 AS ColA,
M1C.C12 AS ColB, M1C.C14 AS ColC, M1C.C32, M1C.C43, M1C.C51, M1C.C52,
M1C.C53, M1C.C61, M1C.C71, M1C.C72 AS ColK, M1C.SumDenC16 AS ColL,
M1C.SumC16 AS ColM, M1C.SumDenC82 AS ColN, M1C.SumC82 AS ColO, M1C.C84
AS ColP, M1C.C91 AS ColQ, M1C.C102 " & _
"FROM M1C WHERE M1C.Facility_Code = " & sClinic & " "
MsgBox ("The current clinic code is " & sClinic & ""), vbOKOnly

AppCmd1.Execute

Set AppCmd1 = Nothing

Exit_cmdUpdateReport_Click:
Exit Sub

Err_cmdUpdateReport_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateReport_Click

End Sub

I really appreciate the help.
Jenni

[Fletcher Arnold]

"Jenni" <jr*******@khulisa.com> wrote in message
news:db*************************@posting.google.co m...

Hi, my simple solution is not so simple. I have done a bit of VB
programming, and am now tasked with trying to make access work using
VBA, and battling my butt off. This probably has a really simple
solution, which right now eludes me.

The code is below, and the error message I get is this "No Value given
for one or more required parameters". sClinic is picking up the string
from a combo box, and this does work cos the message box tosses out
the right value.

Private Sub cmdUpdateReport_Click()
On Error GoTo Err_cmdUpdateReport_Click

Dim stDocName As String
Dim AppCmd1 As ADODB.Command

stDocName = "Macro1"
DoCmd.RunMacro stDocName

On Error GoTo Err_cmdUpdateReport_Click

Set AppCmd1 = New ADODB.Command
AppCmd1.ActiveConnection = CurrentProject.Connection
AppCmd1.CommandType = adCmdText
AppCmd1.CommandText = _
"INSERT INTO New_Complete_Data (Facility_Code, ColA, ColB,
ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL, ColM,
ColN, ColO, ColP, ColQ, ColR)" & _
"SELECT M1C.Facility_Code AS Facility_Code, M1C.C11 AS ColA,
M1C.C12 AS ColB, M1C.C14 AS ColC, M1C.C32, M1C.C43, M1C.C51, M1C.C52,
M1C.C53, M1C.C61, M1C.C71, M1C.C72 AS ColK, M1C.SumDenC16 AS ColL,
M1C.SumC16 AS ColM, M1C.SumDenC82 AS ColN, M1C.SumC82 AS ColO, M1C.C84
AS ColP, M1C.C91 AS ColQ, M1C.C102 " & _
"FROM M1C WHERE M1C.Facility_Code = " & sClinic & " "
MsgBox ("The current clinic code is " & sClinic & ""), vbOKOnly

AppCmd1.Execute

Set AppCmd1 = Nothing

Exit_cmdUpdateReport_Click:
Exit Sub

Err_cmdUpdateReport_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateReport_Click

End Sub

I really appreciate the help.
Jenni

1) If this code is failing, why not replace the line AppCmd1.Execute with
Debug.Print AppCmd1.CommandText temporarily so that you can see the SQL
string which it is trying to execute.

2) This should turn up a missing space here ...ColR)SELECT M1C.Facility_Code
AS...

3) There are also no quotes for the string value WHERE M1C.Facility_Code =
MyClinic instead of WHERE M1C.Facility_Code = "MyClinic"

4) Why is sClinic not dimmed? Please don't say you do not use "Option
Explicit"

5) Other, less important points follow:

**Error Handling:
Why is "On Error GoTo Err_cmdUpdateReport_Click" in there twice?

Why don't you label your error handlers with more standard names? Did you
get a wizard to write the code? Wouldn't "On Error GoTo Err_Handler" be
easier than "On Error GoTo Err_cmdUpdateReport_Click"? Not only is it more
readable, but more transferable and less weird if you rename controls - I
would write it like this:

Private Sub cmdUpdateReport_Click()

On Error GoTo Err_Handler

MsgBox "Hi"

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
**Object library
Any special reason you are using ADO code rather than DAO?
** Macros?
DoCmd.RunMacro stDocName
Not many people here use macros - they are simply too limiting

[Pieter Linden]

Jenni,
looks like you forgot to put quotes around your variable... I think this line:

"FROM M1C WHERE M1C.Facility_Code = " & sClinic & " "
should be "FROM M1C WHERE M1C.Facility_Code = '" & sClinic & "' "

so when the select statement compiles, it looks like this:

....FROM MIC WHERE M1C.Facility_Code = 'Some Value'

Try that and see if that fixes it.

[Jenni]

The string problem solution makes it work, Thanks a million. In
response to the other questions though

1. sClinnic is dimmed as a Private variable at the form level cos it
is picked up in the Lost_Focus event of the combo box.

2. The space thing is not a problem

3. Didn't try the debug.print thing, but will keep it in mind for
future use.

4. Yes a wizard wrote the initial code. I'm new at this, so the wizard
makes it a little easier for me.

5. I use the macro's as it is an inherited project and the macro's do
90% of the work already.

Thanks again. You folks are an absolute fountain of knowledge and
inspiration. I hope I can someone else sometime.

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...

Jenni,
looks like you forgot to put quotes around your variable... I think this line:

"FROM M1C WHERE M1C.Facility_Code = " & sClinic & " "

should be

"FROM M1C WHERE M1C.Facility_Code = '" & sClinic & "' "

so when the select statement compiles, it looks like this:

...FROM MIC WHERE M1C.Facility_Code = 'Some Value'

Try that and see if that fixes it.