How to Requery Sub form from another sub form

[AJ]

I have a main form with 2 sub forms. The first sub form has a list of
businessnames and when I click on one of the names I want the second
sub form to filter to that name.

First it copies the name to an unbound text box on the main form. It
is called NameChosen.
The second sub form is based on a query that filters off NameChosen.

The following code works (sometimes) and is placed in the on click
event of the name in the first subform:

Forms!MismatchMainfrm!NameChosen = BusinessName
Forms!MisMatchMainfrm!MisMatch020subMatchIt.Requery

This works but sometimes does not. The database likes it well enough,
but when I close down and come back in, sometimes things go wrong.
I've been here before and when I wrote the procedure I had the faint
idea that I should be doing this in another way. What is wrong with
this code and how should I change it?

When this code fails, all I have to do is take the code off, save it,
close down, come back in, reinstate the code, and bingo, it works.

Matt

[Marshall Barton]

Post by AJ
I have a main form with 2 sub forms. The first sub form has a list of
businessnames and when I click on one of the names I want the second
sub form to filter to that name.
First it copies the name to an unbound text box on the main form. It
is called NameChosen.
The second sub form is based on a query that filters off NameChosen.
The following code works (sometimes) and is placed in the on click
Forms!MismatchMainfrm!NameChosen = BusinessName
Forms!MisMatchMainfrm!MisMatch020subMatchIt.Requery
This works but sometimes does not. The database likes it well enough,
but when I close down and come back in, sometimes things go wrong.
I've been here before and when I wrote the procedure I had the faint
idea that I should be doing this in another way. What is wrong with
this code and how should I change it?
When this code fails, all I have to do is take the code off, save it,
close down, come back in, reinstate the code, and bingo, it works.

I'm not sure about the works "sometimes" aspect, but, if
you're going to use that approach, that much of what you've
done looks ok to me. Another key piece of the code that you
need to go along with it is, if the record is dirty,
especially the BusinessName field, the record needs to be
saved before doing the Requery. I think the could would be
more like:

If Me.Dirty Then Me.Dirty = False
Parent!NameChosen = Me.BusinessName
Parent!MisMatch020subMatchIt.Form.Requery

BUT, I probably not use that approach. Instead, set the
MisMatch020subMatchIt subform control's LinkMasterFields
property to NameChosen and LinkChildFields to the
corresponding field is the MisMatch020subMatchIt subform's
record source table/query. This way, you can remove the
criteria from the record source query, remove the entire
Click event procedure and only use this one line in the
first sub form's Current event:
Parent!NameChosen = Me.BusinessName

[d***@aol.com]

Thanks. I haven't tried this yet but will get to soon.

Matt

[Pat Hartman]

You are not referencing the subform's requery method correctly.

Forms!MisMatchMainfrm!MisMatch020subMatchIt.Form.Requery



Submitted via EggHeadCafe
Microsoft SQL Server Developer For Beginners
http://www.eggheadcafe.com/training-topic-area/SQL-Server-Developer/5/SQL-Server.aspx