Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009

    Unanswered: [moved]Design Advice

    I am working on a database with these tables. LotNumber & priductType are already entered, BatchID and testID are auto numbers.

    I have designed this form. When someone types in the lot number and the date and tabs to the next field, the product type gets automatically filled in and the appropriate sub form is displayed.

    It 'works' but if someone enters the same lot number/date combination you get duplicate entries.

    How would I go about fixing this?

    Thank You For Your Time,
    Original Post

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    What code do you use in the Form?
    Have a nice day!

  3. #3
    Join Date
    Sep 2009
    There is a hidden productID textbox that is linked to the lot number. When you exit the date textbox it reads the value and compares that to the different product types. This in turn toggles the visibility of the appropriate subform and updates the product type textbox.The same function is linked to the prev./next buttons.

    I suppose I will have to rewrite the query in such a way that it reads the lot number and the date on the form and runs the query based off those values. I figured I could avoid pain and suffering in the future by having a better table design. I can do what I am told in Access, but I am a little green when it comes to practical design.

    I think I am going to develop a work around for now as I am facing a pending deadline, 'good enough for now' will suffice but I am still interested in a better way to design my tables, and I don't like doing things in a haphazard fashion.


    EDIT: Third time is a charm I hope:

    Here is the new table setup. The first time I did this, tblDailyBatch was an associative table until I realized that it was set up as a many-to-many relationship when I wanted nested one-to-many relationship. I just now realize why I had used the many-to-many relationship in the first place... I needed a concatenated key. If you see any glaring problems with this solution please let me know.

    BTW: Thanks for giving me a place to air out my thoughts!!!
    Last edited by djorn; 09-13-09 at 19:17.

Posting Permissions

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