Updating subform data with control input

[GSan]

Hi,

I am having trouble updating the data in a subform after the user selects data from a control. This is the design of the form...

I have a set of controls (ListBox, CheckBoxes, and Date Enters). Below these controls is a subform that has a datasheet that displays data using a crosstab query. Below the subform is a bar graph that displays data from the subform.

When the user initially opens the form, the controls are set to reflect the data in the subform and the graph displays the data in the subform.

When a user clicks on a selection from the listbox, I want to use that value to requery the crosstab query in the subform and update the data. Then, in turn, update the graph.

Currently, when I select a value from the ListBox, the update is not reflected in the subform unless I close the form and reopen it. Below is the code:

On Error GoTo ErrorHandler

Set db = CurrentDb

'This is the updated query with the parameter added
sqlString = sqlQuery
Set queryDefinition = CurrentDb.QueryDefs("TotalFileCountByNameAndType_Crosstab")

'Set the new SQL string
queryDefinition.SQL = sqlString

'Requery the subform crosstab query
TotalFileCount_SubForm.Requery

queryDefinition.Close
db.Close


Set queryDefinition = Nothing
Set db = Nothing

Exit Sub

ErrorHandler:
'Display error message
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

I have tried putting the requery statement after the .Close function but it still does not update the subform unless I close the form and then reopen.

How can I get the subform to update after the user selects or deselects a value in a control?

Thanks for the help!

 

[GSan]

I figured it out. For anyone else who has this problem, this is how I fixed it...
My subform was a Subform control, not a subform Form.
To determine which one you have, look at the Properties of the object.
If the object gets its data using a Source Object, it is a control.
If the object gets its data using a RecordSource, it is a form.

I had the control variety. When I used the Requery function to update my crosstab, the subform control did not update the data on the display.
But, if you reset the SourceObject parameter with the new SQL statement, it works.
So the code is...
SubformControlName.SourceObject = ""
SubformControlName.SourceObject = Query.NameOfCrosstabQuery

SubformControlName is the name of the subform control
Query.NameOfCrosstabQuery is the name of your query that you changed.