CODEWITHSUNDEEP
vbams-access-2013with-statement
erasmo carlos
erasmo carlos

Reputation: 682

VBA: creating multiple parameters inside with/ end with statement

I have code that works with no errors. My question is more out of wanting to learn the syntax of a With/ End With statement that can include multiple parameters. Right now a new ADODB.Command is set and inside a With/ End With the ActiveConnection, the commandType, and the CommandText are included. Then outside the With 5 parameters are created, appended and assigned.

I was wondering if those parameters can also be inside the With/ End With of the ADODB.Command.

Here is my code:

Set ADOQD = New ADODB.Command
    With ADOQD
        .ActiveConnection = ADOCon
        .CommandType = adCmdStoredProc
        .CommandText = "jsp_AddFeedback"
    End With

Set pArrangementID = CreateParameter("@ArrangementID", adVarChar, adParamInput, 15)
ADOQD.Parameters.Append pArrangementID
pArrangementID = Forms("MTDDataCheck").ArrangementID.Value

Set pEditor = ADOQD.CreateParameter("@Editor", adVarWChar, adParamInput, 20)
ADOQD.Parameters.Append pEditor
pEditor = gstrLastEditor

Set pProofer = ADOQD.CreateParameter("@Proofer", adVarWChar, adParamInput, 20)
ADOQD.Parameters.Append pProofer
pProofer = gstrLastProofer

Set pControlName = ADOQD.CreateParameter("@ControlName", adVarWChar, adParamInput, 255)
ADOQD.Parameters.Append pControlName
pControlName = lbl.name

Set pComment = ADOQD.CreateParameter("@Comment", adLongVarWChar, adParamInput, -1)
ADOQD.Parameters.Append pComment
pComment = reply

Here is my attempt:

    Set ADOQD = New ADODB.Command
With ADOQD
    .ActiveConnection = ADOCon
    .CommandType = adCmdStoredProc
    .CommandText = "jsp_DeleteFeedback"
    Set pArrangementID = .CreateParameter("@ArrangementID", adVarChar, adParamInput, 15)
    .Parameters.Append pArrangementID
    pArrangementID = Forms("MTDDataCheck").ArrangementID.Value
    Set pControlName = .CreateParameter("@ControlName", adVarWChar, adParamInput, 255)
    .Parameters.Append pControlName
    pControlName = lbl.name
    .Execute
End With

Is this the correct way to do it? Any examples are much appreciated.

Thank you.

Code After getting response:

Set ADOQD = New ADODB.Command
With ADOQD
    Set .ActiveConnection = ADOCon
    .CommandType = adCmdStoredProc
    .CommandText = "jsp_AddFeedback"
    .Parameters.Append .CreateParameter("@ArrangementID", adVarChar, adParamInput, 15, Forms("MTDDataCheck").ArrangementID.Value)
    .Parameters.Append .CreateParameter("@Editor", adVarWChar, adParamInput, 20, gstrLastEditor)
    .Parameters.Append .CreateParameter("@Proofer", adVarWChar, adParamInput, 20, gstrLastProofer)
    .Parameters.Append .CreateParameter("@ControlName", adVarWChar, adParamInput, 255, lbl.name)
    .Parameters.Append .CreateParameter("@Comment", adLongVarWChar, adParamInput, -1, reply)
    .Execute
End With

Upvotes: 0

Views: 815

Answers (1)

Alex K.
Alex K.

Reputation: 175766

I would have thought

ADOQD.Parameters.Append pArrangementID
pArrangementID = Forms("MTDDataCheck").ArrangementID.Value

Would actually fail or replace the in-collection parameter with a string ...

Doesn't matter though as you don't need to explicitly create a parameter, supply the value along whith everything else:

Set ADOQD = New ADODB.Command
With ADOQD
    SET .ActiveConnection = ADOCon
    .CommandType = adCmdStoredProc
    .CommandText = "jsp_AddFeedback"

    .Parameters.Append .CreateParameter("@ArrangementID", adVarChar, adParamInput, 15, Forms("MTDDataCheck").ArrangementID.Value)
    .Parameters.Append .CreateParameter("@Editor", adVarWChar, adParamInput, 20, gstrLastEditor)
    ...
End With

Upvotes: 1

Related Questions