I am using an Access Project and I want to be able to open the report without that input box appearing and asking for the parameter
value. I created code, but apparantly the parameter value is not going through. Here is the code I use and I will also supply the
stored procedure syntax I use.



Any help would be appreciated.



Private Sub Report_Open(Cancel As Integer)

On Error Resume Next

Dim objConn As New ADODB.Connection

Dim objCmd As New ADODB.Command

Dim objParm As ADODB.Parameter

'establish a connection



Set objConn = CurrentProject.Connection     



'set the Command object properties

objCmd.ActiveConnection = objConn   



objCmd.CommandText = "up_selProdCode"

objCmd.CommandType = adCmdStoredProc

Set objParm = objCmd.CreateParameter("PC", adVarChar, adParamInput, 255, "PAL - 118 - 1500 - Q - DE - RC - IY - AB - 120 - None") 



objCmd.Parameters.Append objParm

Set objCmd = Nothing

objConn.Close

Set objConn = Nothing

End Sub



Here is the stored procedure:



Alter Procedure up_selProdCode (@PC nvarchar(255))



 



As



SELECT tbl_cfgProductCodes.ProductCode, tbl_cfgProductCodes.LightTypeID, tbl_ColumnNames.ColumnName AS C1, tbl_Column01.Description
AS D1, tbl_ColumnNames_1.ColumnName AS C2, tbl_Column02.Description AS D2, tbl_ColumnNames_2.ColumnName AS C3,
tbl_Column03.Description AS D3, tbl_ColumnNames_3.ColumnName AS C4, tbl_Column04.Description AS D4, tbl_ColumnNames_4.ColumnName AS
C5, tbl_Column05.Description AS D5, tbl_ColumnNames_5.ColumnName AS C6, tbl_Column06.Description AS D6, tbl_ColumnNames_6.ColumnName
AS C7, tbl_Column07.Description AS D7, tbl_ColumnNames_7.ColumnName AS C8, tbl_Column08.Description AS D8,
tbl_ColumnNames_8.ColumnName AS C9, tbl_Column09.Description AS D9, tbl_ColumnNames_9.ColumnName AS C10, tbl_Column10.Description AS
D10, tbl_ColumnNames_10.ColumnName AS C11, tbl_Column11.Description AS D11



 



FROM tbl_ColumnNames AS tbl_ColumnNames_10 RIGHT JOIN (tbl_ColumnNames AS tbl_ColumnNames_9 RIGHT JOIN (tbl_ColumnNames AS
tbl_ColumnNames_8 RIGHT JOIN (tbl_ColumnNames AS tbl_ColumnNames_7 RIGHT JOIN (tbl_ColumnNames AS tbl_ColumnNames_6 RIGHT JOIN
(tbl_ColumnNames AS tbl_ColumnNames_5 RIGHT JOIN (tbl_ColumnNames AS tbl_ColumnNames_4 RIGHT JOIN (tbl_ColumnNames AS
tbl_ColumnNames_3 RIGHT JOIN (tbl_ColumnNames AS tbl_ColumnNames_2 RIGHT JOIN (tbl_ColumnNames AS tbl_ColumnNames_1 RIGHT JOIN
(tbl_ColumnNames RIGHT JOIN (((((((((((tbl_cfgProductCodes LEFT JOIN tbl_Column01 ON tbl_cfgProductCodes.Column01Abrv =
tbl_Column01.Column01Abrv) LEFT JOIN tbl_Column02 ON tbl_cfgProductCodes.Column02Abrv = tbl_Column02.Column02Abrv) LEFT JOIN
tbl_Column03 ON tbl_cfgProductCodes.Column03Abrv = tbl_Column03.Column03Abrv) LEFT JOIN tbl_Column04 ON
tbl_cfgProductCodes.Column04Abrv = tbl_Column04.Column04Abrv) LEFT JOIN tbl_Column05 ON tbl_cfgProductCodes.Column05Abrv =
tbl_Column05.Column05Abrv) LEFT JOIN tbl_Column06 ON tbl_cfgProductCodes.Column06Abrv = tbl_Column06.Column06Abrv) LEFT JOIN
tbl_Column07 ON tbl_cfgProductCodes.Column07Abrv = tbl_Column07.Column07Abrv) LEFT JOIN tbl_Column08 ON
tbl_cfgProductCodes.Column08Abrv = tbl_Column08.Column08Abrv) LEFT JOIN tbl_Column09 ON tbl_cfgProductCodes.Column09Abrv =
tbl_Column09.Column09Abrv) LEFT JOIN tbl_Column10 ON tbl_cfgProductCodes.Column10Abrv = tbl_Column10.Column10Abrv) LEFT JOIN
tbl_Column11 ON tbl_cfgProductCodes.Column11Abrv = tbl_Column11.Column11Abrv) ON tbl_ColumnNames.ColumnNameID =
tbl_Column01.ColumnNameID) ON tbl_ColumnNames_1.ColumnNameID = tbl_Column02.ColumnNameID) ON tbl_ColumnNames_2.ColumnNameID =
tbl_Column03.ColumnNameID) ON tbl_ColumnNames_3.ColumnNameID = tbl_Column04.ColumnNameID) ON tbl_ColumnNames_4.ColumnNameID =
tbl_Column05.ColumnNameID) ON tbl_ColumnNames_5.ColumnNameID = tbl_Column06.ColumnNameID) ON tbl_ColumnNames_6.ColumnNameID =
tbl_Column07.ColumnNameID) ON tbl_ColumnNames_7.ColumnNameID = tbl_Column08.ColumnNameID) ON tbl_ColumnNames_8.ColumnNameID =
tbl_Column09.ColumnNameID) ON tbl_ColumnNames_9.ColumnNameID = tbl_Column10.ColumnNameID) ON tbl_ColumnNames_10.ColumnNameID =
tbl_Column11.ColumnNameID



 



