Requery data on subform

[HelenJ]

I have a subform, the records on which are limited by an unbound combo box on
the main form, the values in the combo are also determined by the main form.

To put it more plainly the main form is suppliers, the combo box lists the
ranges that the supplier has - so change the supplier and the list of ranges
changes. Associated with a supplier's range is a list of products.

This all works fine, except if I go to a new supplier (via next record or
using a "find supplier" type combo) then I can easily update the list of
ranges but the subform (which shows the list of products) refuses to update.

In the Suppliercbo AfterUpdate I have tried using:

Me.RangeCbo.Requery (this works fine the range cbo clears)
Me.ProductSbfm.requery (this appears to do nothing - any existing records
stay)

I also tried requerying the subform in the range cbo AfterUpdate - but this
still does nothing - if I actually change the value in the rangecbo then it
works fine, but how can I clear it without a click??

Thanks

 

[Maurice]

If you are requering from the main form the reference from the main form
should also be there. So try something like:

forms!frmMainform!subformname.forms.requery

hth

 

[HelenJ]

In the combo box after update I tried putting:

Forms!suppliersFm!ProductSbfm.Forms.Requery

but I got a run-time error 438 "Object doesn't support this property or
method"

But it happily lets me select
Me.ProductSbfm.Requery

from the help lists - ie I type in me. and then select from the pop-up, so
I'm surprised it Access doesn't know what I am talking about....

 

[HelenJ]

OK I've solved it - so I post it here for anyone else (or for my solution to
be shot down!)

My problem was that requerying my combo was leaving the value stored in it -
so even though I was requerying the subform it came back with the same
values. So I have now forced the value to 0 - so no records show in the
sub-form which forces the user to choose a value from the list.

I put the code in the OnCurrent of the subform - which means how ever the
user gets there the code runs (at least it seems to!)

Private Sub Form_Current()
Me.RangeCbo.Requery
Me.RangeCbo.Value = 0
Me.ProductSbfm.Form.Requery
End Sub