Subform Problem
Apr 10, 2006The question I have is a bit complicated but I'm hoping someone can help me out.
I am using SQL Express as the back end database, connecting through MS Access and using VBA to do the code. I have created a project in Access and in it I have a form in which I created a subform. The subform has a stored procedure as it's record source, the problem is that in order to keep the data in the subform in sync with the data in the form the stored procedure needs a parameter to retrieve the correct data. How does one pass that parameter as well as the stored procedure on the record source setting?
I have been able to do combolist population, or data retrieval using stored procedure but it doesn't seem to be working with populating the subform.
'create a connection to database
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn
'Populate the PO Number text box
Dim cmdUnitPrice As ADODB.Command
Dim prmUnitPrice As ADODB.Parameter
Dim rstUnitPrice As ADODB.Recordset
Dim strSQLUnitPrice As String
'Define a command object to call stored procedure
Set cmdUnitPrice = New ADODB.Command
Set cmdUnitPrice.ActiveConnection = Cnxn
'set the criteria to the stored procedure called
strSQLUnitPrice = "sp_GetUnitPrice"
cmdUnitPrice.CommandText = strSQLUnitPrice
cmdUnitPrice.CommandType = adCmdStoredProc
cmdUnitPrice.CommandTimeout = 15
'define the stored procedure's input parameter
Set prmUnitPrice = New ADODB.Parameter
prmUnitPrice.Type = adInteger
prmUnitPrice.Size = 3
prmUnitPrice.Direction = adParamInput
prmUnitPrice.Value = intProdID
cmdUnitPrice.Parameters.Append prmUnitPrice
'create a recordset by executing the command
Set rstUnitPrice = cmdUnitPrice.Execute()
dblUnitPrice = rstUnitPrice.Fields(0)
rstUnitPrice.Close
Set rstUnitPrice = Nothing
In the case of a list I would use a while loop to enter all the values that the recordset returned.
Rodrigo