WHERE (tbl_cfgProductCodes.ProductCode = @PC)

hi Mavin



You seem to be creating your command object, adding a parameter to it, but 

hen destroying the command object before you've done anything with it. Try 

this (I'm sure that it would work for a form, but reports can be tricky 

things...)



 Private Sub Report_Open(Cancel As Integer)

 On Error Resume Next

 Dim objConn As New ADODB.Connection

 Dim objCmd As New ADODB.Command

 Dim objParm As ADODB.Parameter

 'establish a connection

 

 Set objConn = CurrentProject.Connection     

 

 'set the Command object properties

 objCmd.ActiveConnection = objConn   

 

 objCmd.CommandText = "up_selProdCode"

 objCmd.CommandType = adCmdStoredProc

 Set objParm = objCmd.CreateParameter("PC", adVarChar, adParamInput, 

255, "PAL - 118 - 1500 - Q - DE - RC - IY - AB - 120 - None") 

 

 objCmd.Parameters.Append objParm



 'NEW CODE HERE

 Set Me.RecordSource = objCmd.Execute



 Set objCmd = Nothing

 objConn.Close

 Set objConn = Nothing

 End Sub





> 

> 

> 

> 

> I am using an Access Project and I want to be able to open the report 

without that input box appearing and asking for the parameter value. I 

created code, but apparantly the parameter value is not going through. 

Here is the code I use and I will also supply the stored procedure syntax 

I use.

> 

> Any help would be appreciated.

> 

> Private Sub Report_Open(Cancel As Integer)

> On Error Resume Next

> Dim objConn As New ADODB.Connection

> Dim objCmd As New ADODB.Command

> Dim objParm As ADODB.Parameter

> 'establish a connection

> 

> Set objConn = CurrentProject.Connection     

> 

> 'set the Command object properties

> objCmd.ActiveConnection = objConn   

> 

> objCmd.CommandText = "up_selProdCode"

> objCmd.CommandType = adCmdStoredProc

> Set objParm = objCmd.CreateParameter("PC", adVarChar, adParamInput, 

255, "PAL - 118 - 1500 - Q - DE - RC - IY - AB - 120 - None") 

> 

> objCmd.Parameters.Append objParm

> Set objCmd = Nothing

> objConn.Close

> Set objConn = Nothing

> End Sub

> 

> Here is the stored procedure:

> 

> Alter Procedure up_selProdCode (@PC nvarchar(255))

> 

>  

> 

> As

> 

> SELECT tbl_cfgProductCodes.ProductCode, tbl_cfgProductCodes.LightTypeID, 

tbl_ColumnNames.ColumnName AS C1, tbl_Column01.Description AS D1, 

tbl_ColumnNames_1.ColumnName AS C2, tbl_Column02.Description AS D2, 

tbl_ColumnNames_2.ColumnName AS C3, tbl_Column03.Description AS D3, 

tbl_ColumnNames_3.ColumnName AS C4, tbl_Column04.Description AS D4, 

tbl_ColumnNames_4.ColumnName AS C5, tbl_Column05.Description AS D5, 

