Allow me to document it a little better:
1- You have textbox controls on your Form that are named Dep1, Dep2,
Dep3......Dep19
2- Create an unbound textbox control named "txtDepositCount". This control
will have the result of the number of deposits counted
3- With the Form open in design view select the menu option View > Code
4- Copy paste the following code within the VBA module
Function CountNumberOfDeposits()
' i & j are used as counters
Dim i As Integer, j As Integer
' set j = 0 initially
j = 0
' loop thru ALL the controls on the Form
' you never know you made more deposits in the future
' like Dep20, Dep22, Dep23.... plus it less lines of code
For i = 0 To Me.Count - 1
' Dep1, Dep2,etc.. are Textbox controls so test to see if
' control is a textbox
If TypeOf Me(i) Is TextBox Then
' check to make sure the 1st three characters
' of the textbox control is named "Dep"
If Left$(Me(i),3) = "Dep" Then
' check to see if "Dep##" is not null
' don't want to count a control that is Null
If Not IsNull(Me(i)) Then
' start your counter
j = j + 1
End If
End If
If
Next i
txtDepositCount = j
End Function
' xxxxxx end of code xxxxxxxx
5- Now to run the above code on your Form you can do it a couple of ways.
You can place the following:
=CountNumberOfDeposits()
in the OnClick event of command button on your Form. When the user wants
to see the updated Deposit count appear in the textbox control
"txtDepositCount" they would click the button
OR
you can place the same in the AfterUpdate event for each of the textbox
controls Dep1, Dep2, Dep3, etc..... So that has the user enters a value in
one the textbox controls the function will run returning the value to the
textbox control "txtDepositCount"
I hope this helps. I'm sorry about the short explanation earlier.
Eric
--------------------
| Reply-To: "Gulf Coast Electric" <
[email protected]>
| From: "Gulf Coast Electric" <
[email protected]>
| Newsgroups: microsoft.public.access.forms
| References: <
[email protected]>
<
[email protected]>
| Subject: Re: Count Number of not null fields on form.
| Lines: 110
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <
[email protected]>
| NNTP-Posting-Host: 64.123.206.21
| X-Complaints-To: (e-mail address removed)
| X-Trace: newssvr23.news.prodigy.com 1076421140 ST000 64.123.206.21 (Tue,
10 Feb 2004 08:52:20 EST)
| NNTP-Posting-Date: Tue, 10 Feb 2004 08:52:20 EST
| Organization: SBC
http://yahoo.sbc.com
| X-UserInfo1:
FKPO@MONRZVYBP\YMBN@^RX@USXB@DTMNHWB_EYLJZ]BGIELNVUEAE[YETZPIWWI[FCIZA^NBFXZ
_D[BFNTCNVPDTNTKHWXKB@X^B_OCJLPZ@ET_O[G\XSG@E\G[ZKVLBL^CJINM@I_KVIOR\T_M_AW_
M[_BWU_HFA_]@A_A^SGFAUDE_DFTMQPFWVW[QPJN
| Date: Tue, 10 Feb 2004 13:52:20 GMT
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.su
l.t-online.de!t-online.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!in
..100proofnews.com!in.100proofnews.com!prodigy.com!newsmst01.news.prodigy.com
!prodigy.com!postmaster.news.prodigy.com!newssvr23.news.prodigy.com.POSTED!5
36158fa!not-for-mail
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.forms:254512
| X-Tomcat-NG: microsoft.public.access.forms
|
| How would I use this? What is J and i ?
| Need to add up Dep1 thru Dep19 Plus cash fields if they have a value
in
| them so I can count the number of deposits.
|
|
| --
| -------------------------------------------------------------------------
| Thanks for your Help.
| | > Hi,
| >
| > I take it that your information is on a Form. You can Loop through the
| > controls on the Form, example:
| >
| > Dim i As Integer, j As Integer
| >
| > j = 0
| > For i = 0 To Me.Count - 1
| > If TypeOf Me(i) Is TextBox Then
| > If Not IsNull(Me(i)) Then
| > j = j + 1
| > End If
| > End If
| > Next i
| > MsgBox j
| >
| > I hope this helps! If you have additional questions on this topic,
please
| > respond back to this posting.
| >
| >
| > Regards,
| >
| > Eric Butts
| > Microsoft Access Support
| >
| > "Microsoft Security Announcement: Have you installed the patch for
| > Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
| > you to review the information at the following link regarding Microsoft
| > Security Bulletin MS03-026
| > <
http://www.microsoft.com/security/security_bulletins/ms03-026.asp>
and/or
| > to visit Windows Update at <
http://windowsupdate.microsoft.com/> to
| install
| > the patch. Running the SCAN program from the Windows Update site will
| help
| > to insure you are current with all security patches, not just MS03-026."
| >
| >
| > --------------------
| > | Reply-To: "Gulf Coast Electric" <
[email protected]>
| > | From: "Gulf Coast Electric" <
[email protected]>
| > | Newsgroups: microsoft.public.access.forms
| > | Subject: Count Number of not null fields on form.
| > | Lines: 17
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Message-ID: <
[email protected]>
| > | NNTP-Posting-Host: 66.142.130.26
| > | X-Complaints-To: (e-mail address removed)
| > | X-Trace: newssvr11.news.prodigy.com 1076360952 ST000 66.142.130.26
(Mon,
| > 09 Feb 2004 16:09:12 EST)
| > | NNTP-Posting-Date: Mon, 09 Feb 2004 16:09:12 EST
| > | Organization: SBC
http://yahoo.sbc.com
| > | X-UserInfo1:
| >
|
FKPGW]KEPBUYRWX[\JIBOFXBWR\HPCTL@XT^OBPLAH[\RYIBK^RAQFW[ML\THRCKV^GGZKJMGV^^
| >
|
_JSCFFUA_QXFGVSCYRPILH]TRVKC^LSN@DX_HCAFX__@J\DAJBVMY\ZWZCZLPA^MVH_P@\\EOMW\
| > YSXHG__IJQY_@M[A[[AXQ_XDSTAR]\PG]NVAQUVM
| > | Date: Mon, 09 Feb 2004 21:09:12 GMT
| > | Path:
| >
|
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.su
| >
|
l.t-online.de!t-online.de!newsfeed.freenet.de!newspeer1.nwr.nac.net!in.100pr
| >
|
oofnews.com!in.100proofnews.com!prodigy.com!newsmst01.news.prodigy.com!prodi
| >
|
gy.com!postmaster.news.prodigy.com!newssvr11.news.prodigy.com.POSTED!not-for
| > -mail
| > | Xref: cpmsftngxa07.phx.gbl microsoft.public.access.forms:254427
| > | X-Tomcat-NG: microsoft.public.access.forms
| > |
| > | I have a Deposit Slip form where I enter deposits, some cash and some
| > | checks.
| > | The deposits are numbered Dep1 thr Dep19
| > | Also there is one named cash
| > | If there is a value in any of these fields I need it to add up this
| > number.
| > | Say cash has a value of $50.00 and Dep1 has a value of $100.00 , then
| the
| > | number count would be (2)
| > | The field that I need this in is named Number Of Deposits it is a
| bound
| > | control.
| > | Can this be done and if so how?
| > |
| > |
| > | --
| >
| |
-------------------------------------------------------------------------
| > | Thanks for your Help.
| > |
| > |
| > |
| >
|
|
|
