Problem updating Sub-Form

[utzja1]

I have a sub-form that has a query as its source. The user enters a value into an unbound text box on the parent form, and the query is run using the user-supplied value to filter the data. I have a button on the parent that launches the macro OnClick, but the sub-form is not updating after the query is run. I have verified that the query is running properly; the hitch is that the subform is not refreshing at all. Here is the code that I'm using:

Private Sub FilterData_Click()

On Error GoTo Err_FilterData

Dim dbs As DAO.Database
Dim rstQ As DAO.Recordset

Set dbs = CurrentDb

Me.[SourceQueryQ].Requery
Forms!EditMntItems001F.[subfrmMaintItemsQ].Form.Refresh

Exit_FilterData:
Set rstQ = Nothing
Set dbs = Nothing
Exit Sub

Err_FilterData:
Resume Exit_FilterData

End Sub

Thanks for your help.

 

[pbaldy]

Have you tried Requery instead of Refresh?

 

[utzja1]

Yes, but the Subform still doesn't refresh. If I click the macro button and then go to the Home/Refresh All button at the top of the screen, then the Subform gets refreshed with the proper data. The query seems to work fine, but I can't get the data updated in the subform.

I have the record source of the subform set to a query. Does it need to be set to the main form in order for this to occur? Would I set the record source of the main form to the query that supplies data to the subform?

Thanks.

 

[JHB]

Try to comment out the error handling, ('On Error GoTo Err_FilterData), to see if there error in the code!

 

[utzja1]

Thanks, JHB. By commenting out the error-handler language, it's now telling me that it can't find field 1 in the expression, so it looks like it won't update the subform because the field reference is coded incorrectly and the statement is just crapping out. That's where I'll focus next.

Thanks.

 

[utzja1]

Is the syntax for requerying the subform the same as for the form? I entered this into the VBA module and I got "Run-time Error 424: Object Required":

SubForms!SingleMaintQ.Requery

I tried requerying the main form, but no luck in getting the subform to update. Thanks for your consideration.

 

[JHB]

Post you database with some sample data (zip it) - and the name of the form where the problem is.

 

[utzja1]

I figured out my issue, and not to get too technical, I may have set the wrong query as the record source. It might be working correctly now that I might have corrected that.

Thanks for your help.

 

[JHB]

You're welcome, good you found the problem. Luck with you project.