Re: Slow Code

On Dec 28, 1:44 am, "tina" <nos...@xxxxxxxxxxx> wrote:
so your expression says "if the recordcount of subform Mssubform equals the
value in textbox Text171 on the subform, then set the value of this textbox
to "Paid", otherwise set it to "To Be Paid".

first of all, just as an FYI, you can cut down the reference a bit in the
expression; there's no need to refer to the mainform when the expression is
executing within the mainform. try

=IIf(Mssubform.Form.RecordsetClone.RecordCount=Mssubform.Form!Text171,"Paid"
,"To Be Paid")

i doubt that will solve your stated problem, though, it's just an fyi. i
assume the above expression is used in the ControlSource property of Text109
in the mainform, though you don't make that clear in your post. suggest you
run the expression in the subform, rather than in the mainform. add an
unbound textbox to the subform's Footer section, and set its' Visible
property to No. note that you can do this even if the subform is set to
Data*** view. i'll call the textbox txtPay. try the following expression
in the ControlSource property, as

=IIf(Count(MyField) = Text171, "", "To Be Paid")

replace MyField with the correct name of a field in the subform's
RecordSource (i usually use the primary key field). unless you're using the
"Paid" text for something else, there's no point in assigning that value and
then hiding the control in the mainform, so i replaced the text with a
zero-length string.

change the ControlSource of Text109 in the mainform to

=Mssubform.Form!txtPay

no point using code to hide/unhide the control; if the subform record count
equals Text171, nothing will show in the control, if the values are not
equal, you'll see the "To Be Paid" text. if you don't want an "empty"
control showing, just set it's BackStyle and BorderStyle properties to
Transparent - all you'll see is text, or nothing. but if you really want to
hide/unhide the control in the mainform, try the following code in the
mainform's Current event, as

    Me!Text109.Visible = (Me!Text109 = "To Be Paid")

hth

"iamnu" <iamn...@xxxxxxxxx> wrote in message

news:303adc4f-e603-465e-a4f9-521bc84c015e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I have a subform with a control in the footer with this Control
Source:
=Count([DatePaid])

I have a control on the Main form with this Control Source:
=IIf(Forms!MSmainform!Mssubform.Form.RecordsetClone.RecordCount=Forms!
MSmainform!Mssubform.Form.Text171,"Paid","To Be Paid")

I have the following Event Procedure:
Private Sub Form_Current()
   If Text109 = "Paid" Then
      Text109.Visible = False
   Else
      Text109.Visible = True
   End If
End Sub

The Text109 Control is ALWAYS visible, UNLESS I step through the code.

Can someone explain?

Thanks for the reply, Tina...

None of what you suggested solved the problem, but I now think I know
what the REAL problem is. I have tested some various methods to do
what I want, and the problem is that I need a DCount function to work
in place of the "=Count([DatePaid])" expression that I am currently
using in the MSsubform.

So to restate my problem, how do I write a DCount function that will
duplicate the values provided by "=Count([DatePaid])"???

For each AcctID in tblMSsub I want to count how many records have a
[DatePaid] that is not null.

This sounds simple enough to me, but I cannot get any code I try to
come up with the same results as "=Count([DatePaid])". Please Help!!

.

Loading