Subform within Subform on Main Form data refresh

[Guest]

On the main form I have drop down boxes which allow the user to select 3
criteria for the data to view. I then have a subform which does grouping by
topic and then a subform within that subform that gives the details. I am
updating the record source with vba without anyproblems, however when I make
the 2 subforms visible the data is not current. The queries that underly
the 2 subforms are updated and have the correct data. Why isn't the
screen/form refreshing with the correct data.

Any info you can provide would be greatly appreciated.

CLM

 

[Rob Oldfield]

Sounds like you're just missing a me.subformControlName.requery or two.

If you can't figure it from there... post some code.

 

[Guest]

Rob,

After updating the subform's query I have the following code;
'Requery Main SubForm
Me.subfrmDTSRptFiltered.Requery
'Requery subform within Main Subform
Me.subfrmDTSRptFiltered!subfrmDTSRptFilteredDates.Requery
Me.Refresh

If
Forms!frmDTSRptFilter!subfrmDTSRptFiltered.Form.RecordsetClone.RecordCount =
0 Then
intMsg = MsgBox("There are no records to show for the criteria: " &
vbCrLf _
& vbCrLf & vbTab & "Domain: " & txtDomain, vbOKOnly)
Else
Me.subfrmDTSRptFiltered.Visible = True
End If

So you can see I do have the requery commands and a refresh as well, but the
subform is still not updating with the new data. Any other thoughts?

CLM

 

[Rob Oldfield]

When you say that you're updating the subform's query... how are you doing
that? Setting it directly to an SQL statement? Basing it on a query that
references form controls? Same question for the subsubform.

It sounds like you're doing it the SQL way but I'd like to check. Please
post the rest of your code. (...and how are you triggering the requery
routine?)

 

[Guest]

Rob,

User left mouse click on Button "Get Data"... strSQL is updated with the
fields that they selected to make the SELECT statement, then I use the code

db.QueryDefs("qselDTSRptFilteredGroup").SQL = strSQL
db.QueryDefs("qselDTSRptFilteredDates").SQL = strSQL2

Me.subfrmDTSRptFiltered.Requery
Me.subfrmDTSRptFiltered!subfrmDTSRptFilteredDates.Requery
Me.Refresh

If Forms!frmDTSRptFilter!subfrmDTSRptFiltered.Form.RecordsetClone.RecordCount
=0 Then
intMsg = MsgBox("There are no records to show for the criteria: "
& vbCrLf _
& vbCrLf & vbTab & "Domain: " & txtDomain, vbOKOnly)
Else
Me.subfrmDTSRptFiltered.Visible = True
End If

End Sub

 

[Rob Oldfield]

Hmm. That is a little bit strange. I have to admit that I've never tried
to set it up in the way that you're using but, trying it here, you just need
to reset the object that the subforms are based on... something like...

db.QueryDefs("qselDTSRptFilteredGroup").SQL = strSQL
Me.subfrmDTSRptFiltered.sourceobject="YourSubFormName"

....though it beats me why a straight requery isn't good enough.

Having said that, unless you have some reason for wanting to modify the
actual SQL of the query, I'd suggest just doing something like this
instead...

Me.subfrmDTSRptFiltered.Form.RecordSource = strSQL

Another thing maybe worth considering for the subsubform. Is it not
possible to set that up just by a standard child/master link to the subform?