Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: Data validation tbl to form

    Creating a form that contains 3 tables of data, merged into a single qry. The "basic" info tbl is intended to have the high level identifiers (name, dob, etc).

    Secondary tables are for income and expenses.

    The form has all information, however, I would like for the data in the "basic" category to be verified against the standalone tbl to ensure that the core information is there prior to the income/expense data being added and to prevent user from entering before the "basic" info has been added prior to using this particular form.

    My hope was to start entering the "Last Name" and have a cross-ref against the "basic" table and error to generate if that "Last Name" was not present on the table.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I think that first we need to talk about what you're doing here! If you've actually got a single Form, based on a Query that draws data from three Tables,then you have nothing to worry about!

    You have nothing to worry about because your Form will almost certainly be Read-Only, as Multi-Table Queries are usually are, and you won't be able to enter anything into any of the Controls!!

    Is this, in fact, the situation you're describing here? This type of situation is normally addressed using three Forms, one for each Table. The 'basic' information, as you say, would be in the Table behind the Main Form. The Forms used to display/enter data in the income and expenses Tables would be placed on the Main Form as Subforms. Each 'basic' entity would have a unique identifying number which would be Primary Key of that Table, and each of the other two Tables would have a like-named Field, which would be their Foreign Key. When you connect the Subforms to the Main Form (the Subform Wizard does most of the heavy lifting, here) Access will 'link' using these Fields.

    Using Validation code, in the Main Form's BeforeUpdate event, you can prevent a New Record from being created until all 'basic' info has been entered. With a little more work, you can prevent data being entered into the income and expenses Subforms until a Record on the Main Form exists.

    You can find help here on doing all of this, but you need to get your Main Form/Subforms set up first of all.

    If the situation isn't as you've described it in your original post, come back and let us know.

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

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