Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62

    Method for finding record for primary key, and then appending that record

    My primary key is a Serial Number that is entered in the first operations form, OP 20, creating a new record. The first form fills out a handful of columns for that SN with data. In OP30, I would like to append that record by adding data to the next set of columns. I tried to set up a combo box from which you can select an existing serial number, and then write to that record. However, it is trying to overwrite the Serial Number, or maybe write a new one, which is giving me an error. Is it possible to use the combo box to select the row to be written to using the SN, but not actually write the SN value to the table?

    Exact error text: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."



    Click image for larger version. 

Name:	Capture.JPG 
Views:	27 
Size:	13.9 KB 
ID:	32572

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are trying to append when you should be updating. You don't append to new records, you update them. You append records to tables (or delete).
    Yes it is possible, but there's too little information here to expound further. You'd need to provide at least the sql you've tried. That might or might not be enough for us to decipher.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    Quote Originally Posted by Micron View Post
    You are trying to append when you should be updating. You don't append to new records, you update them. You append records to tables (or delete).
    Yes it is possible, but there's too little information here to expound further. You'd need to provide at least the sql you've tried. That might or might not be enough for us to decipher.
    Ok; I'm new to Access, so please bear with me and let me know if I need to provide with more information. I'll do my best to provide what I have.

    I'm not using any SQL - rather, I'm using the Control button wizard for the New part/Save part buttons, and for the combo box.

    Combo box:
    Control Source SN
    Row Source t_23071095
    Row Source Type Table/Query
    Bound Column 1

    New part macro: GoToRecord: Record: New
    Save part macro: RunMenuCommand: Save

    here is a sample of the main table filled with some bogus test data, cut off after the first OP30 entry. As you can see, the first form fills all the OP 20 columns, then the second form should allow you to select the SN and fill the OP 30 columns for that record, ETC. I'm trying to figure out how I can make the combo box act as that selector, in such a way that it does not try to overwrite the SN or create a new record with the same SN value (both error cases).

    SN OP20 CUBE NO OP20 1 OP20 2 OP20 3 OP20 4 OP20 5 OP20 6 OP20 7 OP20 8 OP20 INIT OP20 BCHECK OP20 NOTES OP20 TIME OP30 CUBE NO OP30 1
    1461461
    1 Yes Yes 5 4 6 7 5 BD GG
    2/8/2018 11:51:59 AM 3 13
    T654987

    No No




    BD bd
    2/8/2018 11:50:36 AM 12 54
    TESTUPDATE11 1 1 Yes Yes 1 1 1 1 1 BD GD
    2/8/2018 11:58:19 AM


  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You want to apply filter to retrieve existing record?

    A combobox used to input filter criteria must be UNBOUND (no Control Source). Can be BOUND only if using the intrinsic search/filter tools from the ribbon and right click shortcut menu.

    See if this is what you are looking for http://allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't know if you get the drift of June7's comments, but a bound combo will change the underlying data in most cases (as long as no aspect of an underlying record source doesn't prevent that) so you can't use it as a type of choice/selection provider. Not sure you should be using a 2 form approach to modify an existing record. That and the look of your data suggests your data is poorly (if at all) normalized. As a newbie, you should make sure you're on the right track in this regard, lest you are continually frustrated in your attempts. Maybe read up on it? Here's 1 or 2
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

  6. #6
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    Awesome, thank you, I will read through those.

    As for the multiple forms (There will be one form per operation, and each part usually goes through about 8 operations total), they are necessary as those operations occur at separate times and are done by separate people. So I want another operator to be able to open that serial number and add the inspection data for that particular operation. Each operation has different inspection parameters, but all of the serial numbers go through the same operations and inspections, which is why I set it up the way that it is. Is this a valid method for doing this, or could it be done in a better way?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A form for each operation might be appropriate.

    Or maybe just one form with fields distributed on pages of a Tab control.

    Depends a lot on data structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This reminds me of a CMMS work order task situation.
    A WO is on the main form with all related data (creator, approver, start date, equipment id, etc, etc).
    The task is a subform, with all pertinent fields and links to supporting data (SOP's, parts lists, sub-equipment id's, etc).
    Navigation is provided to move from task to task, or jump to a task (tasks themselves can be a separate datasheet form presented as a list)
    If the task is completed (TaskComplDate is not null) or any other action is taken to "lock it up" all the fields are disabled. Otherwise, the task is open for work by the AssignedTo individual or department. So, only two forms for WO and Task.

    Your inclination to use a form per task/operation doesn't jive with me. Some parts need 3 operations, some 6, some 8? Too volatile, and a tab control per task just seems awkward. You'd have to show/not show (or enable/disable) tabs where the operation doesn't need the number of tabs you have on the form. I think most would agree that beyond simple oversight (where the db structure is normalized but you just forgot an attribute for an entity) if you have to add forms to support the same basic process because you introduce a new operation, something is wrong. I'm not convinced you're on the right track, and I say this with over 20 years of using that CMMS system as the basis for my opinion.

  10. #10
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    Micron:

    I'm interested in your approach, but I'm not sure I understand it. How can I avoid having a form per operation? There isn't really a work order situation - essentially, we need to be able to see all of the inspection data tied to one serial number. Each field is essentially unrelated, there are no inherent relationships. I can't think of any way to normalize this beyond what is already done. There's just no standard format for a form - some of them have 3 inspection fields, some have 6, and the measurement for each field is always different. I have attached an example of a form.

    Click image for larger version. 

