Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Location
    Artesia, CA
    Posts
    5

    Exclamation Unanswered: Need assistance with Access DB Project (DB, Form, Query, Report design)

    Hi Everyone,

    Version: Access 2000

    First, let me first describe this project that I'm trying to complete. I'm trying to create an Audit database project from scratch. Instead of tackling everything at once, I want to be able to fully understand each phase of my project before moving on to the next step. Since I have no experience, I've included screen shots of a sample form and what I want my database application to do. Basically, the auditor will review the data in the Audit form. If the data matches, the "correct" checkbox option is selected. If the data does not match, the "incorrect" checkbox is selected with the option to insert the "correct" data into a field. Eventually, I want to generate a report based on the number of correct and incorrect fields (a calculated percentage score).

    Part one: I want to be able to populate all the fields in this form using existing data from backend MySql DB tables. I created a link to the applicable tables via an ODBC connection. How do I create an append query to view the data in the form and then save the results in an access table?

    Hope this makes sense.

    I appreciate everyone's assistance.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    IF I UNDERSTAND THIS CORRECTLY:

    Create a table in your Access file for the form to use. Append the data from your linked MySQL table to the new Access table. Then bind the form to the Access table.

    I get the distinct impression that I am not quite understanding what you want to do.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Sep 2009
    Location
    Artesia, CA
    Posts
    5
    Thanks StarTrekker.

    I guess I'm not using the right terminology. Anyhow, I did what you suggested, binded the form to the new access table. I also created a command button to run the append query from the form (using the wizard).

    How come the updated records don't show in the form after I run the append query? They do after I close/reopen the form. Is there a way for the updated records to show up automatically in the form?
    Last edited by Disciple1; 09-23-09 at 15:57.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You have to requery the form after running the Append Query. After the line of code that runs the Append Query use

    Me.Requery
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Sep 2009
    Location
    Artesia, CA
    Posts
    5
    Thanks for your help Missinglinq. It worked!!! I am so glad I stumbled across this forum. It is an awesome resource (aside from my Access Bible book which is still hard to follow - for me at least).

    Part Two: I need some design advice on how to accomplish the next phase. Since this will be an audit form, I need to compare the data/calculate the statistical accuracy of the data. For example (see attachment):

    If first, last, state, and zip fields are correct, the "Correct" check boxes are selected. If the data is incorrect (in the example - address, city, and phone), the "incorrect" check box is selected. Since 4 of 7 fields are correct, I need to display the average score in percentage on the form (4/7 = 57%).

    *** Eventually, I want to run and print these reports via a command button on the form itself (if possible) and save the statistical results to the database.

    Thanks again to all forum members. Your assistance is greatly appreciated.
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2009
    Location
    Artesia, CA
    Posts
    5

    How would I build this into an Access form!!!! Please help.

    Need help adding this to an access form. I want to be able to add correct/incorrect check boxes to the form (please see zip attachment) and calculate a percentage score based on the number of correct/incorrect check boxes ticked.

    I already bind a form to an access table (records populated via an append query). On the form, I want to add a correct and incorrect check boxes then calculate a statistical and financial score based on the check boxes ticked then print a report based on ProcessorID. If any fields are incorrect, auditor will be able to enter correct data in form and then print score results and corrected data on a report via a command button. Please use excel spreadsheet for example.

    Thanks everyone for your assistance.
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Should really create a new topic for each issue you have btw.

    Add the checkbox fields (yes/no) to your table and then just drag and drop them onto your form from the field list.

    Then you can add them up Check1 + Check2 etc... apply some math to it and your off and running.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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