Page 1 of 16 1234567891011 ... LastLast
Results 1 to 15 of 232
  1. #1
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393

    Complex Coding --> Please Help

    This is tricky however I would love some help to do this, My design is quite complex however I will try and explain it (I also have a number of other posts pertaining to the design with example databases)

    I have the following tables

    - tblInspections
    - tblEquipment
    - tblEquipmentInspectionJunction

    (Each piece of equipment can be involved in many inspections and each inspection can have many pieces of equipment)

    Furthermore each piece of equipment will be inspected with a totally different check-list where the table structure is

    - tblChecklistQuestions (stores the questions for all check-lists)
    - tblChecklistAnswers (stores the answers for all check-lists)


    - tblResults (stores the results with the checklistID and ChecklistAnswerID foreign keys)

    I have almost got my first junction sorted however the second junction is where I have 2 major complex coding questions

    1) For this to work the form must have the same field for the answer however this will store different records for the same piece of equipment

    I can use something like this

    For i = 1 To 45 (whatever number of questions in the checklist)
    db.Execute " INSERT INTO [tblChecklistAnswer]" & "([Answer], [Response]) VALUES " _
    & "('" & Me.Controls("txtAnswer" & i).Value & "'," & "'" & Me.Controls("cmbResponse" & i).Value & "'" & ");"
    Next i

    And this works great however this simply enters whatever is input into ChecklistAnswer Table

    What I need is similar code that extracts the checklistAnswerID and the ChecklistID from the appropriate tables and then goes through the loop and enters this data in the results table

    2) This may be related somewhat as my difficulty is in extracting the data from the tables however for each specific record (same field) I would like to set the default value as that of the last inspection (if the equipment has been inspected before)

    Therefore I am trying with DLookup where the code is something like

    if inspected before

    DLookup from tblChecklistAnswer (answer) where checklistID and checklistAnswerId are matched in tblResults for that specific piece of equipment (complicated in my head even to write this)

    This is the hardest part of my database, however it is also extremely essential for the whole database to operate and I will be greatly appreciative of any assistance

    Thank you in advance

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Without a little more detail on the key fields in your tables it is a little difficult to understand what you are trying to do. I understand your first series of tables (equipment/inspections and the junction table)

    tblInspections
    -pkInspectID primary key autonumber
    -dteInspect (inspection date)


    tblEquipment
    -pkEquipID primary key, autonumber
    -txtEquipNo
    -txtSerialNo

    tblEquipmentInspection (Junction table)
    -pkEquipInspectID primary key, autonumber
    -fkInspectID foreign key to tblInspections
    -fkEquipID foreign key to tblEquipment

    Since for each inspection/equipment combination, you will have many associated questions and answers to those questions (i.e. results), the results table must be joined to the tblEquipmentInspection table (I could not tell what your results table looked like)

    tblEquipmentInspectionResults
    -pkEquipInspectResultsID primary key, autonumber
    -fkEquipInspectiID foreign key to tblEquipmentInspection
    -fkQuestID foreign key to tblQuestions
    -fkAnswerID foreign key to tblAnswers


    tblQuestions
    -pkQuestID primary key, autonumber
    -txtQuestion

    tblAnswers
    -pkAnswerID primary key, autonumber
    -txtAnswer

    Some general questions....

    For each piece of equipment are the same questions asked at each inspection or do the questions change?

    Are certain answer choices related to certain questions?

    For example, some questions may require either a yes/no response while others might require true/false?

    Are all of the answers predefined or do you have some questions that require freeform responses?

  3. #3
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Here is a copy of my current database
    Attachment 4704

    and here is a thread I posted to work out design (there is a lot more info in all my threads if you are interested)

    https://www.accessforums.net/access/...ase-16943.html

    For each piece of equipment are the same questions asked at each inspection or do the questions change? Each inspection has many pieces of equipment and they all have different questions dependant on equipment type

    Are certain answer choices related to certain questions?
    They will be

    For example, some questions may require either a yes/no response while others might require true/false?
    Every question consists of 2 parts

    1) a comment (this will be unique in the majority of cases and needs to be able to include anything at all that the inspector wishes to write) and

    2) yes, no or N/A 3 option combo box is the second part to every question

    Are all of the answers predefined or do you have some questions that require freeform responses?
    Freeform responses are required some may be pre-defined however the inspector can still overrule the pre-defined value and enter whatever they want

    Thanks for having a look at my situation and offering your advice I apologise for any delay in my responses as I have a very busy weekend

    Much appreciated

  4. #4
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Attachment 4706

    Just realised you use 2010 here is the 2010 version, some things may not work in 2003

    Thanks

    Also I apologies if some things do not work at the moment and that some forms contain elements that should not be there (I am in the building stage and am trying many things)

    An example of a check-list for a certain piece of equipment in an inspection is frmEquipPressureVessel (there will be about 30 different check-lists at this stage, ranging in number of question from about 15 --> 150)

    let me know if you need any more info


  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Going back to your original post, I'm a little unclear as to what you want to do. Are you wanting to populate the results table with the applicable questions for the particular piece of equipment being inspected depending on it's type (and thus load the questions associated with that type)? If so you can just run an append query at the time a new inspection/equipment record is create in the junction table. You can do this via some code in the form tied to the junction table. At this point the answers are unimportant because the inspection has not yet occurred.

  6. #6
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks again for taking the time to look through my database, the first post (and my code) may be deceiving in places as I was figuring out the way to do it and I have actually managed to write code that populates the answer table with certain answers (this may be helpful later for Adding to list).

    It would be great if I could work out to code the forms extracting questions from the table however I thought it may just be easier to have a different form for each check-list. Ideally I would only want to create one form that is built each time a piece of equipment is inspected and therefore creates the questions and answers on the fly. Ideally I would like to do this the best way so am open for suggestions.

    At this stage I figure I am trying to load the results table with all the different fields for each specific question say for example a piece of equipment is being inspected then it should open the relevant form for that equipment. As you go through the check-list it should store all the values in the results table for each question/ answer/ etc.

    Therefore for every single different piece of equipment (check-list) it should store EquipInspectID (this differentiates between each piece of equipment and each inspection and will be the same value for every question/answer in a given check-list but will be different over different inspections), ChecklistID (this will be different for each question and will differentiate between each question that is being asked), ChecklistAnswerID (this will be different for each answer and will differentiate between each answer for each question)

    Therefore the loop or append coding that I need will enter all of these fields with a different resultsID for each instance which will deliver the unique link between the Inspection/Equipment, question and answer

    Furthermore Part 2 of my question will need to somehow extract the equipment id to see if the equipment has been inspected before and if yes than it should default the answer for each question of the new check-list with the answer from last inspection using the same 4 fields as above however it will also need to get EquipmentID and also retrieve the Answers each time from ChecklistAnswer. This is quite a complicated DLookup statement I think


    Let me know if you need any more info

    Thanks

  7. #7
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Having a form for each piece of equipment is not the correct approach. You should have a main form based on the inspection table and then within that form have a subform based on the inspection-equipment junction table and then within that subform have another subform (subsubform) based on the results table. When a new record is added to the junction table (via the first subform), you would populate the result table (and thus the subsubform) with the related questions based on the equipment type using an append query. That append query would look something like this:

    INSERT INTO tblResults ( ChecklistID, EquipInspectID )
    SELECT tblChecklist.ChecklistID, [equipinspectID]
    FROM tblChecklist
    WHERE (((tblChecklist.EquipTypeID)=[typeID]));

    The variables highlighted in red would have to be populated dynamically using controls on the subform, so you will have to construct the query in code , most likely in the after insert event of the subform. Once the query is constructed, you would just execute it to populate the applicable questions into the results table. You would not need to worry about the resultID field if you have it set as an autonumber primary key (Access will take care of it when you do the append)

    As to the answers, I would probably not populate those from a previous inspection because the answers might be different for this new inspection. Pre-populating the answers would not be good from a data integrity point of view.

  8. #8
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Yes I have a form for Inspection and a subform for each piece of equipment my difficulty is creating the form for any given check-list and looping it through each check-list question. Especially since there will be many controls on the form that are actually the same field but a different record.

    The second part is essential as usually the answers will be the same as last inspection and this will dramatically decrease the data input time. I actually have to go out for the day in a minute however will definitely be working my way through this tomorrow.

    Thanks for your feedback and so quickly on a weekend

  9. #9
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Yes I have a form for Inspection and a subform for each piece of equipment my difficulty is creating the form for any given check-list and looping it through each check-list question. Especially since there will be many controls on the form that are actually the same field but a different record.
    The subform should be based on the junction table and you would use a combo box for the equipment. The subform would contain a record for each piece of equipment specific to that inspection.

    I've create the form/subform/subsubform in the attached. See frmInspection2. I did not put in any of the code mentioned earlier; I'll let you give that a try.

    As to the answers, I still do not agree in pre-populating them for a new inspection. But if that is the way you want to do it, it might be better to set up the first inspection for each piece of equipment complete the answers and then use that as a template for all future inspections. You would use an append query that pulls the questions and answers from that first inspection and then append them to subsequent inspections. The append query would be similar in design as the one I presented earlier you would need add the answer field and revise the SELECT & FROM clause to get the data from the result table instead.

  10. #10
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks for your efforts, I have to pop out now, however will definitely give this a try tomorrow or as soon as I get the chance

    Much appreciated

  11. #11
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Attachment 4713

    OK I have spent a few hours on this now and I think I understand parts of your choice of design however I have not managed to get it to work properly.

    As I already had the first form and sub-form set-up I have added the second sub-form to my frmInspections (frmChecklist). I have then created qryAppendResults which successfully updates the ChecklistID to tblResults (EquipInspectID not working properly at the moment)

    However I am still having difficulty working out how to get the ChecklistAnswerID into the table

    As to your comment

    As to the answers, I still do not agree in pre-populating them for a new inspection.

    I actually mean populating the form not the table

    So I guess I need to work out how to display different questions on the form for different pieces of equipment as well as finishing the tblResults filling

    Please let me know if I am on right track

    Thanks

  12. #12
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    OK may throw it in for the day shortly, major eye and brain strain this is what I have currently come up with

    Attachment 4715

    As you can see some issues still stand, notably

    - working with subs, I have managed to pass the correct inspectEquipID to combo in subform2 (frmChecklist) but can't get it into table
    - Getting Answers into ChecklistAnswer has not come along very quickly
    - Adding another piece of equipment and therefore another check-list opening in new mode, have tried many things for this, basically it is just going through forms/ subforms, but onclick, onfocus, onload, etc nothing working for me or working in wrong way
    - DLookup or any default values and validation
    - populating form with questions dependant on equiptype


    Plus many many more things

    I have actually got quite a bit working however I thought I would have finished the design weeks ago and be on to the data extraction and all of the validation, default values etc I wanted to do. It is extremely frustrating at times


    Thanks to everyone that can help me on my way

  13. #13
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    - working with subs, I have managed to pass the correct inspectEquipID to combo in subform2 (frmChecklist) but can't get it into table
    The frmChecklist is not linked to the frmEquipment, you have to set the master/child linking fields. Note: this is done automatically by Access if you have your relationships set up in the relationship window. To do this manually, left click on the frame of the frmChecklist and then go to the Property Sheet-->Data tab. Fill in the Link Master Field & Link Child Field (Access will make suggestions to help). I'll let you give this a try.

    As to the answers, does a checklist item have multiple possible answers? (I assume yes) Can the possible answers be used for multiple checklist items? (I assume yes). If so then you have a many-to-many relationship which requires a junction table.

    What I would suggest is to have a table that holds all possible answers. Then have a junction table that joins each checklist item with its applicable possible answers

    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

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

    tblCommonAnswers
    -pkAnswerID primary key, autonumber
    -txtAnswer

    The records might look like this

    tblCommonAnswers
    AnswerID|txtAnswer
    1|Sighted
    2|Not Sighted
    3|Not Applicable
    4|Satisfactory Condition
    5|Refer Manuafacter's Specifications
    6|Yes
    7|No
    8|N/A
    9|Don't Know


    You would then relate the checklist items to the possible answers (the junction table I mentioned). You can modify your Checklist Answer table to handle this

    tblCheckListAnswer
    -CheckListAnsID primary key, autonumber
    -fkChecklistID foreign key to tblChecklist
    -fkAnswerID foreign key to tblCommonAnswers

    As an example, let's say that for the checklist item with a key value of 51 and assuming that the possible answers are yes/no/NA, the tblCheckListAnswer would look like this

    tblChecklistAnswer
    ChecklistAnswerID
    |ChecklistID|fkAnswerID
    2|51|6
    3|51|7
    4|51|8

    The DB with the above table modifications is attached.

    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.

  14. #14
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    The frmChecklist is not linked to the frmEquipment, you have to set the master/child linking fields. Note: this is done automatically by Access if you have your relationships set up in the relationship window. To do this manually, left click on the frame of the frmChecklist and then go to the Property Sheet-->Data tab. Fill in the Link Master Field & Link Child Field (Access will make suggestions to help).

    This is invaluable information, thanks so much, no wonder I have been having so much trouble. Not sure why but I thought if I dragged the form into another form than it would automatically set-up this relationship

    I will try and sort this now

    Thanks

  15. #15
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    As to the answers, does a checklist item have multiple possible answers? (I assume yes)
    A checklist item may have infinite answers as the inspector may enter anything in this field (hence unique comment)

    Can the possible answers be used for multiple checklist items? (I assume yes). If so then you have a many-to-many relationship which requires a junction table. Occasionally this will occur, yes, one answer may be used for more than one Check-list question

Page 1 of 16 1234567891011 ... LastLast
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
  •  
Other Forums: Microsoft Office Forums