I am working on an application that should allow user select a code from a list box, and this value should be passed as a parameter to a sub-form(continuous) which is tied to an SQL Query to populate the form with values returned from the query.
How do I tie the listbox on the parent form to the continuous form (child form) in such a way that a change in the listbox is registered in the subform and it(child form) is re-populated accordingly?
The code below is what I currently have in my Parent form to trigger the change in the subform. But I am still missing the bit where I trigger the reloading of the information in the subform with this.
storedproc
SELECT tblACCOUNT.ACCOUNT_LABEL, tblACCOUNTValue.[ACCOUNTVALUE_VALUE]
FROM ((tblUPS INNER JOIN tblProductUPS ON tblProductUPS.[PRODUCTUPS_UPS] = tblUPS.[UPS_CODE]) INNER JOIN tblACCOUNT ON tblUPS.UPS_ID = tblACCOUNT.ACCOUNT_UPSID) INNER JOIN tblACCOUNTValue ON tblACCOUNTVALUE.[ACCOUNTVALUE_ACCOUNTID]= tblACCOUNT.[ACCOUNT_ID]
WHERE TBLPRODUCTUPS.[PRODUCTUPS_PRODUCTID] = (SELECT tblProductLevel.[PRODUCTLEVEL_ID]
FROM tblProductLevel WHERE tblProductLevel.[PRODUCTLEVEL_Code] IN ( [UPSIDS])
);
This is a field in my sub-form which I would like to tie to my query as source of parameter for the query.
Public Property Let qString(unstring As String)
If Not IsNull(unstring) And Len(unstring) > 0 Then
Currentstring = unstring
End If
End Property
The code below is code in my parent form I test connects directly to the Query that I created and passes the parameter directly to the query. However, since my sub-form is itself tied to the query, I need a way to get to the subform query and pass the information I need passed and invoke an update on the form afterwards.
Private Sub LoadSubform(unspscstring As String)
On Error GoTo Err_LoadSubform_Change
Dim dbs As Database
Dim strSQL As String
Dim strSelect As String
Dim strQueryName As String
Dim qryDef As QueryDef
Dim rst As Recordset
Dim prmOne As DAO.Parameter
Set dbs = CurrentDb
'then we'll open up the query:
Set qryDef = dbs.QueryDefs("spgetAttributeByUNSPSC")
'Now we'll assign values to the query using the parameters option:
'link your DAP.Parameters to the query
'Set prmOne = qryDef.Parameters!param_one
'prmOne = unspscstring
qryDef.Parameters(0) = unspscstring
'Now need to somehow trigger an update on the subform
'Close all objects
rst.Close
qryDef.Close
Set rst = Nothing
Set qryDef = Nothing
Bye_LoadSubform_Change:
Exit Sub
Err_LoadSubform_Change:
Beep: MsgBox Error$, 16, "Select Failed"
Resume Bye_LoadSubform_Change
End Sub
