Can't figure out Requery

[Annie]

Hi. I know requery is pretty basic, but I can't figure
out how to do this.

I have a subform MemberNumber that I'm using to display
the member count. The unbound form is Statistics and I
have no link between the subform and form (Link Child
Fields and Link Master Fields are blank). Is this setup
OK?

I add an Member(on the Individuals form). I want
the Statistics form, MemberNumber subform to increment by
one. So I need to tell MemberNumber that Individuals has
increased: Forms!Individuals.Requery - that makes sense
to me.

The part I'm not getting is where to put the code.
I don't think I want Individuals to requery itself.
So that means I'd want the code to be in MemberNumber.
However, MemberNumber subform only has On Enter and On
Exit. So I tried the code on Statistics:

Private Sub Form_AfterUpdate()
Forms!Individuals.Requery
End Sub

which doesn't work and doesn't make sense to me as the
Statistics form (or MemberNumber subform for that matter)
isn't updating (it's Individuals that is).

I realize that this is basic, but I'm not getting it. I
appreciate the details...
Thanks for your help, Annie

 

[Steve Schapel]

Annie,

It is not clear what this members count is. But if you mean you have
two forms (Individuals and Statistics) open at the same time, and you
want something to happen on the Statistics form when you add a new
record on the Individuals form, then the code needs to happen on an
event on the Individuals form, probably the After Update event of the
form would be applicable. But I don't think Forms!Individuals.Requery
will be correct. I don't think you want to requery the Individuals
form, that's where you are adding the new record, right? You are
interested in something happening on the MemberNumber subfrom on the
Statistics form, so that's what your code needs to refer to. I would
imagine it is Recalc you need, and not Requery. Maybe...
Forms!Statistics!MemberNumber.Form.Recalc

 

[Annie]

Thanks for the reply! I originally had the MemberNumber
on my (main) switchboard, but it of course showed on ALL
switchboards (when I clicked a button on the main, I
navigate to a "sub" switchboard). And the count never
didn't increment until I made it back to the main,
meaning that if I added a member, closed the Individuals
form and was on a "sub" switchboard, MemberCount was not
incremented until I went to the main. So, to get away
from this issue I decided to have a separte Statistics
form that I'd navigate to if I wanted the count. Hope
this can be done. Or if I could get the s
"sub" switchboard to increment right away, I'd love to
keep it on the switchboard.

Here's MemberNumber:
SELECT Count(Contacts.Member) AS CountOfMembers
FROM (Contacts INNER JOIN MemberStatus ON
Contacts.MemberStatusID = MemberStatus.MemberStatusID)
INNER JOIN MemberType ON Contacts.MemberTypeID =
MemberType.MemberTypeID
GROUP BY Contacts.Member, MemberStatus.MemberStatus,
MemberType.MemberType
HAVING (((Contacts.Member)=Yes) AND
((MemberStatus.MemberStatus)="Current-Active") AND
((MemberType.MemberType)="Handler"));

So when I add a Member on the Invidivuals form, I want
MemberNumber (subform on Statistics form) to increment
even though the STATISTICS FORM IS NOT OPENED.

Individuals form:
SELECT Contacts.* FROM Contacts WHERE
(((Contacts.DonorTypeID)="IN"));

So would I put this on Individuals:

Private Sub Form_AfterUpdate()
Forms!Statistics!MemberNumber.Form.Recalc
End Sub

Thanks for your time~ Annie

 

[Annie]

Actually, I think I don't need to do anything- the best
fix ever! Since I moved my subform off the switchboard
and onto a separate form, when I open the form/subform it
makes the current count.

But thanks for the methodology explanation- I understand
requery/recalc and the code placement much better.
Thanks, Annie