Page 1 of 3 123 LastLast
Results 1 to 15 of 42

Default values from matched ids in tables

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

    Default values from matched ids in tables

    Here is a copy of my current database
    Attachment 4779



    If you go through frmMainMenu-->Select relevant combos and click on Add new Inspection-->fill in relevant details-->Add Equipment-->select equipment from combo box-->click in checklist form

    The check-list form is populated with the questions for that specific piece of equipment type

    What I want to be able to do is to default all the answers to the same answers that were recorded on the piece of equipment at the last inspection. Does anyone know how to do this with my current design?

    This is extremely important for my system to work effectively and was thinking DLookup was the way to do it

    If someone can give me a way to default one or 2 certain answers I may be able to figure out the rest

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,273
    This will certainly require VBA code. Can use DLookup for each answer to retrieve value and then populate new record. Something like:

    If IsNull(Me!fieldname) Then Me!fieldname = DLookup(...)

    The trick is figuring out what event to put code in. Try the OnCurrent event of 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.

  3. #3
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Yes it is vbcode that will be required and my difficulty for this part is also how to get the code to dynamically get the value

    I can easily dlookup from one table and have used it throughout my database multiple times and can even dlookup where one value equals another value from a form or from multiple tables in my database

    My situation from what I can see is a bit more complex

    for this to work the dlookup must look to see if the piece of equipment has been inspected before if yes then it must find the last equipInspectID --> using the equipInspectID it will have to find the linked response from tblResults linking equipInspectID to each ChecklistID and AnswerID and for each ChecklistID (Question) it will have to put the last AnswerID (Answer from tblChecklistAnswer) dynamically into the frmChecklist

    Hope this makes sense and am extremely grateful for anyone that can help me code this. This ideally would also just do the same thing in a for loop for each question

    Thanks

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,273
    Yes, that is complicated. Would need a series of DLookups, each dependent on the result of previous. Or, possibly a single recordset in VBA could serve.
    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
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks June7, any idea where to start or a hint on how to code this. Thought DLookups would be the go however not sure how to do this in this situation. Also don't understand what you mean by a VBA recordset

    Thanks

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,273
    How to explain recordset? Hmm. It's basically a virtual table, a set of data stored in virtual memory. Uses SQL SELECT statement in VBA code to query data table(s). Check out this reference http://allenbrowne.com/ser-29.html

    By using recordset can programmatically read each record and manipulate data. Understanding recordsets opens up a whole new world of programming capabilities.

    Whereas DLookup can only retrieve a single value. This value can be set to a variable and the variable can be used in subsequent code process.
    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
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Yes they both look like they will do what I want, any tips on code

    Thinking something like

    Select Answer FROM tblChecklistAnswer
    if EquipmentID = EquipmentID from tblEquipment
    THEN Select LastEquipInspectID FROM tblEquipInspectJunction
    --> THEN
    SELECT AnswerID from tblResults where EquipInspectID = EquipInspectID and ChecklistID = ChecklistID
    AND then Select Answer WHERE ANswerID = AnswerID in tblChecklistAnswer

    Loop through all instances

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,273
    By using a recordset I would hope that one SELECT statement would be able to retrieve all needed fields. This will probably require JOINing tables in the statement. Use Access query designer to help build the SQL statement then recreate it in VBA code.

    This won't come quickly - learning to use recordsets effectively will take time. On top of that is learning VBA in general. Also need a good grasp of basic programming concepts, such as conditional statements, looping, variables.

    Otherwise, what you show is more like a series of DLookup calls to the data tables.

    You will have to attempt code and when encounter specific issue, post question.
    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.

  9. #9
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    OK this select statement will filter exactly the instance I want for each EquipmentInspect/ checklist question/ answer instance

    SELECT tblChecklistAnswer.ChecklistAnswerID, tblChecklistAnswer.ChecklistID AS tblChecklistAnswer_ChecklistID, tblChecklistAnswer.AnswerID AS tblChecklistAnswer_AnswerID, tblResults.ResultsID, tblResults.EquipInspectID, tblResults.ChecklistID AS tblResults_ChecklistID, tblResults.AnswerID AS tblResults_AnswerID
    FROM (tblChecklist INNER JOIN tblResults ON tblChecklist.[ChecklistID] = tblResults.[ChecklistID]) INNER JOIN tblChecklistAnswer ON tblChecklist.[ChecklistID] = tblChecklistAnswer.[ChecklistID]
    WHERE (((tblChecklistAnswer.ChecklistID)=([Forms]![frmInspections]![frmEquipment].[Form]![frmChecklist].[Form]![ChecklistID])) AND ((tblResults.EquipInspectID)=([Forms]![frmInspections]![frmEquipment].[Form]![EquipInspectID])));

    I then just need to display the answer that matches the tblChecklistAnswer.AnswerID with the tblCommonAnswers.AnswerID... God knows how

    Am I on the right track?

    Thanks

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,273
    Let's review what you want:
    "What I want to be able to do is to default all the answers to the same answers that were recorded on the piece of equipment at the last inspection."

    The query you showed doesn't have equipmentID so can't retrieve answers related to a specific equipment.

    I am somewhat confused by your data structure. AnswerID and ChecklistID fields are present in both tblResults and tblChecklistAnswer, both of which are in your query. Neither of these tables has data in AnswerID field. I really don't see reason for tblChecklistAnswer. This query seems to retrieve needed data and it is updatable.
    SELECT tblEquipmentInspectionJunction.InspectionID, tblEquipmentInspectionJunction.EquipmentID, tblResults.ChecklistID, tblResults.AnswerID
    FROM tblResults LEFT JOIN tblEquipmentInspectionJunction ON tblResults.EquipInspectID = tblEquipmentInspectionJunction.EquipInspectID;

    If that query is correct, save it as an Access query for later reference in code. I have another idea that doesn't involve opening a recordset in VBA. It would be an INSERT SELECT sql but depends on the above query being correct.
    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
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    The query you showed doesn't have equipmentID so can't retrieve answers related to a specific equipment. Of course, my mistake this really confuses me I apologise. It definitely needs to match the equipmentID as well

    I am somewhat confused by your data structure. AnswerID and ChecklistID fields are present in both tblResults and tblChecklistAnswer, both of which are in your query. Neither of these tables has data in AnswerID field. I really don't see reason for tblChecklistAnswer. tblChecklistAnswer was included as a recommended table for a junction table between tblchecklist/tblCommonAnswers
    As a checklist item has multiple possible answers and possible answers may be used for multiple checklist items

    Therefore I have a table that holds all possible answers (tblCommonAnswers) and a junction table that joins each checklist item with its applicable possible answers (tblChecklistAnswer)

    Is this relationship correct I am confused as it seems that same data is stored in tblChecklistAnswer and tblResults. tblResults should already be my junction table, right?

    There is not much data in AnswerID as the form i have currently has many questions and I can't fill them all in everytime) also I can't seem to work out how to populate tblChecklistAnswer at the moment (however come to think of it I may not need this table)

  12. #12
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    I think tblChecklistAnswer was actually so I could have different available common answers for each individual checklist question, however this may not be totally necessary.

    Your query does deliver the required fields however it does not do any filtering I think it at minimum still requires a where clause for current checklist item on form and answerID must equal answerID in tblCommonAnswers to fill the combo with the correct default value

    However I am out of my league on this one for sure

    Thanks

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,273
    Okay, tblChecklistAnswers is not to record users responses, but serves as a lookup for answers applicable to particular checklist item. This could be useful if you want to limit users choices in a combobox RowSource. But that is another issue and therefore the table is not relevant to issue under discussion.

    If the query I suggested is correct, then I offer this. Save the query. Then in code:

    x = DMax("InspectionID","queryname","EquipmentID=" & Me!EquipmentID)
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO queryname (InspectionID, EquipmentID, ChecklistID, AnswerID) SELECT " & Me!InspectionID & " AS InspectionID, EquipmentID, ChecklistID, AnswerID FROM queryname WHERE InspectionID = " & x & " EquipmentID=" & Me!EquipmentID

    DoCmd.SetWarnings True

    This will save a full set of checklist items with default answers for a new inspection for a particular equipment.

    Now the tricks are to figure out what event to put the code in and how to determine the new InspectionID and EquipmentID to pass to the SQL.

    One approach is user selects the equipment to be inspected, records are saved then open form to the new records to review the answers.

    However, think a problem just occurred to me. Apparently an inspection can have multiple equipment. Need to identify all equipment for a new inspection at the same. This will change my suggested code somewhat.
    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.

  14. #14
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks June 7, not been able to get this to work yet, however I think I understand what you are trying to do with it.

    Noticed you may have added some more to this post and yes you are correct a single inspection can have multiple equipment, however the equipment is added to the inspection one by one so theoretically you can just run this code again every time you add another piece of equipment to the inspection

    This is all very new to me, I assume I put this code in the before update section however as I said I can't get it to do anything at the moment, it is hard because I cannot see what it is doing in the background either. I will start by trying to msgbox the variable value so I can at least see if it is assigning anything

    I will keep trying

    Thanks

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,273
    Forget the DMax function as suggested. User would have to identify the inspection and the equipment then click a button to add records. Put code in the Click event. The SQL would refer to the form controls to get those criteria. Problem is with the first equipment entered. InspectionID doesn't yet exist. Either have to create the Inspection record before going to enter equipment or give user option to create new Inspection at time of entering first equipment. The latter gets a bit more complicated.
    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.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Default Min & Max Date Values in Form
    By alsoto in forum Forms
    Replies: 3
    Last Post: 10-03-2011, 10:54 AM
  2. Default Values in a form
    By handjor in forum Forms
    Replies: 3
    Last Post: 08-03-2011, 09:11 AM
  3. Replies: 8
    Last Post: 05-25-2010, 04:50 AM
  4. Last matched item in an unmatching query
    By kabaa01 in forum Queries
    Replies: 0
    Last Post: 05-19-2010, 03:46 PM
  5. Default Values
    By twainscott in forum Programming
    Replies: 0
    Last Post: 09-14-2006, 11:18 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