Requery Subform on Tab Change not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Steven said:
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
Click to expand...


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?
 
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.
 
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.
 
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.
 
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.

Marshall Barton said:
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.
Click to expand...
Click to expand...
 
Back
Top