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!
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!