Solved

access: link textbox to checkbox in query

Posted on 2011-02-16
21
579 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

932 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

11 Experts available now in Live!

Get 1:1 Help Now