Results 1 to 14 of 14
  1. #1
    zydeceltico is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6

    Data entry create new record in separate table at same time

    Hi All - new to the forum....and an aspiring yet novice Access user. I am in the QC department of a steel manufacturing plant. We take big coils of steel and roll them into forms.



    I am in charge of inspections. I am building an inspection database.

    Basic DB structure has tables: tblJobs (general fields pertaining to Job); tblParts (ultimately the finished rolled sheets of rolled steel); tblInspections (junction table for the various types of inspections); tblMillInspections (one of several inspection types but the most relevant tbl for my current question); and tblCoils (the raw material, coils of steel).

    We purchase our coils of steel from several suppliers. Each coil has a specific and unique coil number. We suspect that some of the issues we see in the manufacturing process are related to supplier processes.

    We have hundreds of coils in house at any time. Coils are not specified for a job prior to the manufacturing process. In other words, we do not know which coil will be used until it is actually being mounted on the mill.

    To accommodate this business process which will never change - ever - I have not been using tblCoils at all. I simply have a field in tblMillInspection where I enter the coil number which is stored in tblMillInspection. Then - once I know the coil number of the coil being used for the inspection that I am currently working through, I make an entry in tblCoils where I can add may other pieces of data that are coil-specific and unrelated to the inspection.

    This is obviously not efficient at all.

    The two fields in tblCoil that are relevant for my question are Coil_PK (autonumber, primary key) and CoilNumber (the literal string, e.g., R30836). As all coils have unique numbers, I suppose I could use CoilNumber as the primary key but I have chosen not too as that coil number might be repeated a lot of times on a number of different job inspections.

    What I would like to do - and don't know how to do - is open my inspection form; enter the coil number; and have that entry continue to show as the coil number on my form but in the backend two things happen: 1) a new record is added to tblCoil which means a new Coil_PK and a new CoilNumber are added to tblCoil and 2) the new coil is referenced as foreign key in tblMillInspections for the current inspection taking place linked to the new record in tblCoil.

    FYI - the inspection form is frmMillInspeciton and is based on tblMillInspection.

    It is also of note that we may use multiple coils on a single job - or we may only use one. Or we may use partial coils or leave partial coils. It depends on the size of the job. That is also why I do not store coil numbers directly with jobs.

    Thoughts and ideas?

    Thank You!

    Tim
    Last edited by zydeceltico; 02-08-2019 at 08:58 AM. Reason: additional info

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    What about a combox with the rowsource set to tblCoils and use the not in list event to add new coils to the table?

  3. #3
    zydeceltico is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6
    Quote Originally Posted by moke123 View Post
    What about a combox with the rowsource set to tblCoils and use the not in list event to add new coils to the table?
    With my limited knowledge that does sound like it makes logical sense. I'll see if I can get that to work.

    Do I need to anything special in the not in list event? Is VBA necessary?
    Thanks.

    Tim

  4. #4
    zydeceltico is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6
    Quote Originally Posted by zydeceltico View Post
    With my limited knowledge that does sound like it makes logical sense. I'll see if I can get that to work.

    Do I need to anything special in the not in list event? Is VBA necessary?
    Thanks.

    Tim
    I see the "not in list" event. What code do I need to add to that? I'm sure it looks something like DoCmd.AddNew??????????
    :-)

    Like I said me = "knows just enough to be dangerous."

    Thanks!

    Tim

  5. #5
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    zydeceltico,

    I am also in QC and have similar things I need to keep track of. What you are trying to do is perfectly doable, and like Moke123 said, you can use a combo box, I use them all the time.

    If you could post a zipped copy of the file, I'll be happy to assist. Your the first QC person I have seen on the forum.

    Thanks

    Dave

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    zydeceltico is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6
    Quote Originally Posted by orange View Post
    Re the Not in List, you may find this free video helpful.
    THANK YOU
    That is exactly what I needed and so crazy simple.
    Thanks!

    NOTE: for anyone else who may benefit from this, it is important to also make sure that "Limit To List" is set to Yes otherwise it does not trigger "List Items Edit Form."

  8. #8
    zydeceltico is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6
    Quote Originally Posted by Dave14867 View Post
    zydeceltico,

    I am also in QC and have similar things I need to keep track of. What you are trying to do is perfectly doable, and like Moke123 said, you can use a combo box, I use them all the time.

    If you could post a zipped copy of the file, I'll be happy to assist. Your the first QC person I have seen on the forum.

    Thanks

    Dave
    Thanks Dave. I totally appreciate the offer. The video that orange supplied below answers my question and I believe that - for the moment - I am good to go.
    But there will be more issues come up I am sure. :-)

    Tim

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tim,

    The video I recommended addresses the specific point on not in list. But Dave is in QC and has experience with Access and QC, you might want to see what he has to offer or his comments re your approach. I'm sure there's a lot more there that could be helpful to you beyond not in list.
    Good luck with your project.

  10. #10
    zydeceltico is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6
    Quote Originally Posted by orange View Post
    Tim,

    The video I recommended addresses the specific point on not in list. But Dave is in QC and has experience with Access and QC, you might want to see what he has to offer or his comments re your approach. I'm sure there's a lot more there that could be helpful to you beyond not in list.
    Good luck with your project.
    Oh I know. I totally understand and agree....and fully intend to do just that. :-)

    The info from the video you shared is a game changer for the process I am trying to put in place. I'm going to be able to redesign and relate several issues I have been facing. My plan is to make those changes, get a better representation of where we are trying to go with the db and then post again.

    Dave's experience in QC and inspections will be invaluable as my "next big question" is specifically about inspection table design given the variability of inspection conditions and the challenge of trying to design tables, forms, and process that are normalized and non-redundant.

    Dave - I'm definitely looking forward to sharing and "asking" - but asking a better question, better formatted. If I shared the db in its current state I won't be able to ask the best questions that are really hounding me - which at least for me - are far more complicated. I'm sure you'll have insight that will greatly benefit me and this effort.

    As someone, somewhere once said: "I'll be back." :-)

    Tim

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good to hear. I sort of figured that and would emphasize --you don't have to wait until you have a physical data base before checking/vetting your scope, approach or design intentions.

    See the link in my signature re Database Planning and Design for lots of tutorials and reference material.

    Best of luck with your project.

  12. #12
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Just let me know what I can help with, I am sure I have faced many of the issue that you will and hopefully with the QC background I hopefully will have an understanding that some others may not have and may be able to help you avoid some issues. I have a couple that lend themselves very well to trace-ability of where lots are used, what final products a given lot went into, etc.

    Thanks
    Dave

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Dave,
    I'd be interested in seeing your tables and relationships.

  14. #14
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Orange,

    I cannot get a good screenshot of the relationships because of the size of it and it seems that yet again, it doesn't fit the window properly, but here is a part of it.
    Click image for larger version. 

Name:	Capture of relationships for orange.PNG 
Views:	14 
Size:	181.6 KB 
ID:	37406

    Dave

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

Similar Threads

  1. Replies: 6
    Last Post: 10-22-2022, 08:27 AM
  2. Replies: 2
    Last Post: 03-16-2017, 05:50 AM
  3. Replies: 9
    Last Post: 03-01-2017, 10:00 AM
  4. Replies: 2
    Last Post: 02-23-2016, 04:36 PM
  5. Replies: 2
    Last Post: 01-25-2016, 12:21 AM

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