Slow Code

[iamnu]

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?

 

[Stefan Hoffmann]

hi,

I have the following Event Procedure:
Private Sub Form_Current()
If Text109 = "Paid" Then

Compare the record numbers using DCount().

ReccordCount is always a problem, as it is not necessarily reflecting
the total number of records. To get the total number, you must often
access all records by Recordset.MoveLast before you get the correct value.

Another problem is, that your control containing =Count([DatePaid]) is
evaluated asynchronous. So your event Form_Current() is often executed
before the correct count can be used.


mfG
--> stefan <--

 

[iamnu]

hi,

I have the following Event Procedure:
Private Sub Form_Current()
   If Text109 = "Paid" Then

Compare the record numbers using DCount().

ReccordCount is always a problem, as it is not necessarily reflecting
the total number of records. To get the total number, you must often
access all records by Recordset.MoveLast before you get the correct value..

Another problem is, that your control containing =Count([DatePaid]) is
evaluated asynchronous. So your event Form_Current() is often executed
before the correct count can be used.

mfG
--> stefan <--

Thanks for the reply, stefan...

I don't know how to "compare the record numbers using DCount(), and I
don't know to include "Recordset.MoveLast" into my code.
Can you give me some code to get me started?

 

[tina]

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
Datasheet 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]

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
Datasheet 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

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!!

 

[Stefan Hoffmann]

hi,

I don't know how to "compare the record numbers using DCount(), and I
don't know to include "Recordset.MoveLast" into my code.
Can you give me some code to get me started?

DCount("*", _
"TableName", _
"Condition")

TableName is the name of the table or query your subform is based on. If
it uses an embedded query, then save it to a normal query.

Condition is a WHERE clause without the WHERE keyword. Here do you need
to reassemble the filter which does do subform control over the
master-child fields. E.g.

Your main form has an ID field and your subdorm is linked with a field
SubID, then you need

"SubID = " & Me![ID] & " AND NOT IsNull([DatePaid])"

as condition, cause you're running the DCount() in the main forms On
Current event.


mfG
--> stefan <--

 

[iamnu]

hi,

I don't know how to "compare the record numbers using DCount(), and I
don't know to include "Recordset.MoveLast" into my code.
Can you give me some code to get me started?

   DCount("*", _
          "TableName", _
          "Condition")

TableName is the name of the table or query your subform is based on. If
it uses an embedded query, then save it to a normal query.

Condition is a WHERE clause without the WHERE keyword. Here do you need
to reassemble the filter which does do subform control over the
master-child fields. E.g.

Your main form has an ID field and your subdorm is linked with a field
SubID, then you need

   "SubID = " & Me![ID] & " AND NOT IsNull([DatePaid])"

as condition, cause you're running the DCount() in the main forms On
Current event.

mfG
--> stefan <--

I am running DCount in the SubForm. Here is an example of the code
that DOES NOT work properly.

TotalCount: DCount("*","tblMSsub",("AcctID = " & [AcctID] And
([DatePaid])))
This gives me the TOTAL records in tblMSsub.

I want to know the number of records for EACH AcctID that has a
[DatePaid]. If [DatePaid] is null, then it would not be counted.

Example:
AcctID=1, TotalCount=5
AcctID=2, TotalCount=0
AcctID=3, TotalCount=23
Etc...

Whats wrong with my code?

 

[Stefan Hoffmann]

hi,

I am running DCount in the SubForm. Here is an example of the code
that DOES NOT work properly.

TotalCount: DCount("*","tblMSsub",("AcctID = " & [AcctID] And
([DatePaid])))

Try this:

TotalCount: DCount(
"*",
"tblMSsub", _
"AcctID = " & [AcctID] & " And Not IsNull([DatePaid])"
)

You need to assembly the correct condition as a string.


mfG
--> stefan <--

 

[iamnu]

hi,

I am running DCount in the SubForm.  Here is an example of the code
that DOES NOT work properly.

TotalCount: DCount("*","tblMSsub",("AcctID = " & [AcctID] And
([DatePaid])))

Try this:

TotalCount: DCount(
  "*",
  "tblMSsub", _
  "AcctID = " & [AcctID] & " And Not IsNull([DatePaid])"
)

You need to assembly the correct condition as a string.

mfG
--> stefan <--

Your code still gives the TOTAL number of records in tblMSsub. In
other words, no change to my original code.

 

[iamnu]

hi,

I am running DCount in the SubForm.  Here is an example of the code
that DOES NOT work properly.
TotalCount: DCount("*","tblMSsub",("AcctID = " & [AcctID] And
([DatePaid])))

Try this:

TotalCount: DCount(
  "*",
  "tblMSsub", _
  "AcctID = " & [AcctID] & " And Not IsNull([DatePaid])"
)

You need to assembly the correct condition as a string.

mfG
--> stefan <--

