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

    Dynamic Form, Dynamic labels/ captions?

    I have a subform that has different questions (number and question) on it dependant on what value is selected from a combo box in the main form (EquipmentType).



    Ideally I would like to have the questions automatically fill the appropriate caption from the question table. Is this possible?

    To do this you would need to match the EquipmentTypeID selected in the combo box with the foreign EquipmentType ID in the Question table and therefore display/ unhide the correct number of questions and fill the captions with each question from the Answer table

    Not sure if you can use labels like controls, however would be the best way to do it if possible as the labels will then relate to answers for the answer table

  2. #2
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    229
    You can use labels as controls because they are controls, but first a few questions:
    1. Why are the questions in the Answer table? This indicates wrong design.
    2. When you say caption, do you mean label captions or the form caption itself?
    3. Why are you displaying/unhiding the required questions instead of populating only the required ones dynamically?

  3. #3
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    1. Why are the questions in the Answer table? This indicates wrong design. My mistake too long sitting at computer questions in question table, answers in answers table

    2. When you say caption, do you mean label captions or the form caption itself? I mean Label captions

    3. Why are you displaying/unhiding the required questions instead of populating only the required ones dynamically? Not sure if I understand I think that's what I do want to do, only need to populate questions and answers that are required for any given type of Equipment

    Thanks for your input Goodguy

  4. #4
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    229
    If you set your Main form /Sub form links correctly to filter by Equipment ID, your subform should populate correctly. The way you want to do it though will require textboxes instead of labels so that you could bind the recordsource.

  5. #5
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    The links/ relationships should be set up correctly as I have been on here for over 2 months trying to figure out best design,

    How can I make the questions from the question table populate the labels on the form for each equipmentID and therefore match the corresponding textboxes for the answer

    Here is a copy of my current database

    Attachment 4714

    Thanks

  6. #6
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    229
    Attached file rejected by Access 2007.

  7. #7
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Apologies here it is in 2003 format, I thought 2007/ 2010 were same format

    Attachment 4716

    Thanks

  8. #8
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    229
    I could not figure out the form that you want populated. Which is it? What have you tried so far?

  9. #9
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    The form I want populated is frmChecklist, At the moment I have simply typed the questions into the label fields as I have no idea how to fill these from the table.

    I can easily extract from the table to a combo box using SQL however when it comes to filling the labels with all the different questions I am at a loss.

    Notably the questions will come from tblChecklist and will populate the form based on EquipType.

    Also note that I have only filled the tblChecklist with some questions so far and furthermore there will be more questions for this EquipType and different questions for all Equipment as per tblEquipType


    Thanks

  10. #10
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    here is a later version with a fixed relationship, I didn't know how to make frmChecklist a subform of frmEquipment, not sure if this changes things

    Cheers

    Attachment 4732

  11. #11
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    229
    I have spent hours trying to rationalise your tables and relationships but still can't figure them out. And unless I can fix that part, I can't even begin to contemplate solving your stated problem.

  12. #12
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    I am not sure how to explain this to you however if you are interested the design was discussed in much length on this forum and all my threads discuss the design in varying amounts of details

    https://www.accessforums.net/search.php?searchid=144439

    specifically
    https://www.accessforums.net/access/...ase-16463.html
    https://www.accessforums.net/access/...ase-16943.html
    https://www.accessforums.net/forms/p...-vb-17501.html
    https://www.accessforums.net/program...elp-17857.html

  13. #13
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    It is interesting to see that you are finally using Sub Forms as I suggested ages ago.

    EquipTypeID should be a Text Box not a Combo.

    EquipmentNumber Should be a Combo not a Text Box.

    That is if I understand. You are adding a piece of Equipment to an Inspection you are not adding an Equipment Type.

  14. #14
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks Rain, long time no see.

    Yes took me a while to come around as the reason it wasn't working for me is because I didnt' and still don't understand access's inbuilt functionality (like how to link subforms), hence I tried to write everything in code

    I welcome your input as always and think I see what you are saying however maybe both could be combo's. The majority of the time it will be a new piece of equipment and they will either start by adding equipment type (or searching by equipment type if item exists). Furthermore there is a set amount of equipment types as per tblEquipType however there is unlimited equipment number

    Please keep checking back into my progress and offering your advice

  15. #15
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    I welcome your input as always and think I see what you are saying however maybe both could be combo's. The majority of the time it will be a new piece of equipment and they will either start by adding equipment type (or searching by equipment type if item exists). Furthermore there is a set amount of equipment types as per tblEquipType however there is unlimited equipment number

    If your Relationships have not changed then each time you add a Piece of Equipment you also select the Equipment Type. So by selecting (In the Form) via the Combo Box an existing Piece of Equipment you are automatically selecting the Equipment Type. Just make sure your Record Source refelects that.

    If the Exquipment is not on the list then don't forget to add the equipment Type when add the new piece of equipment.

    I can see you are getting some good advice from others.

    My work load has increased lately so I can't help much but will continue to monitor.

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

Similar Threads

  1. Dynamic Form and memory limit
    By pmi in forum Access
    Replies: 5
    Last Post: 10-07-2011, 09:49 AM
  2. Replies: 1
    Last Post: 03-14-2011, 11:11 AM
  3. Dynamic Data
    By top1hat19 in forum Access
    Replies: 4
    Last Post: 02-14-2011, 06:32 PM
  4. Dynamic functions
    By bubu678 in forum Programming
    Replies: 2
    Last Post: 11-09-2010, 09:55 PM
  5. help with dynamic hyperlink to pdf
    By iresolver in forum Access
    Replies: 0
    Last Post: 12-24-2008, 01:21 PM

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