By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 670 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

How to hide row when multiple check boxes are checked.

P: 7
MS Office 2016 on Windows 10 platform. I am using access so that a team of purchasers can generate purchase order numbers every time they need to order something. I am using this system to track that the product was received, an invoice was entered into our financial database, and that we received a confirmation for the order. I have a check box for each of those 3 events. I would like the row to become hidden once all 3 boxes are checked so I can more quickly get a picture of what is still outstanding. Thanks.
Attached Files
File Type: pdf po generator.pdf (111.8 KB, 83 views)
Mar 1 '17 #1

✓ answered by PhilOfWalton

Excellent.

So although this is not essential, in the original TABLE design, I strongly urge you to change the name of PO# (AS I said, "#" is a reserved word. Change it to say PoNo
Equally spaces in field names are a pain as they have to be surrounded with square brackets [] when reffering to them. So I suggest that Acquired Date becomes AcquiredDate, Job Name becomes JobName, ans similarly with the other fields with spaces in them.

When you have done that, in the same way you dragged the "*" into column 1, drag ConfirmationReceieved into Column 2, drag EnteredEBMS into Column 3 and OrderRecieved into Column 4.

In column 2 on the line marked "Criteria" (Line 5) type the word "false" without the quotes.

In column 3 on the line marked "or" (Line 6) type the word "false" without the quotes.

In column 4 on the line 7 type the word "false" without the quotes.

In the line marked "Show" (Line 4) untick the box in columns 2,3 & 4.

So what we should have is the first column says show all the fields, and the other 3 columns say if any of the tick boxes hasn't got a tick, show the record.

There's one additional thing that may prove useful in that you can sort the records into some sort of logical order. You nay want to do it by date or order number or purchaser or something else or any combination of those fields ... but that's the next lesson.

Phil

Share this Question
Share on Google+
13 Replies


PhilOfWalton
Expert 100+
P: 1,430
The RecordSource for the form must have something in the criteria like

Expand|Select|Wrap|Line Numbers
  1. WHERE Confirmation = False OR EnteredEBMS = False or OrderRecieved = False
  2.  
If all 3 are true, then the test fails and the record is not displayed.

Phil
Mar 1 '17 #2

P: 7
Thank you for the quick response. This is a little over my head though. What would I click on to enter in a code like that? Would I open the table and select something under the database tools tab?
Mar 1 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
OK What is the RecordRource for your form? It is usually a query which you can view by pressing the 3 vertical dots with the form in design view on the Data tab.

Phil
Mar 1 '17 #4

P: 7
okay, so i'm working off of the property sheet. i press the 3dot next to recordsource and invoke the query builder on a table, and i select yes to create a query based on the table. now i'm looking at a blank query. what do I do next? Thank you for your help.
Mar 1 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
Oh Dear!!
Now I look at the PDF file, I see all sorts of problems.

I think you need to read up about database normalisation.
A VERY rough guide is that each table should have data relating to a unique subject.

So I see you have Purchasers, Vendors, possibly Products & possibly jobs. There will also be an Orders Table that links the Purchaser, Vendor, Job & Product together.

The Purchaser's information should all be in one table something like
Expand|Select|Wrap|Line Numbers
  1. PurchaserID        AutoNumber        Primary Key
  2. PurchaserName      Text
  3. PurchaserAddress   Text
  4. PurchaserPhone     Text
  5. PurchaserEMail     Text
  6.  
Something similar for the Vendors table.

The advantage is that on your Order form, there will be a dropdown to select the Purchaser, another dropdown to select the Vendor.

That way the purchaser and vendor details are held only once in the database, and once you have those names correct, they will be correct forever. Although it doesn't matter, in your example, names are not consistent (only in that the capitalisation differs)

Once we get that sorted, we can progress.

Phil
Mar 1 '17 #6