Your code still gives the TOTAL number of records in tblMSsub.  In
other words, no change to my original code.

I just noticed that when I view my SQL Statement in DataSheet mode,
the Field [AcctID] is displayed with a heading Expr1000.
I haven't experienced this previously. Is this normal?

 

[Stefan Hoffmann]

hi,
Your original code was

"=Count([DatePaid])"

TotalCount: DCount(
"*",
"tblMSsub", _
"AcctID = " & [AcctID] & " And Not IsNull([DatePaid])"
)

should return the same values.

The only difference may be the linking fields you have in your subform
control. Is the subform only linked via [AcctID] ?

I just noticed that when I view my SQL Statement in DataSheet mode,
the Field [AcctID] is displayed with a heading Expr1000.
I haven't experienced this previously. Is this normal?

Yes, when you have selected this field twice or more.



mfG
--> stefan <--

 

[iamnu]

hi,

Your original code was

   "=Count([DatePaid])"

TotalCount: DCount(
   "*",
   "tblMSsub", _
   "AcctID = " & [AcctID] & " And Not IsNull([DatePaid])"
)

should return the same values.

The only difference may be the linking fields you have in your subform
control. Is the subform only linked via [AcctID] ?

I just noticed that when I view my SQL Statement in DataSheet mode,
the Field [AcctID] is displayed with a heading Expr1000.
I haven't experienced this previously.  Is this normal?

Yes, when you have selected this field twice or more.

mfG
--> stefan <--

Right you are...[AcctID] was selected twice.

The subform is linked by [AcctID];[CoName];[PayeeID].

Is this the problem?

 

[Stefan Hoffmann]

hi,

The only difference may be the linking fields you have in your subform
control. Is the subform only linked via [AcctID] ?

The subform is linked by [AcctID];[CoName];[PayeeID].

Is this the problem?

Yes, you need these fields to get the same filter for DCount(), so this
condition should work (one line):

"AcctID = " & [AcctID] & " And [CoName] = '" & [CoName] & "' And
[PayeeID] = " & [PayeeID] & " And Not IsNull([DatePaid])"



mfG
--> stefan <--

 

[iamnu]

hi,

The only difference may be the linking fields you have in your subform
control. Is the subform only linked via [AcctID] ?

The subform is linked by [AcctID];[CoName];[PayeeID].

Is this the problem?

Yes, you need these fields to get the same filter for DCount(), so this
condition should work (one line):

"AcctID = " & [AcctID] & " And [CoName] = '" & [CoName] & "' And
[PayeeID] = " & [PayeeID] & " And Not IsNull([DatePaid])"

mfG
--> stefan <--

