Solved

access: link textbox to checkbox in query

Posted on 2011-02-16
21
578 Views
Last Modified: 2012-08-13
I attach my dbse “Completion matters”

First, go to the Form
Go to tab called chaps.

On that from there are a number of text boxes and corresponding tick boxes.

When I tick a box the text box turns colour red.

What I want to do is make a query that tells me which of the boxes is not red or
alternively is not ticked against.

At the moment there is no other code connection between thew boxes other than the colour red/ black.

The “ colour” code operates on the update of the check box.

What is the best way to run a query based on what I want to do, which is to tell me which text boxes do not have a tick against them?

Thanks



practice-completionDatabase.mdb
0
Comment
Question by:topUKlawyer
  • 11
  • 10
21 Comments
 
LVL 11

Expert Comment

by:RgGray3
ID: 34908706
Your question is a little confusing...

Let me restate it and see if I have it right....

You want to create a query, that identifies which checks are not selected.

Are you looking to return full records for any record that has ANY check not selected

If so...   simply insert FALSE in the criteria section of your query under each of the columns...
To clarify...   if you want any record that does not have ALL of the checks selected
Put a False under each column but on seperate rows in that column

0
 

Author Comment

by:topUKlawyer
ID: 34908737
Sorry to be confusing....

I would like ANY record which has an unchecked text box on that form showing up in the query/report
0
 

Author Comment

by:topUKlawyer
ID: 34908768
no thats not true apologies...

I want any record iin the query that HAS a black entry (ie has an entry that is unchecked) to show up

BUT NOT any text box entry on that tab which is checked/red
0
 

Author Comment

by:topUKlawyer
ID: 34908790
blank text box and no tick doesnt show up

red text and tick shows up.
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34908821
FYI...

You can manage the color logic without the code...

Use conditional formatting...

Select the textbox that is associated with a particular checkbox

Select the [to whom sent 1] text control
Select Format from the menu and Conditional formatting from there.

You will get a dialog box that allows you to create different formatting based upon your logic
in this case...  if [Check325] = True    

BTW,  for clarity I would give your check boxes that make it easy to see the relationship between the check and text controls  IE txtWhoSent1 and chkWhoSent1

If you use conditional formatting you no longer require code to manage the "Flag" nature of your color coding

0
 

Author Comment

by:topUKlawyer
ID: 34908833
forgive me,

blank text box and no tick doesnt show up

BLACK text and no tick shows up.

RED text and tick is excluded

0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34908858
Go back to my original post...  in your query  (names were changed for simplicity)

Check1    Check2   Check3   Check4
---------|----------|---------|---------
False
               False
                               False
                                             False

A seperate false for each column on a seperate row in the query builder grid
0
 

Author Comment

by:topUKlawyer
ID: 34908859
rationale is that where my cashier sees a BLACK text and NO tick shes knows to enter it inot separate accounts.

When shes entered its tocked and turns red

BUT she wants a list of unticked BLACK items to show whats remainng to be entered

phew.... thanks
0
 

Author Comment

by:topUKlawyer
ID: 34908904
cool sounds like i am over complicating it.

Just for my info... i get the false bit but why the separate row and whats happening there...
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34908921
OK... for clarity...

you need to identify A) If the text Box is Blank...  
                       AND B)there is NO tick

OR

you need to identify A) If the text Box is Blank...  
                         OR B)there is NO tick

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Expert Comment

by:RgGray3
ID: 34908942
If you place the falses on the same line...  they become an AND  instead of an OR

Meaning...

ALL of the 4 fields would need to be false to become part of your result
0
 

Author Comment

by:topUKlawyer
ID: 34908948
not sure if its there yet..

because someone else is entering the black entries she needs to filter IN the BLACK entries and filter OUT the blank ones.
0
 

Author Comment

by:topUKlawyer
ID: 34908965
thanks for the explanation for the rows.
0
 

Author Comment

by:topUKlawyer
ID: 34909010
the reason why she needs to filter out the BLANK ones is that she needs to locate those black entries

would it something like this:

if contents of text box <>0 then checkbox is something so she could pick up the checkboxes.

0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34909044
OK...  we are getting somewhere...  good.

Let's clarify the question...  then the logic will follow..

You need to separate Those where the Check is not selected AND the corresponding field is blank..  correct...?

If so....  now we use field pairs in the criteria. (I'll do this for 2 sets....   the same logic would need to be done for the additional pairs)

  Check1        Text1      Check2       Text2
-----------|------------|-----------|-----------
False         is null or ""
                                     False         is null or ""

Depending on how your table is constructed and validity rules...   I am assuming that the text field can be blank or hold an empty string...  (there is a difference)  and there is no penalty in testing for both

Do the same for Pairs 3 and 4 and you should have it.
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34909062
If you are looking for those that are Blank and have a checkmark...  simply replace the false with True
0
 

Author Comment

by:topUKlawyer
ID: 34909153
I think we are getting there too i need to FIND those text boxes with "something in them"

AND which dont have a check mark ticked.

please forgive my logical flaws.

It would appear from what you say the colour is a red herring (joke)

0
 
LVL 11

Accepted Solution

by:
RgGray3 earned 500 total points
ID: 34909269
UGGH....   (but funny)

Yes...  leave color out of the logic...

Checkmarks are True or False
Data is there or it's NOT (Null or Empty)

SO....  you logic becomes IS NOT NULL  AND  (Not"") in the text fields
           or we could use LEN()
           (but I don't want to complicate it now...  just let that one hang out for a while)

Now I have just gotten more confused....
It appears that the checkboxes are not "Bound" to a data field

Am I missing something?
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34909301
Just double checked...
There is no Control Source for the check marks...   this would need to be stored somewhere to perform logic against it...
0
 

Author Comment

by:topUKlawyer
ID: 34909425
thats ok i can fix that just go back to the table and make some new ones

thanks for pointing that out...i'd forgotten

i will award points to you and if i get into any diffculties i will post a re-comment but i can see where im going......

ty

0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34909594
Good luck and let me know if you have additional problems

Rich
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now