Requery Subform on Tab Change not working

[Steven M. Britton]

I have an Unbound Form, on that form I have a TabControl and a subform at is
a datasheet. The Subform is not on the tab pages, it sit below the tab
controls. In the OnChange Event of the Tab Control I have the below code
which extracts the Name on the tab and then redefines a query. That all
works fine, the problem is that the subform datasheet doesn't change to
reflect the new criteria of query unless I close and reopen the form. Can
someone point out what is missing.

thanks

Private Sub TabCtl0_Change()
Something = Me.TabCtl0.Value

Select Case Something

Case Is = 0
Something = Me.Page1.Caption
Case Is = 1
Something = Me.Page2.Caption
Case Else
MsgBox "ERROR"
End Select

CurrentDb.QueryDefs("qryLifecycleSpread").SQL = "SELECT & LongSQLstatment _
& "WHERE (((tblModels.ModelNum) = '" & Something & "')) " _
& "ORDER BY tblChanges.Date;"

Me.frmChanges.Requery

End Sub

 

[Marshall Barton]

I have an Unbound Form, on that form I have a TabControl and a subform at is
a datasheet. The Subform is not on the tab pages, it sit below the tab
controls. In the OnChange Event of the Tab Control I have the below code
which extracts the Name on the tab and then redefines a query. That all
works fine, the problem is that the subform datasheet doesn't change to
reflect the new criteria of query unless I close and reopen the form. Can
someone point out what is missing.

thanks

Private Sub TabCtl0_Change()
Something = Me.TabCtl0.Value

Select Case Something

Case Is = 0
Something = Me.Page1.Caption
Case Is = 1
Something = Me.Page2.Caption
Case Else
MsgBox "ERROR"
End Select

CurrentDb.QueryDefs("qryLifecycleSpread").SQL = "SELECT & LongSQLstatment _
& "WHERE (((tblModels.ModelNum) = '" & Something & "')) " _
& "ORDER BY tblChanges.Date;"

Me.frmChanges.Requery

End Sub

Assuming that frmChanges is the name of the subform
**control** on the main form, I think that should be:
Me.frmChanges.FORM.Requery

But why mess with the query def object when you can set the
subform's RecordSource to the SQL statement?

 

[Steven M. Britton]

I could set the RecordSource to the SQL statement you're correct, but I would
still need to requery. I had prior to your posting changed the statement to
read

Me.frmChanges.Form.Requery

I still do not receive the desired result - Still nothing happens the data
doesn't change. And you are correct frmChanges is teh subform **control** on
the main form.

 

[Peter Martin]

I'll suggest that if you're going to do it via the querydef, then after you
change the SQL you refresh the collection

currentdb.QueryDefs.Refresh

Most would just change the rowsource - you're not gaining any speed here by
doing the SQL. If you use something like tblModels.modelnum =
forms!theForm!tabclt0... the query might stay built and would be quicker.

HTH

Peter.

 

[Marshall Barton]

Setting the query's SQL property is the long way around.

The record source is much more straightforward - it doesn't
involve another object and it also requery's the form
automatically.

 

[Steven M. Britton]

Per both your and Peter's comments I change the recordsource to the SQL
String - No it works fine and I'll continue to use it. Thanks for the advise.

Setting the query's SQL property is the long way around.

The record source is much more straightforward - it doesn't
involve another object and it also requery's the form
automatically.
--
Marsh
MVP [MS Access]

I could set the RecordSource to the SQL statement you're correct, but I would
still need to requery. I had prior to your posting changed the statement to
read

Me.frmChanges.Form.Requery

I still do not receive the desired result - Still nothing happens the data
doesn't change. And you are correct frmChanges is teh subform **control** on
the main form.