Well, my DCount expression now reads as follows:
TotalCount: DCount("*","tblMSsub","AcctID = " & [AcctID] And
"CoNameID] = '" & [CoNameID] And "PayeeID = " & [PayeeID] And " Not
IsNull([DatePaid])")

Notice that the quotes and brackets are different than what you had,
as I was getting a syntax error from what you specified.

I'm still getting the TOTAL records in tblMSsub...

 

[tina]

your expression is not correct either, hon. try

DCount("*","tblMSsub","AcctID = " & [AcctID] & " And CoNameID = '" &
[CoNameID] & "' And PayeeID = " & [PayeeID] & " And DatePaid Is Not Null")

note that the above syntax assumes that AcctID is a Number data type,
CoNameID is a Text data type, and PayeeID is a Number data type; if you look
closely you'll see that there are single quotes inside the double quotes,
which surround the [CoNameID] value, as (exaggerated for clarity)

And ' " & [CoNameID] & " ' And

if the data type assumptions are wrong, you'll need to adjust the syntax
accordingly. also, there's no need to use the IsNull function. the Criteria
argument of a domain aggregate function is essentially a SQL WHERE clause
without the WHERE keyword, so you'd write it the same as for a query, with a
direct "Is Not Null" criteria.

my only question is, if the simple expression

=Count([DatePaid])

gives you the correct count in a textbox control in the subform's Footer
section, why are you not able to utilize that value, rather than going
through the exercise above?

hth

hi,

The only difference may be the linking fields you have in your subform
control. Is the subform only linked via [AcctID] ?

The subform is linked by [AcctID];[CoName];[PayeeID].

Is this the problem?

Yes, you need these fields to get the same filter for DCount(), so this
condition should work (one line):

"AcctID = " & [AcctID] & " And [CoName] = '" & [CoName] & "' And
[PayeeID] = " & [PayeeID] & " And Not IsNull([DatePaid])"

mfG
--> stefan <--

Well, my DCount expression now reads as follows:
TotalCount: DCount("*","tblMSsub","AcctID = " & [AcctID] And
"CoNameID] = '" & [CoNameID] And "PayeeID = " & [PayeeID] And " Not
IsNull([DatePaid])")

Notice that the quotes and brackets are different than what you had,
as I was getting a syntax error from what you specified.

I'm still getting the TOTAL records in tblMSsub...

 

[iamnu]

your expression is not correct either, hon. try

DCount("*","tblMSsub","AcctID = " & [AcctID] & " And CoNameID = '" &
[CoNameID] & "' And PayeeID = " & [PayeeID] & " And DatePaid Is Not Null")

note that the above syntax assumes that AcctID is a Number data type,
CoNameID is a Text data type, and PayeeID is a Number data type; if you look
closely you'll see that there are single quotes inside the double quotes,
which surround the [CoNameID] value, as (exaggerated for clarity)

And ' " & [CoNameID] & " ' And

if the data type assumptions are wrong, you'll need to adjust the syntax
accordingly. also, there's no need to use the IsNull function. the Criteria
argument of a domain aggregate function is essentially a SQL WHERE clause
without the WHERE keyword, so you'd write it the same as for a query, with a
direct "Is Not Null" criteria.

my only question is, if the simple expression

=Count([DatePaid])

gives you the correct count in a textbox control in the subform's Footer
section, why are you not able to utilize that value, rather than going
through the exercise above?

hth

hi,

The only difference may be the linking fields you have in your subform
control. Is the subform only linked via [AcctID] ?
The subform is linked by [AcctID];[CoName];[PayeeID].
Is this the problem?

Yes, you need these fields to get the same filter for DCount(), so this
condition should work (one line):

"AcctID = " & [AcctID] & " And [CoName] = '" & [CoName] & "' And
[PayeeID] = " & [PayeeID] & " And Not IsNull([DatePaid])"

mfG
--> stefan <--

Well, my DCount expression now reads as follows:
TotalCount: DCount("*","tblMSsub","AcctID = " & [AcctID] And
"CoNameID] = '" & [CoNameID] And "PayeeID = " & [PayeeID] And " Not
IsNull([DatePaid])")

Notice that the quotes and brackets are different than what you had,
as I was getting a syntax error from what you specified.

I'm still getting the TOTAL records in tblMSsub...  

stefan,

I want you to know that I have really appreciated your help. But at
this point, I think we are spinning our wheels. I'm not sure that I
have communicated the problem fully, but a break from this might
help. I am going to close out this "Slow Code" message, take a break
for about a week, then start a new more distinct message to restate
the problem as I now see it (and after some more testing).

Thanks very much for your help. I'm sorry we couldn't solve the
problem, but I'll try again at another time.

 

[tina]

okay. good luck with it.

your expression is not correct either, hon. try

DCount("*","tblMSsub","AcctID = " & [AcctID] & " And CoNameID = '" &
[CoNameID] & "' And PayeeID = " & [PayeeID] & " And DatePaid Is Not Null")

note that the above syntax assumes that AcctID is a Number data type,
CoNameID is a Text data type, and PayeeID is a Number data type; if you look
closely you'll see that there are single quotes inside the double quotes,
which surround the [CoNameID] value, as (exaggerated for clarity)

And ' " & [CoNameID] & " ' And

if the data type assumptions are wrong, you'll need to adjust the syntax
accordingly. also, there's no need to use the IsNull function. the Criteria
argument of a domain aggregate function is essentially a SQL WHERE clause
without the WHERE keyword, so you'd write it the same as for a query, with a
direct "Is Not Null" criteria.

my only question is, if the simple expression

=Count([DatePaid])

gives you the correct count in a textbox control in the subform's Footer
section, why are you not able to utilize that value, rather than going
through the exercise above?

hth

hi,

The only difference may be the linking fields you have in your subform
control. Is the subform only linked via [AcctID] ?
The subform is linked by [AcctID];[CoName];[PayeeID].
Is this the problem?

Yes, you need these fields to get the same filter for DCount(), so this
condition should work (one line):

"AcctID = " & [AcctID] & " And [CoName] = '" & [CoName] & "' And
[PayeeID] = " & [PayeeID] & " And Not IsNull([DatePaid])"

mfG
--> stefan <--

Well, my DCount expression now reads as follows:
TotalCount: DCount("*","tblMSsub","AcctID = " & [AcctID] And
"CoNameID] = '" & [CoNameID] And "PayeeID = " & [PayeeID] And " Not
IsNull([DatePaid])")

Notice that the quotes and brackets are different than what you had,
as I was getting a syntax error from what you specified.

I'm still getting the TOTAL records in tblMSsub...

stefan,

I want you to know that I have really appreciated your help. But at
this point, I think we are spinning our wheels. I'm not sure that I
have communicated the problem fully, but a break from this might
help. I am going to close out this "Slow Code" message, take a break
for about a week, then start a new more distinct message to restate
the problem as I now see it (and after some more testing).

Thanks very much for your help. I'm sorry we couldn't solve the
problem, but I'll try again at another time.