Calculating the missing number

[Danishham via AccessMonster.com]

I searched the archives and can't quite obtain the answer I am looking for.

I have a table that allows for unlimited entries, but it needs to have at
least 2 entries per Person (already connected relationally in a one-to-many).
I need a calculated control for the form that can state how many more entries
the Person needs by counting the number of entries that are already in the
table.

For example, PersonA has 1 entry and the control would state that 1 more is
needed. However, Person B has 3 entries and the control would simply be
blank.

I plan to use the control in both a form and also a report.

I would really appreciate any insight you can provide to help me here.

Thanks.

 

[Tom van Stiphout]

On Sun, 21 Mar 2010 04:08:25 GMT, "Danishham via AccessMonster.com"

You could use the DCount function to count the number of records where
PersonID=X:
dim intCount as integer
intCount = dcount("myPKField", "myTable", "PersonID=" & Me.PersonID)
if intCount < 2 then
MsgBox "Yo! We need " & 2-intCount & " more entries."
end if

-Tom.
Microsoft Access MVP

 

[Danishham via AccessMonster.com]

Hi Tom,
That MsgBox is hilarious - they'd get a real kick out of that one at work! I
created an unbound txtbox and in the BeforeUpdate field, I entered your code,
amended as follows:

Private Sub Text9_BeforeUpdate(Cancel As Integer)

Dim intCount As Integer
intCount = DCount("RecommendationID", "Recommendations", "ApplicationID=" &
Me.ApplicationID)
If intCount < 2 Then
MsgBox "Yo! We need " & 2 - intCount & " more entries."
End If

End Sub


But the text box is blank. What am I doing wrong?
Also, I should note that I need the "missing" amount to actually appear in
the control and not just remind the user that more are needed....

Thanks!

You could use the DCount function to count the number of records where
PersonID=X:
dim intCount as integer
intCount = dcount("myPKField", "myTable", "PersonID=" & Me.PersonID)
if intCount < 2 then
MsgBox "Yo! We need " & 2-intCount & " more entries."
end if

-Tom.
Microsoft Access MVP

I searched the archives and can't quite obtain the answer I am looking for.

[quoted text clipped - 13 lines]

Thanks.

 

[Tom van Stiphout]

On Sun, 21 Mar 2010 04:49:16 GMT, "Danishham via AccessMonster.com"

The text box is blank? Which text box? This code does not refer to a
text box nor populate one.
Are you saying "the user leaves the amount textbox blank and I don't
want that"? If so, move the code to the Form_BeforeUpdate. Also add
one line after the MsgBox:
Cancel = True
This prevents the user from leaving the record until the validation
rule is satisfied.

-Tom.
Microsoft Access MVP

Hi Tom,
That MsgBox is hilarious - they'd get a real kick out of that one at work! I
created an unbound txtbox and in the BeforeUpdate field, I entered your code,
amended as follows:

Private Sub Text9_BeforeUpdate(Cancel As Integer)

Dim intCount As Integer
intCount = DCount("RecommendationID", "Recommendations", "ApplicationID=" &
Me.ApplicationID)
If intCount < 2 Then
MsgBox "Yo! We need " & 2 - intCount & " more entries."
End If

End Sub


But the text box is blank. What am I doing wrong?
Also, I should note that I need the "missing" amount to actually appear in
the control and not just remind the user that more are needed....

Thanks!

You could use the DCount function to count the number of records where
PersonID=X:
dim intCount as integer
intCount = dcount("myPKField", "myTable", "PersonID=" & Me.PersonID)
if intCount < 2 then
MsgBox "Yo! We need " & 2-intCount & " more entries."
end if

-Tom.
Microsoft Access MVP

I searched the archives and can't quite obtain the answer I am looking for.

[quoted text clipped - 13 lines]

Thanks.

 

[TheAdams via AccessMonster.com]

The text box I created when I created an unbound control on the form... ???
OK, I moved the code to the subform properties, running on BeforeUpdate. Now,
nothing pops up, but the user can't leave the record or save what was already
in it, yet the user has no idea why.

The situation here is that each ApplicationID needs 2 Recommendations to be
completed. These recommendations may be input at separate times until the
Application is complete, so the user needs to be able to enter and exit the
record at will, but when I generate a letter to the applicant, I need the
form (and subsequently the report) to say, "____ recommendations are missing
still" with the blank line being auto-calculated based on how many
recommendations have not been sent in yet.

