Results 1 to 8 of 8
  1. #1
    bonskyn is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2016
    Posts
    7

    Check if checkbox is true on an entered value.


    Hi. I looked all over the internet for an answer to my question, but didn't find any. I have a table of check numbers, which allows duplicates, it is for requesting checks. When a check has arrived they are marked as "Received". I want Access to tell me, if a check has already been received when I enter that check number again for another request. I tried making another table with the received checks using the Update Query, but it doesn't bring the values, it only brings empty cells. Any help will be greatly appreciated.
    Thanks.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Why wouldn't a DLookup or DCount not work in the BeforeUpdate event of the control?

  3. #3
    bonskyn is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2016
    Posts
    7
    Thanks for your quick response, but according to the description of these two expression, I can't find them to fit my needs. I'm an Access rookie, I don't use it that much, so any 101 explanation on how to do this will be greatly appreciated.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The notion that you would split checks into two different tables suggests that you would be very wise to research database normalization. Here is a page that contains a lot of subject matter on db design that you should start with - http://www.fmsinc.com/free/newtips/primarykey.asp Look at the topics in the nav pane on the left.

    Your initial table can do this with a field that 'flags' the check as received. This can be as simple as a checkbox field, or a word such as "Received". However, if using a text value, I'd consider implementing some sort of process control so that users cannot introduce typos, which would cause a query to not return rows. This could be a combo box in your form with a value list so that you avoid misinformation.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by bonskyn View Post
    Thanks for your quick response, but according to the description of these two expression, I can't find them to fit my needs. I'm an Access rookie, I don't use it that much, so any 101 explanation on how to do this will be greatly appreciated.
    I only use code so I hope this is useful. Micron's suggestions are important too. Those Domain Functions (DLookup, DCount) can search an existing recordset for a value. If you searched for the value that was just entered it could tell you it was a duplicate.

  6. #6
    bonskyn is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2016
    Posts
    7
    Hi. I'm sorry if I wasn't clear enough. I'm not splitting checks into two different tables. I tried making a new table with all the received check numbers so I could check on that table if the check has already been received, but it didn't work out.
    I have one table with all the checks I have requested and when they arrive they are marked as received using a checkbox. The table allows for duplicates, since I can request the same check several times. What I want is that whenever I make a new request, the system checks if that check number has already been marked as received, so I don't request a check that has already been received. I have made a search form which works perfect when looking up a specific check or supplier, but we make a lot of check request, so it will take a long time to search if each check has been received before making a new request.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Indexing on the check number field will give you improved performance.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    While you're in the process of indexing, I'd create a composite index (or composite primary field) where the combination of check id and checkbox value cannot be duplicated, otherwise you could end up with a check received more than once. It should be a simple matter of returning all check ids where check id = something and received = false.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. On Click event filter By checkbox = True
    By nick404 in forum Programming
    Replies: 14
    Last Post: 06-05-2015, 03:08 PM
  2. Replies: 4
    Last Post: 02-03-2014, 12:16 PM
  3. Replies: 7
    Last Post: 05-30-2013, 04:22 PM
  4. Replies: 3
    Last Post: 03-08-2013, 11:34 AM
  5. if checkbox= true subtract 8.75 from A to=B
    By VanillaAwesome in forum Queries
    Replies: 6
    Last Post: 07-28-2012, 12:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums