If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Calculating the missing number
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. |
#3
|
|||
|
|||
Calculating the missing number
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 |
#4
|
|||
|
|||
Calculating the missing number
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. |
#5
|
|||
|
|||
Calculating the missing number
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 |
#6
|
|||
|
|||
Calculating the missing number
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 |
#7
|
|||
|
|||
Calculating the missing number
Sorry Tom, I accidentally posted the same reply twice!
-- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
Calculating the missing number
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. |
#9
|
|||
|
|||
Calculating the missing number
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 |
#10
|
|||
|
|||
Calculating the missing number
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] |
|
Thread Tools | |
Display Modes | |
|
|