Page 3 of 3 FirstFirst 123
Results 31 to 42 of 42

Default values from matched ids in tables

  1. #31
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    Am now getting a list in the combobox but instead of editing inspection I selected a new inspection is created. Further investigation reveals that frmInspection opens to a new record. Selecting an inspection from the drop list does not retrieve/goto selected inspection. I set the InspectionID textbox on frmInspection to Visible Yes to verify this. I notice now that frmInspection Data Entry property is set to Yes. This forces the form to open only to a new Record. Cannot view existing records.

    My code works. Then Private Sub Form_AfterInsert() executes. I have never used this event. When I change the Data Entry property to No this event errors.
    Last edited by June7; 10-22-2011 at 02:04 AM.
    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.

  2. #32
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    OK I have changed the add/ edit button to initiate from frmMainMenu as in this version

    therefore you can choose to to start a new inspection or edit an existing one

    That is good that you can get it to work, however the AfterInsert is required to add the checklist questions and equipinspectId to the results table

    Your code works if a piece of equipment has not been inspected however I can't get it to populate/ display the last answers

    We must be close to this one (did you intend to attach something)

    Attachment 4835

    Thanks

  3. #33
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    No, did not intend to attach anything.

    No, the AfterInsert should not be needed. My code adds records to tblResults through the query. I stopped code execution before the AfterInsert and the records were saved to table. That's the reason the query needed to be updatable.

    Your data is incomplete. Many Inspection records don't have dates. Most of the tblResults records don't have answers so there is nothing to display or to copy. I suggest you clean out the tables. This can be difficult because you have set relationships to force referential integrity but not cascade delete.
    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.

  4. #34
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Please feel free to delete all the data in the database I delete it 3-4 times a day.
    -->
    So I removed the AfterInsert and now my frmChecklist does not even open

    and therefore does not populate/ display either the questions or answers

    Also you do not want the code saved to the table at this stage as the user can still change all the answers as he works through the check-list the questions/ answers/ equipinspectID should only be saved if and when the checklist is finished

    Weird you code does seem to work fine if there is no inspection and with AfterInsert code intact it correctly tells you that the equipment has not been inspected and opens form as soon as i remove afterinspect I get no frmchecklist

    Attachment 4837

    Thanks

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

    OK I seem to have got it to do something by moving the code to lostfocus from afterinsert however it is extremely random

    I can't work out what answers it is giving me each time

    It seems to be cycling through the answers it delivers and for some reason it does not open the appropriate checklist anymore, so I have gone from just not being able to display the answers to not having a clue what is going on

    Thanks

  6. #36
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    Finally got it working.

    You should run Compile after edits. I had to comment out some code so project would compile.

    I will remove the file after you respond or in a few days.

    EDIT: Purpose served, file removed.
    Last edited by June7; 10-24-2011 at 07:01 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. #37
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    OK getting definitely getting there, thanks June7

    Unfortunately this still works randomly and I cannot tell what it is doing?

    At first I tried to add an existing piece of equipment that had been inspected and it gave me multiple answers. I then decided to delete all equipment/ inspection/ results data and start from scratch.

    I added a new piece of equipment and tried to add a new inspection with it and I no longer get the message that it has not been inspected?

    I then tried to add this piece of equipment to another inspection and it worked this time and provided me with the same answers I had given for the last inspection.

    So I changed the answers for the new inspection and and tried to add the equipment to another selection and only got the original answers (not the last answers)

    I then attempted to add an entirely new piece of equipment and it gave me the wrong equipment's answers

    Furthermore another piece of equipment showed me all the previous answers at once

    Not sure how you tested this to see if it is working however it is displaying extremely unpredictable behaviour at my end, please let me know if I am doing something wrong?

    Thanks

  8. #38
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    my mistake I have now got it working in new mode the erratic behaviour was because I was going in between modes and it does not work in edit mode

    Also does not work if a new piece of equipment has never been inspected as I no longer get a message

    For some reason it also will not allow me to inspect more than 1 piece of equipment either, I will try and work my way around this

    Thanks for your help June7

  9. #39
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    Here is what I did:

    1. removed all records from tblResults and tblEqupmentInspectionJunction, removed all but 5 inspections (79-83), removed all but one client

    2. tested adding equipment to inspection and checklist and answer records
    a. from MainMenu select client, site, inspection 79, click Edit Inspection
    b. from frmInspections click AddEquipment
    c. select equipment from droplist
    d. click OpenChecklist
    e. select answers
    f. save
    Repeat for inspection 80 and same equipment, change answers from the defaults carried over from 79
    Repeat for Inspection 81 and same equipment, default answers are from 80
    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.

  10. #40
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Hi June7 sorry to bother you again as you have already been an awesome help.

    I have been encouraged and I have taken the advice to restructure and recreate my db. I have managed to integrate most things from my original db into my new db except a few things that you helped me along the way with

    I think I have got my checklist to dynamically populate the questions OK in the new format (only for Pressure Vessel), I cannot find where I am going wrong defaulting the answers and for validation (both which you showed me) They are both included in my code if you remember how you did it (validation is commented out). I think it may be from my re-naming controls but can't work out where

    A better learning instrument if you would be so kind... If you could let me know what I am missing from your original code? I tried to copy and paste and reconfigure from my old db but am definitely missing some crucial detail

    Thoughts on new design/ structure are also sought

    I appreciate your help as always ( and apologies on subjects you probably had considered solved)

    Thanks again

    Attachment 4904

  11. #41
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    Part of the problem might be conflicting code. You have mostly what I suggested in the cmdOpenChecklist_Click event of frmAddEquipmentToInspection which adds records to tblResults. Then you also have code in Form_AfterInsert and Form_LostFocus events. I removed these from the revision I posted.

    Really don't need the message boxes in the Click event. You have commented the Requery line and eliminated the SetFocus in favor of the macro call, which I eliminated.

    My version works for me. Don't know what else I can do for you.
    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.

  12. #42
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks June7, think mine is working now

Page 3 of 3 FirstFirst 123
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