Page 2 of 16 FirstFirst 123456789101112 ... LastLast
Results 16 to 30 of 232

Complex Coding --> Please Help

  1. #16
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    What I would suggest is to have a table that holds all possible answers. I have this already I think this is tblChecklistAnswer



    Then have a junction table that joins each checklist item with its applicable possible answers I think this is impossible as you can never know all applicable answers as the inspectors can write something unique if necessary

    For example, checklist item 1 might have the possible answers yes/no/NA, checklist item 2 might have the same possible answers. Checklist item 3 might have possible answers of yes/no/ don't know For every single question there is 2 parts to the answer, (for every question for every check-list of every equipment type)
    1) a comment (this can be anything and specific answer is stored in tblChecklistAnswer, this can be a comment, usually a sentence however can also be a date, an integer, serial number, etc) and
    2) a yes, no or N/A (Is the piece of equipment Compliant with this checklist question? Yes, No or N/A)

    I noticed that you added a new table called common answers, perhaps you can use that to hold all possible answers

    I was just playing around with this to easily pre-fill a combo box for common answers however not sure if I can make a comprehensive list at this stage

  2. #17
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Since you still have some issues with your table structure, I would probably hold off on forms until all of the structure issues are resolved.

    I will have a look through and try some design tips, especially getting my subform sorted and other attempts and await your advice if my above comments change your thoughts somewhat

    Thank you so much for your input this is a massive project and I can't do this alone

  3. #18
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks again for letting me know how to properly sort out subforms I spent untold hours trying to code this and now it is resolved in 10 minutes simply by adding the EquipInspectID to my Results form and link child and master. I have been pulling my hair out trying to work this out

    Any other tips would be great

  4. #19
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    A checklist item may have infinite answers as the inspector may enter anything in this field (hence unique comment)

    Occasionally this will occur, yes, one answer may be used for more than one Check-list question
    Based on your first response above, you need a field in the results table to handle the variable response (I think you called it your comment field) AND a field to hold the selectable answer (yes/no/na etc.). Based on your second response, the selectable answers must be tied to the checklist via a junction table. You would use the junction table to filter the applicable responses for each checklist question in your form.

    What I would suggest is to have a table that holds all possible answers. I have this already I think this is tblChecklistAnswer
    Your tblCommonAnswer looked closer to to what I was thinking; I used tblChecklistAnswer as the junction table to link the checklist item to the applicable answers. Feel free to adjust it as you want.

  5. #20
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    OK I think I understand where you are going with this, have a look at my tables when you get chance and see if my relationships match what you were thinking

    Cheers

    Attachment 4730

  6. #21
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Based on your second response, the selectable answers must be tied to the checklist via a junction table. You would use the junction table to filter the applicable responses for each checklist question in your form.

    I think I am missing this table not sure if I understand this one

  7. #22
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I think I am missing this table not sure if I understand this one
    You have it: tblChecklistAnswers

    You just have to populate the data.

    Your tables look OK relative to the checklist, answers and results. The only other table issue I see is in tblContact. You have several fields that relate to contacting the person: phone, fax, e-mail etc. This is technically a one-to-many relationship, so the contact info should be in a separate but related table

    tblContactInfo
    -pkContactInfoID primary key, autonumber
    -fkContactID foreign key to tblContact
    -fkContactMethodID foreign key to tblContactMethod
    -txtContact (the actual phone #, e-mail address etc.)

    tblContactMethod (holds a record for each contact type: phone, main, fax, e-mail, mobile etc.)
    -pkContactMethodID primary key, autonumber
    -txtContactMethod

  8. #23
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Ok so my design is all sorted then, however I am still at a loss how to add questions to frmChecklist for each equipment type and how to add the answers to the tables in addition to how to default the answer value and validate the answers

    Major Disaster today so not much time to do much work on this but still welcome your advice

    Thanks

  9. #24
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    As I said before, you will need some code in the after insert event of the frmEquipment (based on the inspection-equipment junction table) that runs an append query to add the applicable questions to the results table. Also,in the form based on the results table you will need to filter the combo box that holds the answers based on the questions and the relationship in tblChecklistAnswers. I have done the coding in the attached DB. Just open frmInspection2 and add a new inspection record and then a new piece of equipment to be inspected. The questions you had were only applicable to a piece of equipment that was type=1 so I removed all but 1 piece of equipment. You will have to add other pieces of equipment and applicable questions for each type of equipment. Make sure to put the attached database in a trusted location otherwise the code will not run.

    BTW, I also saw that each piece of equipment was associated with a client. Can a piece of equipment be moved from one client to another at some point in time? If so, your tables are not properly structured to handle that possibility.


    Also, if each inspection is applicable to only one client (which looks to be the case) then you can filter the equipment combo box in the subform based on the client selected in the main form. I will leave that to you

  10. #25
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Excellent, apologies for the delay

    I didn't even think about having the question in a combo box. This design seems to work well for adding to results table. I am so busy at the moment I have been working 14 hour days and have not gotten a chance to work on this properly

    Make sure to put the attached database in a trusted location otherwise the code will not run. Not entirely sure what you mean by this

    BTW, I also saw that each piece of equipment was associated with a client. Can a piece of equipment be moved from one client to another at some point in time? If so, your tables are not properly structured to handle that possibility. It would be extremely rare and would probably then have a different equipment number, location amongst other things so the frequency of this occurring means that it is probably easier to just treat this as another piece of equipment


    Also, if each inspection is applicable to only one client (which looks to be the case) then you can filter the equipment combo box in the subform based on the client selected in the main form. I will leave that to you Yes this is indeed the case I will see what I can do

    Just thought I'd quickly keep you in the loop and thank you for everything. I am trying my best to get some free time to go though your advice and current code to try and implement this to mine

    A few slight things come to mind_

    Ideally I would like to have multiple questions on each page of form as per my example (there will be absolutely no datasheet view in my database and form view at the moment is just one per question per page)

    Also I would like the combo locked as the inspector should not be able to change the question.

    However overall this is great and this looks like it puts the data in all the appropriate tables now I think the design is in tact now

    Also I assume the best way to do the questions would be a combo with not in list property code?


    So after I sort all this out I can try and work on my validation of answers and also making the answers default to the answers of a previous inspection


    Still a bit further to go for the data entry side of things before I can work on how I can get the data out

    Thanks again for everything, helping me out no-end
    Last edited by JFo; 10-13-2011 at 02:30 AM.

  11. #26
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Make sure to put the attached database in a trusted location otherwise the code will not run. Not entirely sure what you mean by this
    Trusted locations are part of the new security features in Access 2007/2010. For any code associated with the database to run, you have to put the database file in a trusted location. You can set up trusted locations (folders on your hard drive) via options (File-->Options-->Trust Center)

    Also I would like the combo locked as the inspector should not be able to change the question.
    This is easy to do, just change the locked property of the combo box to yes (via property sheet-->data tab)

    Also I assume the best way to do the questions would be a combo with not in list property code?
    I'm not sure what you mean by this. Would you be trying to add new questions at the time of inspection? I would think that all of your questions would be predefined. You would use a separate form to add new questions relative to an equipment type.

    It would be extremely rare and would probably then have a different equipment number, location amongst other things so the frequency of this occurring means that it is probably easier to just treat this as another piece of equipment
    I would worry about traceability of inspections if you assign a new equipment number. You would loose the correlation of new inspections to old inspections for the same piece of equipment. It will also make you programming to copy the questions from an old inspection to this new inspection since the equipment numbers will not be related. You will have to decide on this one.

    Ideally I would like to have multiple questions on each page of form as per my example (there will be absolutely no datasheet view in my database and form view at the moment is just one per question per page)
    Doing a form view is not an issue. Doing the tabbed form will be a little trickier. You would have to somehow categorize the questions so that you can split them into their appropriate tabs. You can do this with one category field and then reference that in a series of queries and then base each tab on a query.

  12. #27
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Quote Originally Posted by jzwp11 View Post
    This is easy to do, just change the locked property of the combo box to yes (via property sheet-->data tab)
    OK this works great is there any way to make the first value populate the combo before the user clicks on from or in combo, etc?


    I'm not sure what you mean by this. Would you be trying to add new questions at the time of inspection? I would think that all of your questions would be predefined. You would use a separate form to add new questions relative to an equipment type.

    I have got to stop trying to post when I am tired or busy, I think this is the second time I have done this to you. I actually mean Answers. The users will not be able to add any questions. The answers should come from the common answers table however if not on the list for that question then the user should be able to enter anything they want (ie comment) I take it for this I need to use some sort of not in list code



    I would worry about traceability of inspections if you assign a new equipment number. You would loose the correlation of new inspections to old inspections for the same piece of equipment. It will also make you programming to copy the questions from an old inspection to this new inspection since the equipment numbers will not be related. You will have to decide on this one. I understand what you are saying however I really doubt this will ever be an issue and will almost never happen. If it does I can make a clientEquipmentJunction Table however I don't think this will happen



    Doing a form view is not an issue. Doing the tabbed form will be a little trickier. You would have to somehow categorize the questions so that you can split them into their appropriate tabs. You can do this with one category field and then reference that in a series of queries and then base each tab on a query.
    OK I will have a go at converting it to form view first and then maybe tabbed view later, however this may not be required


    I again have a busy morning however I am hoping to get a bit of time later in the day today to do a bit on my db otherwise this not looking like a week for my project. Without your help I would not have gotten anywhere and have somehow managed to get to a more functional level with very little time so again I thank you and I will be in touch asap

  13. #28
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    OK this works great is there any way to make the first value populate the combo before the user clicks on from or in combo, etc?
    I tried several things to make this happen, but was unsuccessful. Perhaps you can pose that question in a new thread; someone else might be able to come up with a solution.

    The answers should come from the common answers table however if not on the list for that question then the user should be able to enter anything they want (ie comment) I take it for this I need to use some sort of not in list code
    You have two free-form text fields comments and answer2 that can be used, but if you want a specific answer to be included as a common answer choice for that particular question from then on, you would use the on not in list event. You would have to run an append query to first add it to tblCommonAnswers and then another append query to add a record to tblChecklistAnswers. The other caveat to that is if you want that answer to apply to other questions, you would have to associate that answer with other questions via another form (and at a different time) since you would have to pick the questions out on a case-by-case basis. The other thing is that if you are not the one adding the answer, you will have to know when one of your users do add one.

  14. #29
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    I tried several things to make this happen, but was unsuccessful. Perhaps you can pose that question in a new thread; someone else might be able to come up with a solution. Will do

    You have two free-form text fields comments and answer2 that can be used, but if you want a specific answer to be included as a common answer choice for that particular question from then on, you would use the on not in list event. You would have to run an append query to first add it to tblCommonAnswers and then another append query to add a record to tblChecklistAnswers. The other caveat to that is if you want that answer to apply to other questions, you would have to associate that answer with other questions via another form (and at a different time) since you would have to pick the questions out on a case-by-case basis. The other thing is that if you are not the one adding the answer, you will have to know when one of your users do add one.

    OK at the moment than I am thinking something like common answers will filter the common answers for that question and will be in common answers table

    The not in list code (on the combo) will be set-up to store the answer that the user enters if not available in the common answers list however this is considered a once off value and not going to be used in available answers for any future questions (besides the next inspection default for that piece of equipment). This value still gets inserted into tblCommonAnswers table and is linked in tblChecklistAnswer for that question.

    Is this right and does this make sense?

    Thanks

  15. #30
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    JFo

    OK this works great is there any way to make the first value populate the combo before the user clicks on from or in combo, etc?

    Can you post the latest Version in 2003?

    Please supply directions as to where this Combo Box you want repopulated Resides

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

Similar Threads

  1. Coding question
    By kzoli62 in forum Access
    Replies: 1
    Last Post: 07-04-2011, 05:09 PM
  2. Combo Box Coding
    By Hale in forum Forms
    Replies: 2
    Last Post: 05-04-2010, 10:56 AM
  3. Coding for Empty Combo Box
    By gazzieh in forum Programming
    Replies: 1
    Last Post: 03-26-2010, 05:22 PM
  4. Button Coding
    By yamie in forum Programming
    Replies: 3
    Last Post: 02-22-2010, 10:45 AM
  5. Bar-Coding
    By texasprincess7 in forum Access
    Replies: 1
    Last Post: 02-12-2009, 10:29 AM

Posting Permissions

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