requery

[Annie]

Hi. I'm using a switchboard that has statistics on it (how
many members...). Since the statistics are on the main
switchboard they show up on all of the switchboard, but
don't seem to update the counts until I navigate back to
the main switchboard. I beleive that I can't just have
these statistics appear only on the main switchboard.

So, how can I get the statistics to be updated after the
members get updated? Would the code go on the Members
form, the CountOfMembers form or the switchboard form?
And which event would I attach it to? It's a little
strange in that the "count" form appears on the
switchboard form but counts the member form. Thanks, Annie

Here's the "count" sql:
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"));

 

[Arvin Meyer]

You have to tell the form on the main switchboard to requery in the line of
code after you've updated your data:

Forms!MyOtherFormName.Requery
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Annie]

Thanks, Arvin.

I'll use Forms!Individuals.Requery

Now, the question is which event to attach it to on the
switchboard. The switchboard itself isn't doing anything
(like updating). I have:
Private Sub Form_Current() and then a function to handle
button clicks.

Thanks,
Annie

 

[Arvin Meyer]

You need to do it in the After Update code for each of the members.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Annie]

Thanks, I couldn't get it to work. BUT I decided to put
statistics on it's own unbound form (called Statistics).
So that means that my subform is not linked to the form.
I don't think I'm understanding.

I add an Individual (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.
Obviously, I don't 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.
Thanks for your help, Annie

 

[Arvin Meyer]

Hi Annie. Sorry for the delay. If it is a sunform. the syntax is different:

Forms!NameOfSubformCONTROL.Form.Requery

Notice, that I capitalized CONTROL for emphasis. It is not the name of the
subform, but the name of the control on the main form. They are often the
same, but not always.

If it is not a subform, but an independent form instead, the earlier syntax
is correct:

Forms!TheFormName.Requery

If you have any spaces in a name, make sure you surrond it with square
brackets:

Forms![The Form Name].Requery
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access