Calculating the missing number

On Sun, 21 Mar 2010 18:08:39 -0600, John W. Vinson
wrote:

Hi John,
You may be onto something, and perhaps you understand better what the
OP wants to do. I tried but I am unsuccessful.
-Tom.


On Sun, 21 Mar 2010 11:13:46 -0700, Tom van Stiphout
wrote:

I wrote: "...Also *ADD* one line after the MsgBox...". The way you are
describing it you put the line in the wrong place. It should be:
if intCount 2 then
MsgBox "Yo! We need " & 2-intCount & " more entries."
Cancel = True
end if

This event will always fire if something changed in the record. If
nothing has changed, it will not. Will that work for you? If not, we
have to use the Form_Current event but this is not ideal.

Tom, if the beforeupdate is cancelled if there are fewer than two entries, the
user will never be able to get to two - because it will block them from
entering the first entry!

It's sort of a chicken-and-egg problem: you can't put in two until you've
first put in one, so the state where there are two few entries must be (at
least temporarily) allowed. I don't think that you can *prohibit* it, the best
you can do (short of temp tables) is warn the user.

So.......... any ideas on this anymore? My current solution has a line where
the user has to manually write in the missing number............


Tom van Stiphout wrote:
Hi John,
You may be onto something, and perhaps you understand better what the
OP wants to do. I tried but I am unsuccessful.
-Tom.

I wrote: "...Also *ADD* one line after the MsgBox...". The way you are
describing it you put the line in the wrong place. It should be:
[quoted text clipped - 15 lines]
least temporarily) allowed. I don't think that you can *prohibit* it, the best
you can do (short of temp tables) is warn the user.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

On Thu, 25 Mar 2010 23:21:47 GMT, "Danishham via AccessMonster.com"
u53905@uwe wrote:

So.......... any ideas on this anymore? My current solution has a line where
the user has to manually write in the missing number............


It may be covering old ground but let's start over.

What is the Recordsource of your mainform?
What is the Recordsource of your subform?
What exactly do you want to happen: do you want to just warn the user that
they haven't entered enough data? Do you want to make it impossible to add one
record but possible to add two records (if so, could you explain how you would
put two cans of peas into a shopping bag when you're not allowed to put one
can into the bag)?
--

John W. Vinson [MVP]

Recordsource of main form is People table
Subform is Applications
Sub-subform is Recommendations

Recommendations table is where I need the solution.

What I want to happen is:
Sub-subform Recommendations lists the recommender names and has no limit. I
have an unbound Count box on this subform that states how many recommenders
have sent in recommendations on an applicant. A letter (report) is generated
from this form that states if the applicant has "missing" recommendations,
meaning s/he has less than the required "2" recommendations. I need some
type of calculation in Access that will compare the number of recommendations
received to the required two. That figure will be generated on the "missing
information" report that is sent to the applicant. I do not want a warning
to the user, just an automatically calculated figure that will appear on the
letter.

(and no, I do not want to limit the number of records able to be added. Some
applicants receive 3 or more letters. But they all need at least 2, even if
the recommendations come in at different times)

Hope this makes sense....


John W. Vinson wrote:
So.......... any ideas on this anymore? My current solution has a line where
the user has to manually write in the missing number............

It may be covering old ground but let's start over.

What is the Recordsource of your mainform?
What is the Recordsource of your subform?
What exactly do you want to happen: do you want to just warn the user that
they haven't entered enough data? Do you want to make it impossible to add one
record but possible to add two records (if so, could you explain how you would
put two cans of peas into a shopping bag when you're not allowed to put one
can into the bag)?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

On Fri, 26 Mar 2010 02:52:00 GMT, "Danishham via AccessMonster.com"
u53905@uwe wrote:

Recordsource of main form is People table
Subform is Applications
Sub-subform is Recommendations

Recommendations table is where I need the solution.

What I want to happen is:
Sub-subform Recommendations lists the recommender names and has no limit. I
have an unbound Count box on this subform that states how many recommenders
have sent in recommendations on an applicant. A letter (report) is generated
from this form that states if the applicant has "missing" recommendations,
meaning s/he has less than the required "2" recommendations. I need some
type of calculation in Access that will compare the number of recommendations
received to the required two. That figure will be generated on the "missing
information" report that is sent to the applicant. I do not want a warning
to the user, just an automatically calculated figure that will appear on the
letter.

(and no, I do not want to limit the number of records able to be added. Some
applicants receive 3 or more letters. But they all need at least 2, even if
the recommendations come in at different times)

You can use the DCount() function to count the number of *records in the
table* (don't try to count the number of records on a form). The textbox on
the subform could have a control source like

=DCount("*", "[RecommendationsTable]", "[ApplicationID] = " & [ApplicationID])

using your actual table and fieldnames of course.
--

John W. Vinson [MVP]

Hi John,
I put it into a text box and it generates a number on ApplicationIDs that
have 1 or more recommendations, but it states "#Error" when there are no
recommendations yet. It should say "2" are needed.

John W. Vinson wrote:
Recordsource of main form is People table
Subform is Applications
[quoted text clipped - 17 lines]
applicants receive 3 or more letters. But they all need at least 2, even if
the recommendations come in at different times)

