Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    15

    Unanswered: How to update tables from a subform

    I have created a form containing a subform in datasheet view.
    Click image for larger version. 

Name:	Form.jpg 
Views:	10 
Size:	63.1 KB 
ID:	10562

    This is my ERD.
    Click image for larger version. 

Name:	ERD.jpg 
Views:	17 
Size:	47.2 KB 
ID:	10559

    Form Select:
    SELECT ProjectToParticipant.ProjectID, Participant.ParticipantID, Participant.NameFirst, Participant.NameLast, Participant.NameMiddle
    FROM Participant INNER JOIN ProjectToParticipant ON Participant.ParticipantID = ProjectToParticipant.ParticipantID
    WHERE (((ProjectToParticipant.ProjectID)=[Forms]![ParticipantInfo]![cboProject]));

    Subform select:
    SELECT ParticipantTestScores.ParticipantID, ARTest.TestName, ParticipantTestScores.Score, ParticipantTestScores.Right, ParticipantTestScores.Location, ParticipantTestScores.Motion, ParticipantTestScores.BodyPosition
    FROM ARTest INNER JOIN ParticipantTestScores ON ARTest.ARTestID = ParticipantTestScores.ARTestID;

    The master and child link fields are: ParticipantID

    To add a test to the datasheet the user drops the list to select a test.
    Click image for larger version. 

Name:	form drop.jpg 
Views:	9 
Size:	71.9 KB 
ID:	10561

    When the test name is selected I get this error.
    Click image for larger version. 

Name:	error.jpg 
Views:	9 
Size:	10.7 KB 
ID:	10560

    1. Why am i getting this error? When I click help it says the table is locked. Which table? I have not locked any table and no tables are open in another window.

    2. When I close the form and look at the ARTest table a new record has been added.
    Click image for larger version. 

Name:	Bad record.jpg 
Views:	7 
Size:	31.0 KB 
ID:	10558

    The objective of the datasheetis to add a record to the ParticipantTestScores table.
    - When the user selects a test name the key for that test should automaticly populate ParticipantTestScores.ARTestID
    - The key for the participant should populate automaticly populate ParticipantTestScores.ParticipantID

    What is the correct way to do this?

    Thanks

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    the screen shots are too small for me to read; but what strikes me is that you have a subform select....and that select statement has a join....which may be rendering the underlying query to be non updateable...

    when you plug in a subform - the wizard walks you thru the set up...and one doesn't need a select statement.....

  3. #3
    Join Date
    Feb 2010
    Posts
    15
    Quote Originally Posted by NTC View Post
    the screen shots are too small for me to read;
    I think if the screen shot is clicked the enlarged view is presented.

    Quote Originally Posted by NTC View Post
    ...but what strikes me is that you have a subform select....and that select statement has a join....which may be rendering the underlying query to be non updateable...
    This is a simple join to a look up table to get the test name. Would that actually cause Access to fail?

    Quote Originally Posted by NTC View Post
    ...when you plug in a subform - the wizard walks you thru the set up...and one doesn't need a select statement.....
    I would like a deeper understanding of how Access works than blindly following the wizards. Using the UI to update or add a row in a normalized DB should not be that is easily done in code and not have to rely on a wizard.

    I really want to understand why Access is behaving in this way.

Posting Permissions

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