Count field boxes that have a value in them (1 Viewer)

[mjwillyone]

Dear Friends,

I have searched the forum and did not find the answer to this particular question and wondered any anyone could help me.

I am trying to create a text box that has a formula which counts those field boxes on a form that contain a value. For instance, if the following fields contained the following values:

FirstName John
Spouse Saundra
Child1 Stacy
Child2 Craig
Child2

The above value that should be displayed as 4, since there are four entries. The 2nd child has no value in it and, therefore, is not calculated in the total.

I would need this to work for each form, never counting anything but the records on that form.

Thanks so much,
Mike

 

[ajetrumpet]

ummm...i think you mean CHILD3 as the empty one. access doesn't allow dup control names on a form.

at any rate though, put this in the afterupdate() of the form's event:

dim c as control, counter as double

counter =0

for each c in me.controls
   if typeof c is textbox then 'PUT ALL CONTROL TYPES YOU WANT TO CHECK ON THIS LINE
      if not isnull(c) or len(c) > 0 then
         counter = counter+1
      end if
   end if
next c

me.TextBoxName = counter

 

[mjwillyone]

Thanks for the help. I am wondering though what you mean by your comment about control types. The form contains many fields, not just the ones I want to count. How do I count only the ones I want?

Mike

 

[ajetrumpet]

Thanks for the help. I am wondering though what you mean by your comment about control types. The form contains many fields, not just the ones I want to count. How do I count only the ones I want?

Mike

when people usually say "field" and they're talking about a form in general, they usually mean the controls on the form that are bound to table fields. anyway, forms have lots of children, and they are classified as TYPES, just like those wondering first meetings in the armed forces where you get a "class" and get to be "classified".

a list of form controls can be found in the VBA editor. go to the window, and use this click stream:

view > object browser > under the "Classes" frame, ---> Accontroltype

that's a list of "stuff" you will see in forms at somepoint in your career. in the code i gave you, to add a control type to that line, reference it by using these names, minus the "ac" at the front of the name.

 

[mjwillyone]

I am wondering .... is there not an easier way to simply take a count of cells that don't have a null value?

Is there a simple formula that I can place in a text box that is not associated with a field in the table that I can use to count the cells that have values in them?

Here are the cell names: FirstName, Spouse, Child1, Child2, Child3 ...

Thanks,
Mike

 

[ajetrumpet]

I am wondering .... is there not an easier way to simply take a count of cells that don't have a null value?

Is there a simple formula that I can place in a text box that is not associated with a field in the table that I can use to count the cells that have values in them?

Here are the cell names: FirstName, Spouse, Child1, Child2, Child3 ...

Thanks,
Mike

i could give you a laudndry list of ways to do this, but the best way to do is the loop that I gave you. it virtually takes milliseconds to complete too, so there's nothing wrong with. by I guess, if you're looking for a real challenge, try messing around with source recordset, although that it totally not need, but does the same thing I already gave you.

 

[mjwillyone]

Ok ...

Well ... I think the only thing I don't understand of your code is this:

'PUT ALL CONTROL TYPES YOU WANT TO CHECK ON THIS LINE

Do I specify the names of the boxes that hold the data or the field names themselves? And can you give me an example of the code to use with a sample control name?

Mike

 

[ajetrumpet]

Ok ...

Well ... I think the only thing I don't understand of your code is this:

'PUT ALL CONTROL TYPES YOU WANT TO CHECK ON THIS LINE

Do I specify the names of the boxes that hold the data or the field names themselves? And can you give me an example of the code to use with a sample control name?

Mike

i'll give you a concrete answer if you tell me these two things?

*what are ALL T=the different control types that you have on you form?
*WHAT TYPES are you wanting to populate? e.g. - textboxes, checkbox, listboxes, etc...

if there is more than one type that needs a value dynamically, give spefcifics. once you do that for me, i'll give you an answer to your problem

 

[mjwillyone]

Thanks for the reply ... I hope this info will help:

Form Name: Form (There is only one!)

ALL of the control types are text boxes, both the ones in which I need to count and the one that I need to use to display the count.

I hope this helps,
Mike

 

[ajetrumpet]

Thanks for the reply ... I hope this info will help:

Form Name: Form (There is only one!)

ALL of the control types are text boxes, both the ones in which I need to count and the one that I need to use to display the count.

I hope this helps,
Mike

this is all wrong dude, and you and I both are getting wayyyyy to confused. what I would suggest from here, is you upload your database so others look. i have a sneaking suspicioun that you don't know how to ask the ? you want answered.

 

[mjwillyone]

I am sorry to be such a bother. I have never had this kind of problem in all of the 8 years that I have been using forums. I have attached my database.

The field in which I am wanting the count of people is called Person Count Box. I am wanting to find the person count (on each form) of the following fields:

Last Name, Spouse Name & Guest Name

Thanks,
Mike

 

[ajetrumpet]

You owe me my good friend.

 

[mjwillyone]

Yes... that is what I wanted. A simple formula to do the work, not a multi-line script. Thanks so much! So .. .I owe you huh ... well, I am going to be in London in a few days. You live in London?

Mike

 

[ajetrumpet]

Yes... that is what I wanted. A simple formula to do the work, not a multi-line script. Thanks so much! So .. .I owe you huh ... well, I am going to be in London in a few days. You live in London?

Mike

nope....i live in the bully state...US of A. we tell you what to do. everyday of my life I wish I lived elsewhere.

so...you owe me another way....a beer through the mail, some cutting hands for a cabin in the woods....etc.. you decide