You can use the DCount() function to count the number of *records in the
table* (don't try to count the number of records on a form). The textbox on
the subform could have a control source like

=DCount("*", "[RecommendationsTable]", "[ApplicationID] = " & [ApplicationID])

using your actual table and fieldnames of course.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

On Fri, 26 Mar 2010 03:33:01 GMT, "Danishham via AccessMonster.com"
u53905@uwe wrote:

Hi John,
I put it into a text box and it generates a number on ApplicationIDs that
have 1 or more recommendations, but it states "#Error" when there are no
recommendations yet. It should say "2" are needed.

Where is the information that this application needs two recommendations
stored? Or is it a constant for all cases?

If it is, try calculating the recommendations still needed:

=2 - IIF(NZ(DCount("*", "[RecommendationsTable]", "[ApplicationID] = " &
[ApplicationID]) = 2, NZDCount("*", "[RecommendationsTable]",
"[ApplicationID] = " & [ApplicationID])) , 2)



--

John W. Vinson [MVP]

Now, this looks a lot closer to what I was envisioning. Yes, the 2 is
constant.
I put in what you told me, modifying to reflect the correct name of the table,
so the line now reads:


=2-IIf(NZ(DCount("*","[Recommendations]","[ApplicationID] = " &
[ApplicationID])=2,NZDCount("*","[Recommendations]","[ApplicationID] = " &
[ApplicationID])),2)

But it is giving me a #Name error now.....
I also tried changing the table reference to "Recommenders" but no success.

What am I doing wrong?


John W. Vinson wrote:
Hi John,
I put it into a text box and it generates a number on ApplicationIDs that
have 1 or more recommendations, but it states "#Error" when there are no
recommendations yet. It should say "2" are needed.

Where is the information that this application needs two recommendations
stored? Or is it a constant for all cases?

If it is, try calculating the recommendations still needed:

=2 - IIF(NZ(DCount("*", "[RecommendationsTable]", "[ApplicationID] = " &
[ApplicationID]) = 2, NZDCount("*", "[RecommendationsTable]",
"[ApplicationID] = " & [ApplicationID])) , 2)


--
Message posted via http://www.accessmonster.com

On Fri, 26 Mar 2010 14:23:22 GMT, "Danishham via AccessMonster.com"
u53905@uwe wrote:

=2-IIf(NZ(DCount("*","[Recommendations]","[ApplicationID] = " &
[ApplicationID])=2,NZDCount("*","[Recommendations]","[ApplicationID] = " &
[ApplicationID])),2)

But it is giving me a #Name error now.....
I also tried changing the table reference to "Recommenders" but no success.

What am I doing wrong?

Probably just using my suggested table and fieldnames instead of yours. I
cannot see your database, so I am posting *EXAMPLES OF HOW TO THINK* rather
than answers to blindly copy and paste.

To explain my logic: you want to count the number of recommendations for a
given application. My *blind guess* was that the recommendations were in a
table named Recommendations, and that the recommendations for a given
application would all have the same ApplicationID.


The expression
DCount("*","[Recommendations]","[ApplicationID] = " & [ApplicationID])

will count the number of records on that basis.

Wrapping that expression in NZ():

NZ(DCount("*","[Recommendations]","[ApplicationID] = " & [ApplicationID]))

will return 0 if there are no recommendations yet.

Subtracting that count from 2 will get the number yet to be met.

The IIF() checks to see if there are already two or more recommendations. It
should have a 0 rather than a 2 at the very end of the expression.

The fieldnames and tablename are probably different in your database, so
you'll need to change them.
--

John W. Vinson [MVP]

John, excellent explanation of what was going on. Thank you for taking the
time to describe the way it all works together. I think we were missing one
parentheses too, but I used what you said and it works! (Except when there
are no recommendation records in that table for that applicant yet, then it
displays ###### but that's ok.)

Here is the final working statement:

=2-IIf(NZ(DCount("*","[Recommendations]","[ApplicationID] = " &
[ApplicationID]))=2,NZ(DCount("*","[Recommendations]","[ApplicationID] = " &
[ApplicationID])),0)

Thanks a million!


John W. Vinson wrote:
=2-IIf(NZ(DCount("*","[Recommendations]","[ApplicationID] = " &
[ApplicationID])=2,NZDCount("*","[Recommendations]","[ApplicationID] = " &
[quoted text clipped - 4 lines]

What am I doing wrong?

Probably just using my suggested table and fieldnames instead of yours. I
cannot see your database, so I am posting *EXAMPLES OF HOW TO THINK* rather
than answers to blindly copy and paste.

To explain my logic: you want to count the number of recommendations for a
given application. My *blind guess* was that the recommendations were in a
table named Recommendations, and that the recommendations for a given
application would all have the same ApplicationID.

The expression
DCount("*","[Recommendations]","[ApplicationID] = " & [ApplicationID])

will count the number of records on that basis.

Wrapping that expression in NZ():

NZ(DCount("*","[Recommendations]","[ApplicationID] = " & [ApplicationID]))

will return 0 if there are no recommendations yet.

Subtracting that count from 2 will get the number yet to be met.

The IIF() checks to see if there are already two or more recommendations. It
should have a 0 rather than a 2 at the very end of the expression.

The fieldnames and tablename are probably different in your database, so
you'll need to change them.

--
Message posted via http://www.accessmonster.com