Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2015
    Posts
    4
    Provided Answers: 1

    Answered: Auto-fill form using existing table data

    Hi all

    Having some difficulties with a database I initially built as the company now wants to expand on it and I'm a total newb at Access.

    I have a sub-form that has c.60 questions on it that users complete Yes/No responses after reviewing a particular document (e.g. Is the document clear?). Each question/answer is a field on a single table called "Documents". Each document has a unique ID. There's a Submit button that users click on to record the responses to the table, which subsequently clears the form.

    Users don't always complete the form before clicking Submit, so some questions are left blank for the document. They would like to be able to go back and edit the remaining questions via the form rather than go into the table.

    Q: Is there a way to auto-fill the form with the fields that have been completed by entering the document's unique ID?

    I have tried adding a combo-box via the Control Wizard and adding an 'On Change' code:
    Me.UniqueKey = Me.cboUniqueKey.Column(0)

    This works in auto-filling up to 20 fields, but I have over 60 to complete.
    Q: Is there a method to allow more?

    Also, once it has been auto-filled, because the Unique ID does not allow duplicates, it does not allow the user to save any updated data to the table.
    Q: Is there a way to overwrite/update the existing record with the new responses the user has added?


    Thank you in advance for any help and insight you can provide.

  2. Best Answer
    Posted by Dezza

    "Nevermind, I have figured it out. I had a dodgy form build which I have now corrected and everything's populating nicely when prompted.

    Thank you!"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is stopping the users from opening the form, selecting the row and filling in the details as required on subsequent visits to the application? that woudl be the traditional way of doing this sort of task

    I do not let users enter data directly into tables, nor do I encourage the use of datasheet views for data entry. its fine for brain dead accountants to think of it as a spreadsheet, jut as long as they doint try changing data
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Sep 2015
    Posts
    4
    Provided Answers: 1
    Yes, editing directly on the table is a no-no, which is why I am trying to re-populate the form using the partial data from the table.

    For example, if there is a unique key = GE00001 that has 20 of the 60 questions completed, I want them to be able to select this from the combo-box, and then this would subsequently fill in the 20 completed questions into the sub-form, which then allows them to continue to complete the rest of the 40 questions.

    At the moment, when a user does not finish the form, they can still click on the 'submit' button which subsequently writes the record and clears the form.

    I've been trying to Google and Youtube it but can't seem to crack it...I'm stuck on trying to retrieve the table data to go back into the form and then allow the user to update the record.

  5. #4
    Join Date
    Sep 2015
    Posts
    4
    Provided Answers: 1
    I have attached a redacted/edited version of the database. As I said, I'm new to Access so feel free to destroy it with any feedback, but ultimately looking to try and get an answer to my auto-fill question.

    DB summary:
    - used to capture responses to questions when reviewing documents
    - all data captured on Documents table
    - GEL_Form is mainly a sub-form due to number of questions

    Thank you!
    Attached Files Attached Files

  6. #5
    Join Date
    Sep 2015
    Posts
    4
    Provided Answers: 1
    Nevermind, I have figured it out. I had a dodgy form build which I have now corrected and everything's populating nicely when prompted.

    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •