Results 1 to 12 of 12
  1. #1
    Dantor is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2019
    Location
    New York, NY
    Posts
    6

    How to create a checklist in MS Access that changes conditional formating

    I am building a database for maintenance performed on lighting fixtures, each with their own unique serial number. I already have an excel document with all this data, but want to create a better system for managing it.

    The idea is that every time I service a unit, I have a new job #, and fill out a checklist and enter notes for that job. I have created a Job # table, with columns for serial #, date received, date completed, notes etc. I have also created a job form, which includes all the relevant info for that job#. The question I have now is how to create relatively efficient checklists that modify the background color of a text field.

    For the first part of this, I need to have a drop down list with different model lights to choose from(each light has different feature sets to test), which, depending on which model I select, will display/generate a checklist, with about 25 items, that is unique to that model.
    Then, when all the check boxes are checked for that specific checklist, I need the background color of a text field("Unit Good?") to change from red to green. These check lists need to be specific to the job #/record #, and I'm curious if there's a relatively easy/efficient way to do this.

    At present I have about 150 jobs, and it will only increase over time. My preference is to avoid VBA if possible, but if necessary, some pointers, or recommended tutorials would be highly useful.
    I've built the underlying table and form, but haven't yet been able to figure out how to do the checklists.


    Any help here would be extremely appreciated. I am happy to provide any additional information/documents if necessary.

  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,825
    Each checklist item is a record? Perhaps DCount() will work.

    To set as green, rule expression like:

    DCount("*", "JobCheck", "JobID=" & [JobID] & " AND Check=False")=0

    If each check item is a field, this gets much more complicated.

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Are you using # character?
    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
    Dantor is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2019
    Location
    New York, NY
    Posts
    6
    No, for each record(job #) there will be a checklist with values that need to be bound to that record. There will be several different unique checklists to choose from, but each record only gets one. Which checklist it gets should be dependent on which value I select in a drop down menu(which model of lighting fixture it is will dictate which features I need to test). When all check boxes are checked in the selected checklist, then a conditional formatting rule should change the background color of a text box to green.

    oh, and yes, I am using the "#" character. I have a "Job # List" form, table, and query, and a "Serial # List" table. Also I have multiple columns in multiple places with "#" in the headers.

    Thanks for taking the time to look into this.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not sure you answered my question. Each check item is a field? As I said, that could get complicated and probably need a VBA custom function.

    If there were only a few fields, say 3, the following might work.

    Calculation in query: Abs(Field1 + Field2 + Field3)

    Then reference that field in Conditional Formatting for green: [calcField] = 3

    25 items could be too long an expression.

    Once the appropriate checklist is selected, record(s) must be saved to a JobCheck table so the responses for each item can be stored.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  5. #5
    Dantor is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2019
    Location
    New York, NY
    Posts
    6
    I'm sorry, I don't exactly understand. I thought that a checkbox was required to reference a field in order to function. For my use, each checkbox would want to be bound in some way to that job#/record.

    In the attached Access db, you can see the very basic, and rough visual layout of what I'm going for here, but the check boxes are all currently linked to the two same fields in my job list column. I've included two different checklists in the two tabs of the job form so that you can see what I'm talking about here. Eventually, the idea is that only one checklist will be available once a model is selected in the unit description field. My appologies for the roughness of the form, its my first attempt at using access to create a db, and its clearly not complete yet.

    Also, for reference, I've included one of the job worksheets from my excel workbook where the database started from. Ideally the Access check lists will want to look and function more or less as they do in the excel worksheet.

    I think the issue I was having with excel, was that 150 worksheets each with one of these checklists was getting to be a bit more than it could handle, and ever increasing lag was becoming a serious concern. The goal of moving over to Access is to build the db so that it would be more effecient, and be generally less buggy.

    To that end, I was having some major issues with this Access db with the text boxes in my job form, where I had imported text fields from excel. While trying to manually clean up some of the formatting, selecting words often selects the word and an undesired adjacent character or two(sometimes even skipping over characters), and the curser becomes hidden behind currupt looking half cut off characters. Where this was a problem, it persisted even after saving and closing the form or db. The only fix seemed to be to delete the section of text and manually retype it. I've had this happen over a dozen times now. Super weird stuff. I'm curious if you find the same issue.

    Thanks again!




    Maintenence database.zip

  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,825
    First of all, you are using a multi-value field. It is not suitable for the data and even if it were, it would be a source of endless headaches. I NEVER use them. I NEVER set lookups in table.

    Why have two Yes/No fields?: Unit_Good?, Unit_Not_Good?. They should be mutually exclusive. One field will serve.

    Need a table like:

    Inspection
    ID JobNum ItemNum IsGood
    1 1223 1 Yes
    2 1223 2 Yes
    3 1223 3 No
    4 1223 4 Yes

    The checklist form is all wrong. 24 checkboxes bound to the same field will all show the same value because they are all pulling from the same record. The only way this form would work is with UNBOUND controls and lots of VBA code to save and retrieve data.

    Have you studied an introductory tutorial book for Access? Recommend you spend a solid week with one.

    Really need to eliminate spaces and special characters in naming convention as already advised.
    Last edited by June7; 04-11-2019 at 11:27 PM.
    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
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.....

    I've been looking at your dB and there are major issues in its design.

    For instance, in the tab named "VL1K Checklist", there are 48 check boxes = 24 for "Good" and 24 for "Not Good".
    In table "Job # List" there are only 2 check box fields: "Good" and "Not Good".
    Back on the tab, each of the 24 "Good" check boxes are bound to the same field, "Good", for a record. And the 24 "Not Good" check boxes are bound to the "Not good" field.
    That means if you change the state of any one of the "Good" check boxes, ALL of the check boxes change.
    The same goes for the "Not Good" check boxes. Change any one of the "Not Good" check boxes and ALL check boxes change.
    Probably not what you want.......


    The other thing is that you cannot set conditional formatting on check boxes - they do not have that property. You can set conditional formatting on a text box, looking at the state of a check box control.



    You said
    different model lights to choose from(each light has different feature sets to test), which, depending on which model I select, will display/generate a checklist, with about 25 items, that is unique to that model.
    So you are going to have to have at least another 2 tables - maybe more.

    I would suggest you pause, do some tutorials, then try designing the tables & relationships on paper/a whiteboard/window/etc before getting back to the physical dB.
    Look at this post




    Good luck with your project........

  8. #8
    Dantor is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2019
    Location
    New York, NY
    Posts
    6
    First off, like I said, I am aware that the check boxes are bound to the same field.

    "you can see the very basic, and rough visual layout of what I'm going for here, but the check boxes are all currently linked to the two same fields in my job list column."

    Those check boxes are really just placeholders I put there to give a sense of the needed visual content/layout. It didn't make sense to me to go much further than that until asking the community about the best/most efficient way to proceed. I suppose I could have been clearer on that point.

    To your comment on my use of multi-value fields, can you be more specific? I thought multi-value fields had to be in the format of a drop down menu, and as of yet, I have not implemented anything like that into my db. Also, how else would I achieve being able to select from a few different models of lights to have the correct checklist be presented?

    To the suggestion of removing special characters and spaces from my naming convention, I have done as you advised, and have found that the text boxes now function flawlessly. If that's what fixed it, then thank you very much for that.

  9. #9
    Dantor is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2019
    Location
    New York, NY
    Posts
    6
    Thanks for your reply. As mentioned in my above reply to June7, I am/was aware of the check boxes all being bound to the same fields. They are placeholders only, intended to show the needed content/layout. I wanted to ask the community how best to proceed before spending oodles of time likely going down the wrong path. I did so with the Excel version of this database, and it is now crazy laggy. I want to make sure that I'm building this db relatively efficiently so that it continues to work well even after doubling or quadrupling the number of jobs/records it contains.

    Also, I found a workaround for the conditional formatting. I create a text box, and then set Enabled to "No", lock it, and added a conditional formatting rule for that text box.

    Code:
    [Job_Number_List]![Unit_Good?]=Yes
    Where the "Unit_Good?" column in my "Job_Number_List" table contains a Yes/No Data type field who's state is currently toggled by the check boxes. Since text boxes are so versatile, I figure that I can use them as a conditionally formatted background for any number of graphical elements in my form.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes, multi-value fields present a dropdown list of items that allow selecting 1 or more items into 1 field. But you need to select and save only 1 item into its own record so can also enter its associated Good/NotGood status in another field. This involves a simple text field with a combobox. Don't build it in table, build combobox on form.
    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.

  11. #11
    Dantor is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2019
    Location
    New York, NY
    Posts
    6
    Sorry, I'm still not getting it. You said: "First of all, you are using a multi-value field. It is not suitable for the data and even if it were, it would be a source of endless headaches."
    Since I have not yet implemented any combo boxes, where exactly is it that I'm using multi-value fields where you think I should not be? Are you referring to my Yes/No columns in my Job_Number_List? If so, how else would I record these values?
    Also, can you please explain what you mean by "This involves a simple text field with a combobox."
    What are you tying to communicate with that sentence? How do you get Good/NotGood status to record into another field with a textbox and a combobox?
    I've been spending almost all of the past two weeks trying to learn Access and build this db, but I need a few more breadcrumbs here to be able to even understand what it is I still need to further research/learn.
    Thanks again.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    [Job #] field in [Serial # List] table is set up as multi-value with a combobox. AllowMultipleValues property is set to Yes. This creates a multi-value field. See the Lookup tab for the field properties.

    The sample table in my previous post shows example data that would be result of user input. In a form/subform arrangement, ID and JobNum would be automatically populated by the form. User would select Item from a combobox list and then the IsGood field could be a checkbox. Checked means Good, unchecked means NotGood.

    Actually, I should have said a "simple number field with a combobox" because want to save the ItemID into Inspections table. Of course this means need a 'lookup' table of all possible inspection items - I think the worksheet showed a list of them.

    Data entity Relationships are not clear. Three tables have Job and Serial number fields. And what relevance do shows The View and GMA have?

    This is really basic Access functionality. If you have been studying a tutorial, have you worked through exercises of building a database from page 1? Or are you hopping around the internet picking up isolated tidbits of knowledge?
    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.

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

Similar Threads

  1. Conditional Formating
    By Derrick T. Davidson in forum Reports
    Replies: 3
    Last Post: 07-18-2014, 09:25 AM
  2. Conditional Formating
    By Derrick T. Davidson in forum Forms
    Replies: 5
    Last Post: 04-13-2013, 07:18 AM
  3. Conditional Formating
    By redbull in forum Reports
    Replies: 2
    Last Post: 03-26-2012, 12:08 PM
  4. Conditional Formating help
    By Mounds in forum Forms
    Replies: 2
    Last Post: 02-10-2012, 11:07 AM
  5. Conditional formating!
    By karanvemuri in forum Access
    Replies: 3
    Last Post: 10-29-2011, 03:34 PM

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