P: 7
Believe it or not the only information that really maters on this sheet is the po# and the check boxes. The rest is arbitrary. I designed this so that it could be used by a bunch of amish guys from any terminal in the shop. I just need to be able to match up the confirmations and shipping receipts that get put on my desk with a po#. As you can see the list is getting kind of full and I'd like to shrink it down after all those checks are filled in.
(Please reference new pdf) I have it set up so that the purchaser hits the "Generate PO#" button and a form pops up where they select the purchaser form a dropdown menu, vendor, product, and job name are all on autofill so all they have to do is start typing and click on the one they want if it's a repeat. So are you telling me from the way I have this set up there is no way to hide a row after all 3 checks are ticked?
Attached Files
File Type: pdf po generator2.pdf (255.3 KB, 63 views)
Mar 1 '17 #7

PhilOfWalton
Expert 100+
P: 1,430
No. Not showing the row is no problem. I get back to what does it does it say is the RecordSource of your form?

Phil
Mar 1 '17 #8

P: 7
So once i hit the build button (3 dots) next to recsource it starts a query. where in the query do I enter this code: WHERE Confirmation = False OR EnteredEBMS = False or OrderRecieved = False
Mar 1 '17 #9

PhilOfWalton
Expert 100+
P: 1,430
I am assuming before you hit the 3 dots, there was a table defined, I think PO# Table.
It is essential that you change the name of this table, as "#" is a reserved symbol in Access and will cause problems. May I suggest you change it to "TblJobs" (without the inverted commas)

OK, Right click your mouse in the large white area above the grid and it will give a list of options, one of which is "Show Table". Click on this and select the newly named TblJobs.

Yoy will now see this table in the white area. At the top of the table, there is an asterisk. Right click in this and when it is highlighted, drag it down to the left hand top cell in the grid.
This is a shorthand way of saying "show me all fields in the table".

When you have done this there is an exclamation mark at the top of the screen that says ! Run. Press this and you should see all your data.
At the top of the screen you should now see triangle and ruler which brings the query back to design view.

At this stage, can you provide one of your excellent PDFs showing the Query in Design view.

Close and save the the Query.

Phil
Mar 1 '17 #10

P: 7
ok, I'm with you so far.
Attached Files
File Type: pdf po generator3.pdf (95.0 KB, 64 views)
Mar 2 '17 #11

PhilOfWalton
Expert 100+
P: 1,430
Sorry, can you make the TblJobs longer so that the scroll bar vanishes and I can see all the fields

Phil
Mar 2 '17 #12

P: 7
Here is the updated pdf.
Attached Files
File Type: pdf po generator4.pdf (97.4 KB, 69 views)
Mar 2 '17 #13

PhilOfWalton
Expert 100+
P: 1,430
Excellent.

So although this is not essential, in the original TABLE design, I strongly urge you to change the name of PO# (AS I said, "#" is a reserved word. Change it to say PoNo
Equally spaces in field names are a pain as they have to be surrounded with square brackets [] when reffering to them. So I suggest that Acquired Date becomes AcquiredDate, Job Name becomes JobName, ans similarly with the other fields with spaces in them.

When you have done that, in the same way you dragged the "*" into column 1, drag ConfirmationReceieved into Column 2, drag EnteredEBMS into Column 3 and OrderRecieved into Column 4.

In column 2 on the line marked "Criteria" (Line 5) type the word "false" without the quotes.

In column 3 on the line marked "or" (Line 6) type the word "false" without the quotes.

In column 4 on the line 7 type the word "false" without the quotes.

In the line marked "Show" (Line 4) untick the box in columns 2,3 & 4.

So what we should have is the first column says show all the fields, and the other 3 columns say if any of the tick boxes hasn't got a tick, show the record.

There's one additional thing that may prove useful in that you can sort the records into some sort of logical order. You nay want to do it by date or order number or purchaser or something else or any combination of those fields ... but that's the next lesson.

Phil
Mar 2 '17 #14

Post your reply

Sign in to post your reply or Sign up for a free account.