Name:	FormExample.JPG 
Views:	16 
Size:	53.0 KB 
ID:	32617

    Would it help to zip up and send you the database I have so far? I am obviously new to this and just trying to put something together as a first step away from paper forms for this company where I am interning; I don't know much about databases at all.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This reminds me of db I developed for tracking construction materials sample testing. Your 'process' is my 'test'. A sample can have any number of tests associated with it (there are about 200 different tests to choose from). I have a data table for each test. The end purpose of my db is to generate a laboratory report of a sample's test data for distribution. Very little aggregate data analysis is accomplished.

    Each sample will have only 1 set of results - a sample is processed only once. This means in my db the sample and test tables have a 1-to-1 relationship. In your db can each serial number have multiple inspections? This is 1-to-many relationship.

    It is a balancing act between normalization and ease of data entry/output. Normalize until hurts and denormalize until works. https://blog.codinghorror.com/maybe-...g-isnt-normal/
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    Quote Originally Posted by June7 View Post
    This reminds me of db I developed for tracking construction materials sample testing. Your 'process' is my 'test'. A sample can have any number of tests associated with it (there are about 200 different tests to choose from). I have a data table for each test. The end purpose of my db is to generate a laboratory report of a sample's test data for distribution. Very little aggregate data analysis is accomplished.

    Each sample will have only 1 set of results - a sample is processed only once. This means in my db the sample and test tables have a 1-to-1 relationship. In your db can each serial number have multiple inspections? This is 1-to-many relationship./
    I feel that this would only work if I could standardize my inspections; it sounds like in your case, each "test" had specific and consistent fields related to them. This is not the case here, though. Each "test" is an individual measurement of a dimension of that part. It's all completely unrelated and arbitrary.

    If I were to separate the operations into separate tables - this is what I initially planned to do, but ran into problems - how would I go about keeping the serial numbers persistent through the operations after 20? The idea is for the operator to read the serial number off of the part, enter that (unique ID) and data into Operation 20, and then that SN would be carried over to the remaining inspections. So in every secondary operation, they don't enter a new SN, they look up an existing one and add data to that. Sorry if my explanation is messy.

    From what I can see, the structure of my data just doesn't play well at all with simplification, since everything is a one-off and is not related to anything but one serial number. So normalizing doesn't make any sense, because the data entered only relates to one single ID and nothing else.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Sorry, I am totally lost as to what your business model is.

    How can an inspection not have a standardized set of criteria?

    As for 'keeping the serial numbers persistent', don't really understand the question but I suppose code and form design should be able to accomplish.

    Here is link to my db if you want to explore it. https://app.box.com/shared/r8nea07sng Even as a zip folder it is over 4mb. Hold down shift key when you open it then change the table links.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Join Date
    Apr 2017
    Posts
    1,673
    It looks like 3-level database.
    1. On 1st level, you have your product or whatever, with SerialNo as ID - you need a table for them - p.e. tblProducts: SerialNo, ...;
    2. On 2nd level, you have your cubes/operations/whatever - you need a table for them too - related with 1st one as one-to-many. P.e. tblProdOperations: ProdOpID, [OpID], SerialNo, ...;
    3. On 3rd level, you have your tests/measurements/whatever - related with tblProdoperations as one-to-many. P.e. tblProdOptTests: ProdOpTestID, ProdOpId, TestType, NumericValue, BooleanValue, TextValue,...
    TestType determines the type of measured value, and into which of value fields the result is saved.

    For form design I think the best is
    1. A single main form where you select the product or register the new one;
    2. A continuous subform on main form, which lists all operations for active product (you can add new operations for product there too);
    3. Another continous subform on main form, which lists all tests for active operation in first subform (where you can also register any number of new tests). Technically you need a hidden unbound textbox on main form. The OnCurrent event of 1st subform writes current ProdOpID value to this hidden textbox, and the second subform is linked to this textbox.

  15. #15
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    Let me start from square one and outline what I'm trying to accomplish instead of just asking for specific steps.

    Its a contract manufacturing/machining plant, where we receive parts and process them with various operations. One part will go through about 8 operations on average. These processes are engineered specifically for that part, as each part has its own dimensional specifications. So for example, part A may need to have 3 faces grinded followed by 2 profiles milled, while part B needs to have a hole punched by an EDM machine, then 2 faces ground. Every process this part undergoes then has specific dimensional tolerances that need to be checked for that operation.

    So you've got:
    1. Part Number
    ----A. Serial Number
    ---------i. Operation 20
    -------------Measurement A
    -------------Measurement B
    -------------Measurement C
    ---------i. Operation 30
    -------------Measurement A
    -------------Measurement B
    -------------Measurement C
    etc.

    These the measurements are unique to the operations, and the operations are unique to the part number.
    There is no case in which Op 20 has the same fields as Op 30. There is no case where one Part Number has the same operations as another Part Number.

    Every serial number under the part number has the same set of ops though.

    The part number describes the type of part being made, while the SN describes the individual part.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-25-2016, 02:56 PM
  2. Replies: 4
    Last Post: 04-01-2014, 02:33 PM
  3. Replies: 16
    Last Post: 02-06-2013, 03:06 PM
  4. Replies: 5
    Last Post: 08-24-2012, 10:32 AM
  5. Replies: 2
    Last Post: 03-25-2011, 12:22 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