Results 1 to 3 of 3
  1. #1
    8cheesestix is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2021
    Posts
    2

    Need to manipulate values from fields in one table to automatically complete fields in another table

    New poster here. Not much experience with vba, learning on the fly.



    I have two tables of concern in my database (linked) with forms for both. The Login table is completed first. The Breaks table is completed later.

    Within the Login table, I have the following, with sample input shown (Note: D=Day so 4D would be 4 days old):

    No Cyl 8
    Pour Date 7/8/21
    1D
    2D
    3D
    4D 2
    5D
    7D 2
    14D
    28D 2
    56D
    90D 2
    180D
    365D

    In my Login form, I have vba connected to a button that allows the user to add records to the breaks table based on the number input into the "No cyl" field (in this case, 8 records would be added).

    WHAT I NEED HELP WITH:

    On the breaks table, I would like to be able to populate this information based on the info in the Login table:

    (ex Two 4-Day cylinders with test date = 7/8/21+4)

    Cylinder Age DateTested
    1 4 7/12/21
    2 4 7/12/21
    3 7 7/15/21
    4 7 7/15/21
    5 28 8/5/21
    6 28 8/5/21
    7 90 8/29/21
    8 90 8/29/21

    NOTE: Fields in both tables are required. I can not eliminate the 1D through 365D fields as I have other required reports working off them that do not work properly based off the age and test date alone (I tried).

    Attached is pdf of two tables and my code to add records to breaks table (is within login form - can't add/manipulate "Cylinder", "Age", and "DateTested" here because they are not also in Login table).

    loginbreaksdb.pdf.

    TIA

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry to say that your table design is all wrong - it's like a spreadsheet. Access (tall) is not Excel (wide) and you should not create tables that way (e.g. repeating data in fields - 1D, 2D etc).
    My suggestion is to study and understand normalization. Then design the db schema. Even doing so on paper is a common approach by many db developers before diving in to table creation. One attempts to validate that design by working through scenarios. You could post a pic of your db relationships when done in Access for feedback, but be prepared for questions because understanding the process that the db supports is key.

    If you don't fix the design, your problems will only continue.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    8cheesestix is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2021
    Posts
    2
    I'm aware of and understand normalization. As I posted previously, I needed these fields set up in order to get a couple reports to work properly (in particular, a daily schedule that tells what cylinders to test that I couldn't get to work off just "age" and "datetested"). However, I tried again after your comment to get these reports to work off the "age" and "datetested" and was able to get them to work successfully. So hopefully, this will solve my problem and I can eliminate these fields altogether in the backend. Just have to double/triple check I'm not using them anywhere else first.

    Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-30-2018, 03:10 PM
  2. Replies: 3
    Last Post: 12-02-2017, 02:04 PM
  3. Auto Complete Form or table fields
    By faodavid in forum Forms
    Replies: 3
    Last Post: 08-05-2014, 11:13 AM
  4. Replies: 8
    Last Post: 07-14-2014, 03:20 AM
  5. Replies: 1
    Last Post: 12-09-2013, 04:23 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