I hope this makes sense - I really appreciate you putting in the time to help
me here.

The text box is blank? Which text box? This code does not refer to a
text box nor populate one.
Are you saying "the user leaves the amount textbox blank and I don't
want that"? If so, move the code to the Form_BeforeUpdate. Also add
one line after the MsgBox:
Cancel = True
This prevents the user from leaving the record until the validation
rule is satisfied.

-Tom.
Microsoft Access MVP

Hi Tom,
That MsgBox is hilarious - they'd get a real kick out of that one at work! I

[quoted text clipped - 34 lines]

 

[Danishham via AccessMonster.com]

The text box I created when I created an unbound control on the form... ???
OK, I moved the code to the subform properties, running on BeforeUpdate. Now,
nothing pops up, but the user can't leave the record or save what was already
in it, yet the user has no idea why.

The situation here is that each ApplicationID needs 2 Recommendations to be
completed. These recommendations may be input at separate times until the
Application is complete, so the user needs to be able to enter and exit the
record at will, but when I generate a letter to the applicant, I need the
form (and subsequently the report) to say, "____ recommendations are missing
still" with the blank line being auto-calculated based on how many
recommendations have not been sent in yet.

I hope this makes sense - I really appreciate you putting in the time to help
me here.

The text box is blank? Which text box? This code does not refer to a
text box nor populate one.
Are you saying "the user leaves the amount textbox blank and I don't
want that"? If so, move the code to the Form_BeforeUpdate. Also add
one line after the MsgBox:
Cancel = True
This prevents the user from leaving the record until the validation
rule is satisfied.

-Tom.
Microsoft Access MVP

Hi Tom,
That MsgBox is hilarious - they'd get a real kick out of that one at work! I

[quoted text clipped - 34 lines]

 

[Danishham via AccessMonster.com]

Sorry Tom, I accidentally posted the same reply twice!

 

[Tom van Stiphout]

On Sun, 21 Mar 2010 16:49:02 GMT, "Danishham via AccessMonster.com"

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.
Microsoft Access MVP

The text box I created when I created an unbound control on the form... ???
OK, I moved the code to the subform properties, running on BeforeUpdate. Now,
nothing pops up, but the user can't leave the record or save what was already
in it, yet the user has no idea why.

The situation here is that each ApplicationID needs 2 Recommendations to be
completed. These recommendations may be input at separate times until the
Application is complete, so the user needs to be able to enter and exit the
record at will, but when I generate a letter to the applicant, I need the
form (and subsequently the report) to say, "____ recommendations are missing
still" with the blank line being auto-calculated based on how many
recommendations have not been sent in yet.

I hope this makes sense - I really appreciate you putting in the time to help
me here.

The text box is blank? Which text box? This code does not refer to a
text box nor populate one.
Are you saying "the user leaves the amount textbox blank and I don't
want that"? If so, move the code to the Form_BeforeUpdate. Also add
one line after the MsgBox:
Cancel = True
This prevents the user from leaving the record until the validation
rule is satisfied.

-Tom.
Microsoft Access MVP

Hi Tom,
That MsgBox is hilarious - they'd get a real kick out of that one at work! I

[quoted text clipped - 34 lines]

Thanks.

 

[Danishham via AccessMonster.com]

I have tried it as you said. it does not do what I want it to do. I want a
little box that autocalculates how many Recommendations are missing as
compared to the 2 that are needed per file. When I put your code into the
Form, I get locked into the field when I try to add/edit it.

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.
Microsoft Access MVP

The text box I created when I created an unbound control on the form... ???
OK, I moved the code to the subform properties, running on BeforeUpdate. Now,

[quoted text clipped - 29 lines]

 

[John W. Vinson]

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.

 

[Tom van Stiphout]

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

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.

 

[Danishham via AccessMonster.com]

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

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.
[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.

 

[John W. Vinson]

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)?

 

[Danishham via AccessMonster.com]

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]

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.

 

[Danishham via AccessMonster.com]

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.

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.

 

[John W. Vinson]

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)

 

[Danishham via AccessMonster.com]

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?

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]

=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.

 

[Danishham via AccessMonster.com]

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!

=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.