UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Text Field To Count Un Clicked Check Box, Access 2010    
 
   
Alhakeem1977
post Aug 19 2017, 11:10 AM
Post#1



Posts: 64
Joined: 8-July 17



what code I have to put in a subform text field called (txtReceived) to show me how many number of ( un clicked [Received] check box ).

Thanks a lot

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Jeff B.
post Aug 19 2017, 12:12 PM
Post#2


UtterAccess VIP
Posts: 9,885
Joined: 30-April 10
From: Pacific NorthWet


With Access, it all starts with the data. You're describing check boxes and text boxes, which sound like they come from a form. Please describe the underlying data structures (tables).

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Alhakeem1977
post Aug 19 2017, 01:16 PM
Post#3



Posts: 64
Joined: 8-July 17



I am sorry I am new at Ms Access, if know what you mean.
Actually the table called (tblFacilityRegister) it works as an electronic register or log book between two Departments one send a document and the another Department receive it, what I want to do is once the next department open the database in main form called (Navigation Form)will see the number of documents to be received and once the user click the check box as received it will deduct the number in the text field my I can name it as txtReceived.

For any explanation please do not hesitate to post it.

Thanks in advance

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
GroverParkGeorge
post Aug 19 2017, 04:27 PM
Post#4


UA Admin
Posts: 31,245
Joined: 20-June 02
From: Newcastle, WA


It's actually a bit more complicated, I think, because I assume your form is filtered. In other words, I would think you use a query to provide the records shown in the form. Is that correct?

So, we're interested in the records in that recordset, which is a subset of all of the records in that table.

Also, I assume that this field in the table --the one called "Received" is a Yes/No (or boolean) datatype field, meaning it can have one of two values, "yes" or "no'. Access actually stores these values as -1 for "yes" and 0 for "no". What you are looking for is the number of records in the form's current recordset where the value of the field called "Received" is 0. Those fields show up in the form in a control called "txtReceived", and that control is displayed as a check box. If the value of the field in a record is 0, then the checkbox appears to be unchecked. If the value of the field in a record is -1, then the checkbox appears to be checked.

In other words, the number of "unchecked checkboxes" in the form can be calculated from its recordset, using the values (either 0 or 1) in the field called "Received".

There are probably a few different ways to do that! Which one you prefer is something I can't really say, but here's one to consider.

Create a Function in the form's code module like this:

CODE
Private Function DocumentstobeReceivedCount() As Integer
    
     DocumentstobeReceivedCount = DCount("Received", "TheNameofTheQuerytoWhichtheFormIsBoundGoesHere", "TheNameofTheQuerytoWhichtheFormIsBoundGoesHere.Received = 0")
     Me.txtReceived = DocumentstobeReceivedCount

End Function


Call this function in the Form's Current event.

--------------------
Go to the top of the page
 
Alhakeem1977
post Aug 20 2017, 02:17 PM
Post#5



Posts: 64
Joined: 8-July 17



Thanks for your reply, I tried the code but it highlights Me. I got a compile error "Invalid use of Me keyword"

Is there a way to do it in Expression Builder?

CODE

Option Compare Database
Option Explicit
Private Function DocumentstobeReceivedCount() As Integer
    
     DocumentstobeReceivedCount = DCount("Received", "QryFacilityTsks", "QryFacilityTsks.Received = 0")
     Me.txtReceive = DocumentstobeReceivedCount

End Function

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
GroverParkGeorge
post Aug 20 2017, 02:19 PM
Post#6


UA Admin
Posts: 31,245
Joined: 20-June 02
From: Newcastle, WA


Oh. This function is NOT in the same form?

When I said, "Create a Function in the form's code module like this:"

I was referring to the form where you have the checkboxes. Isn't that where you want to run this?

If this is supposed to run from a main form and check the control on a subform within that main form, reference it like this instead:

Me.sfrmTheNameOfYourSubformGoesHere.txtReceive = DocumentstobeReceivedCount

--------------------
Go to the top of the page
 
Alhakeem1977
post Aug 20 2017, 02:30 PM
Post#7



Posts: 64
Joined: 8-July 17



Yes dear it's outside the form in "NavDC" form.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Alhakeem1977
post Aug 20 2017, 02:44 PM
Post#8



Posts: 64
Joined: 8-July 17



I got the same error I made it as the both below:

CODE
Me.NavDC.txtReceive = DocumentstobeReceivedCount


and I tried this too

CODE
Me.Form_NavDC.txtReceive = DocumentstobeReceivedCount

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
GroverParkGeorge
post Aug 20 2017, 02:50 PM
Post#9


UA Admin
Posts: 31,245
Joined: 20-June 02
From: Newcastle, WA


You have a main form named: ?

In this main form, you have a subform named: ?

On this subform, you have a control named: txtReceive

--------------------
Go to the top of the page
 
Alhakeem1977
post Aug 20 2017, 03:09 PM
Post#10



Posts: 64
Joined: 8-July 17



The main form named: NavigationForm

In this main form, I have a Navigation page named: NavDC , has the control named: txtReceive , I want it to be their.

sorry to interrupt you.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
GroverParkGeorge
post Aug 20 2017, 03:18 PM
Post#11


UA Admin
Posts: 31,245
Joined: 20-June 02
From: Newcastle, WA


Okay, this should work for a function on the main form, i.e. NavigationForm

Me.NavDC.txtReceive

However, since this is a navigation form, not a standard Access form, I'm going to need to review if the syntax might be different. Give me a little while, please to sort it out.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Aug 20 2017, 03:47 PM
Post#12