tbl_ColumnNames_5.ColumnName AS C6, tbl_Column06.Description AS D6, 

tbl_ColumnNames_6.ColumnName AS C7, tbl_Column07.Description AS D7, 

tbl_ColumnNames_7.ColumnName AS C8, tbl_Column08.Description AS D8, 

tbl_ColumnNames_8.ColumnName AS C9, tbl_Column09.Description AS D9, 

tbl_ColumnNames_9.ColumnName AS C10, tbl_Column10.Description AS D10, 

tbl_ColumnNames_10.ColumnName AS C11, tbl_Column11.Description AS D11

> 

>  

> 

> FROM tbl_ColumnNames AS tbl_ColumnNames_10 RIGHT JOIN (tbl_ColumnNames 

AS tbl_ColumnNames_9 RIGHT JOIN (tbl_ColumnNames AS tbl_ColumnNames_8 

RIGHT JOIN (tbl_ColumnNames AS tbl_ColumnNames_7 RIGHT JOIN 

(tbl_ColumnNames AS tbl_ColumnNames_6 RIGHT JOIN (tbl_ColumnNames AS 

tbl_ColumnNames_5 RIGHT JOIN (tbl_ColumnNames AS tbl_ColumnNames_4 RIGHT 

JOIN (tbl_ColumnNames AS tbl_ColumnNames_3 RIGHT JOIN (tbl_ColumnNames AS 

tbl_ColumnNames_2 RIGHT JOIN (tbl_ColumnNames AS tbl_ColumnNames_1 RIGHT 

JOIN (tbl_ColumnNames RIGHT JOIN (((((((((((tbl_cfgProductCodes LEFT JOIN 

tbl_Column01 ON tbl_cfgProductCodes.Column01Abrv = 

tbl_Column01.Column01Abrv) LEFT JOIN tbl_Column02 ON 

tbl_cfgProductCodes.Column02Abrv = tbl_Column02.Column02Abrv) LEFT JOIN 

tbl_Column03 ON tbl_cfgProductCodes.Column03Abrv = 

tbl_Column03.Column03Abrv) LEFT JOIN tbl_Column04 ON 

tbl_cfgProductCodes.Column04Abrv = tbl_Column04.Column04Abrv) LEFT JOIN 

tbl_Column05 ON tbl_cfgProductCodes.Column05Abrv = 

tbl_Column05.Column05Abrv) LEFT JOIN tbl_Column06 ON 

tbl_cfgProductCodes.Column06Abrv = tbl_Column06.Column06Abrv) LEFT JOIN 

tbl_Column07 ON tbl_cfgProductCodes.Column07Abrv = 

tbl_Column07.Column07Abrv) LEFT JOIN tbl_Column08 ON 

tbl_cfgProductCodes.Column08Abrv = tbl_Column08.Column08Abrv) LEFT JOIN 

tbl_Column09 ON tbl_cfgProductCodes.Column09Abrv = 

tbl_Column09.Column09Abrv) LEFT JOIN tbl_Column10 ON 

tbl_cfgProductCodes.Column10Abrv = tbl_Column10.Column10Abrv) LEFT JOIN 

tbl_Column11 ON tbl_cfgProductCodes.Column11Abrv = 

tbl_Column11.Column11Abrv) ON tbl_ColumnNames.ColumnNameID = 

tbl_Column01.ColumnNameID) ON tbl_ColumnNames_1.ColumnNameID = 

tbl_Column02.ColumnNameID) ON tbl_ColumnNames_2.ColumnNameID = 

tbl_Column03.ColumnNameID) ON tbl_ColumnNames_3.ColumnNameID = 

tbl_Column04.ColumnNameID) ON tbl_ColumnNames_4.ColumnNameID = 

tbl_Column05.ColumnNameID) ON tbl_ColumnNames_5.ColumnNameID = 

tbl_Column06.ColumnNameID) ON tbl_ColumnNames_6.ColumnNameID = 

tbl_Column07.ColumnNameID) ON tbl_ColumnNames_7.ColumnNameID = 

tbl_Column08.ColumnNameID) ON tbl_ColumnNames_8.ColumnNameID = 

tbl_Column09.ColumnNameID) ON tbl_ColumnNames_9.ColumnNameID = 

tbl_Column10.ColumnNameID) ON tbl_ColumnNames_10.ColumnNameID = 

tbl_Column11.ColumnNameID

> 

>  

> 

> WHERE (tbl_cfgProductCodes.ProductCode = @PC)

>