Results 1 to 7 of 7
  1. #1
    averagejo3 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    6

    How can I add a condition to a checkbox??

    Hello everyone, so basically I am making a student database that contains student grades, I have a query that gives me a list of what classes a specific student has taken that are part of their major. This is what the query returns:
    Click image for larger version. 

Name:	Screen Shot 2018-04-26 at 10.23.04 AM.png 
Views:	20 
Size:	47.1 KB 
ID:	33746
    So what I want to do is create a report that has a section like this where it lists all possible classes they can taken per that major:
    Click image for larger version. 

Name:	Screen Shot 2018-04-26 at 10.17.28 AM.png 
Views:	19 
Size:	17.7 KB 
ID:	33747
    I want to have a checkbox next to each class and have the box be checked off if they have taken the class, if they have not I want the box to be empty, so I don't necessarily need anything on the report like grades etc. I just want it checked off if they have taken that specific class . I want to know what I can put in the checkbox's control source where it would check the query results and check for a specific string like "Amat_108" and the set the checkbox to true if there is.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You need a dataset of all possible combinations of student/class. This can be created by Cartesian relation query of students and classes tables. Join that dataset to table of taken classes with compound join on student and class fields join type 'all records from dataset and only those from data that match'.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I don't think you need a Cartesian join as June suggests...but perhaps I'm reading the question in a different way

    You definitely need a Boolean (yes/no)field which your checkbox will be bound to
    Call it Done or Completed or similar
    An update query can be used to set the value equal to true when an agreed condition has been met.
    You just need to decide what the condition is. For example an assessment grade exists?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    averagejo3 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    6
    Quote Originally Posted by ridders52 View Post
    I don't think you need a Cartesian join as June suggests...but perhaps I'm reading the question in a different way

    You definitely need a Boolean (yes/no)field which your checkbox will be bound to
    Call it Done or Completed or similar
    An update query can be used to set the value equal to true when an agreed condition has been met.
    You just need to decide what the condition is. For example an assessment grade exists?
    Yea I don't think so either, I just need it to check for a specific class code, after I run my query I get a short list of classes the student has taken. I want the checkbox to check against that small list and if a class code such as "Amat_108" and if there is I want the checkbox to be set to "True" or "Yes"

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Fine. Use an update query
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Update what table? The lookup table of all courses? What table has required courses associated with each student? You only mention a table of taken courses associated with each student.

    Use DLookup()

    ControlSource expression for checkbox control named Amat108:

    =IIf(IsNull(DLookup("Course", "StudentCourses", "StudentID=" & [StudentID] & " AND Course='Amat_108'")), False, True)

    However, I suspect there are way too many courses for a specific checkbox for each course. And the set of required courses would change depending on the student. And domain aggregate functions can significantly reduce performance. I refer you back to my original suggestion (although Cartesian query can also be slow performer with large datasets).

    If you want report for single student, bind to the Courses table (or table that associates courses to major and apply filter to report for major) and make expression in a single checkbox conditional on course field as well as student. Major and StudentID parameters can both reference form controls for input.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @averagejo3
    June7 & I are clearly interpreting your posts differently.
    Both of us can't be correct but at the moment its not clear which of us has the correct interpretation - maybe neither of us!

    So I suggest you upload a stripped down version of your database changing any personal details & just including the parts relevant to this question.
    Then you should get a definitive answer
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Where condition
    By exupery in forum Macros
    Replies: 2
    Last Post: 06-05-2017, 05:54 PM
  2. Where condition
    By DavidTCH in forum Access
    Replies: 3
    Last Post: 03-25-2016, 09:07 PM
  3. Replies: 1
    Last Post: 11-26-2014, 02:30 PM
  4. Where Condition
    By NISMOJim in forum Programming
    Replies: 4
    Last Post: 04-09-2014, 11:10 PM
  5. Help with using the AND condition
    By ssturges in forum Access
    Replies: 1
    Last Post: 11-25-2012, 12:36 AM

Tags for this Thread

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