Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Back to the same issue - it doesn't show in the query because your links are all inner joins.

    The table went from 649 records to 500
    So you deleted a whole bunch of records?

  2. #17
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    It appears that it is only new records that the form will not accept or allow me to enter and save. Same with the datasheet. If I edit anything previously existing there is no problem. The form and the tables all reflect the changes and it works both ways. Maybe the error is in how this was all originally set up. My "finding" at the end of this post may be most revealing. But first here is how I got there.

    The tables I have now all came from one mega table. I broke it up into seven tables and maybe the way I did that is the cause of my current situation. As an example, in the mega table I had a field "MedProf" and a field "Payment". I left the MedProf field (and 5 others) in a BASIC INFO table. The Payment field went into a COMPENSATION table. The common field/link in both tables is the contract number. Primary key in BASIC INFO and foreign in COMPENSATION (and the other tables) There were/are 649 different contracts.

    When I broke up the mega table I did it this way:

    1. Opened a blank table;
    2. Put in field names for the Contract # and the Payment;
    3. Went back to the mega table, copied the two fields and pasted them into the new table.
    4. Deleted those two fields from the mega table

    I did the same thing to create the other 5 tables.

    I created a one-to-one relationship between the former mega table and the other 6 tables. The primary key in mega was the contract # which is the foreign key in all other tables.

    I ran a query with the wizard bringing based upon the fields from the respective tables. I then created a form based upon the resulting datasheet.

    One big reason I think this may be where my problem lies. While the form and the datasheet will not save changes, the fields left in the mega table do get updated and saved. Nothing gets updated in the other tables. Could this have something to do with the "inner joins" you mentioned previously? Could my "copy and paste" above be the problem?

  3. #18
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    Sorry, I just noticed your "inner joins" post. I will see if I can figure out how to fix that and if not will send up a flare! Thank you.

  4. #19
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your table structure:
    - you started off with one mega-table with 649 records and ended up with 7 smaller tables, each with 649 records - why did you do that?
    - if all data belongs to a contract the shouldn't the contract # be the primary key on all 6 tables?
    - did the mega table have one record per contract and is it going to stay that way?

    I am sure someone here can describe to you exactly when Access will allow updating in a query and when it will not. All I know is that Access is very strict with what it allows. That is why I have kept telling you that it is a query problem, not an issue with the form.

    Basically you cannot do what you are trying to do. If you want to keep your tables in this structure then create a subform for each table. The user will select a contract and all the subforms will be refreshed based on that contract. You can use a tab control and have one on each page.

  5. #20
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    I will try to answer:

    #1 Each table contains information distinct from the others except for the contract number which is the common key. So, for example, table 1 has 649 names and contract #s. Table 2 has 649 locations for each name with the contract #, Table 3 has 649 start dates with the contract #, etc. I have read and have been told that having too many fields in one table can create problems and the mega table had probably 50 fields. Better, I was advised, to have separate tables and link them with a common field/key, in this case the contract field.

    #2 I was told/read that when linking tables I had to link the primary key (contract #) with a similar field (in my case contract #) in the other table where the contract # field was NOT the primary key.

    #3 Yes, one record per contract.

    Having worked with this some more I have found that I can have the one form and have it update all tables and save the new form. The catch was that I had to have data from at least one field in each table placed on the form. When I did that, it saved the form and updated the tables with no problem. Further, I could then delete all the information on the form from a particular table and the rest of the form would still remain. This may relate back to the "joins" issue you raised and maybe if I switch things from inner to outer I won't have the data input requirements mentioned to save a form. Either way, I can work with the solution I now have. Thanks for everything.

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

Similar Threads

  1. How or can I, save a form as a template?
    By rebfein in forum Forms
    Replies: 8
    Last Post: 01-22-2017, 12:44 AM
  2. Replies: 1
    Last Post: 11-15-2014, 12:43 PM
  3. Replies: 2
    Last Post: 07-15-2014, 10:39 AM
  4. Replies: 7
    Last Post: 12-02-2012, 08:32 PM
  5. How do I save info from a form
    By chibearsfan6 in forum Access
    Replies: 4
    Last Post: 10-24-2012, 03:46 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