Calculating the missing number

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.

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

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

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.

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.

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!


Tom van Stiphout wrote:
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.

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

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

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!


Tom van Stiphout wrote:
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.

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.


Tom van Stiphout wrote:
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.

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

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.


Tom van Stiphout wrote:
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.

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

Sorry Tom, I accidentally posted the same reply twice!

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

On Sun, 21 Mar 2010 16:49:02 GMT, "Danishham via AccessMonster.com"
u53905@uwe 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.
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.


Tom van Stiphout wrote:
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.

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.

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

Thanks.

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

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

John W. Vinson [MVP]