UA Admin
Posts: 31,245
Joined: 20-June 02
From: Newcastle, WA


I guess this depends on how your forms are actually set up. The Navigation form is a bit of a complication.

You have a navigation form named "NavigationForm", correct?

One of the navigation tabs in this form is called "NavDC", correct?

This tab contains a subform, also named "NavDV", correct?

Within the subform named "NavDC", is there a nested subform?

Which form contains the control named "txtReceive"? Which form contains the function you created?

--------------------
Go to the top of the page
 
tina t
post Aug 20 2017, 09:45 PM
Post#13



Posts: 5,197
Joined: 11-November 10
From: SoCal, USA


QUOTE
what code I have to put in a subform text field called (txtReceived) to show me how many number of ( un clicked [Received] check box ).

okay, i'll make the following assumptions:

1. the [Received] checkbox is in the same subform, and is bound to a Yes/No field in the subform's RecordSource (the table or query that the subform is bound to).
2. the "text field" called [txtReceived] is actually an unbound textbox control in the subform. if it's not in the subform's Footer section, you can move it there.

if the above assumptions are correct, you can try the following solution, instead of using a VBA function: add an expression to the ControlSource property of the textbox control called [txtReceived], as

=Sum(IIf([Received]=True,0,1))

each time you checkmark the [Received] checkbox in a record in the subform, the number shown in textbox control [txtReceived] should decrease by one, automatically. if the number does not update automatically, then add a line of code to the checkbox control's AfterUpdate event procedure, as

Me!txtReceived.Requery

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
Alhakeem1977
post Aug 21 2017, 05:28 AM
Post#14



Posts: 64
Joined: 8-July 17



Dear GroverParkGeorge my answers after your questions:

1. You have a navigation form named "NavigationForm", correct?
Yes

2. One of the navigation tabs in this form is called "NavDC", correct?
Yes

3. This tab contains a subform, also named "NavDV", correct?
No, it has a button to open a separate form named: frmFacilityDCRegister it contains the control named: Receive, the form Record Source: QryFacilityTsks.

Tab "NavDC" has the text field named txtReceive, I want to get the number of documents to be received in the NavDC

4. Within the subform named "NavDC", is there a nested subform?
Sorry I don't know what you mean with nested ? But I think answer No. 3 is Ok to explain the case.

5. Which form contains the control named "txtReceive"?
Answer No. 3.

6. Which form contains the function you created?
it's NavDC.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Alhakeem1977
post Aug 21 2017, 06:24 AM
Post#15



Posts: 64
Joined: 8-July 17



Dear tina,
Thanks tina for your reply.
Please see my reply to GroverParkGeorge then you will know what I want.

By the way I tried the expression builder code it's works fine if I place it in frmFacilityDCRegister text field, but I want it to be in a text field named: txtReceive in NavDC as I explain to GroverPark.

Thanks in advance. thanks.gif

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
tina t
post Aug 21 2017, 12:04 PM
Post#16



Posts: 5,197
Joined: 11-November 10
From: SoCal, USA


QUOTE
By the way I tried the expression builder code it's works fine if I place it in frmFacilityDCRegister text field, but I want it to be in a text field named: txtReceive in NavDC as I explain to GroverPark.

okay, Alhakeem, i'll leave that for you and George to work out. i'm glad it worked for you when you tested it, though - you've learned a little trick that you may find useful at some point in the future. :)

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
Alhakeem1977
post Aug 21 2017, 02:51 PM
Post#17



Posts: 64
Joined: 8-July 17



QUOTE
okay, Alhakeem, i'll leave that for you and George to work out. i'm glad it worked for you when you tested it, though - you've learned a little trick that you may find useful at some point in the future. smile.gif

I think you can continue with us to solve it I used the expression builder you provided to me in the same form I prefer if you can recommend to do some trick because I can't do it myself my George can help too.

Thanks dears

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
GroverParkGeorge
post Aug 22 2017, 09:57 AM
Post#18


UA Admin
Posts: 31,245
Joined: 20-June 02
From: Newcastle, WA


"No, it has a button to open a separate form named: frmFacilityDCRegister it contains the control named: Receive, the form Record Source: QryFacilityTsks."

Okay, so you have a whole, separate form open? And that other form is the one you want to use to get the number of items checked?

--------------------
Go to the top of the page
 
Alhakeem1977
post Aug 22 2017, 12:12 PM
Post#19



Posts: 64
Joined: 8-July 17



Thanks dear for reply Mr. George.
QUOTE
Okay, so you have a whole, separate form open?

Yes I do.

QUOTE
And that other form is the one you want to use to get the number of items checked?

Yes I do.

As I mentioned earlier I used the expression builder that provided by Tina t in the same form (frmFacilityDCRegister) and it's works fine, but when I placed it in Tab "NavDC'' I got an error instead of the number of documents to be received.

Is it possible to use VBA Dcount function to do that? But I don't know how to do it.

I am sorry I tried to fix the private function you provided but I couldn't it's new to me, I know a little bit how to do the public function through the expression builder.

Forget about it you can continue your idea after my answers.
thanks.gif

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
GroverParkGeorge
post Aug 22 2017, 12:25 PM
Post#20


UA Admin
Posts: 31,245
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, this puts a different interpretation on the problem. (I wish we'd known this was a separate, pop up form earlier in the discussion. We ended up wasting time).

So the reference needs to be to the other form. Control referencing or FMS overview

It's not a true subform, as that term is used in Access development.

--------------------
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 03:47 AM