none
Recordset not updateable RRS feed

  • Question

  • I have a form which is based on this query:

    SELECT [tblStudents]![FirstName] & " " & [tblStudents]![LastName] AS Student, tblHouseStudents.HouseID, tblYearClassStudents.YearClassID
    FROM (tblStudents INNER JOIN tblHouseStudents ON tblStudents.StudentID = tblHouseStudents.StudentID) INNER JOIN tblYearClassStudents ON tblStudents.StudentID = tblYearClassStudents.StudentID
    WHERE (((tblYearClassStudents.YearClassID)=[Forms]![frmAssignHouses]![cboClass]));

    When I try to enter data in the form for the  tblHouseStudents.HouseID field I get the following error:

    "This recordset is not updateable"

    When I try to enter the data directly into the query I get the same message. How could I correct this problem? 

    Wednesday, August 9, 2017 3:28 AM

Answers

  • I assume that you are trying to insert rows into tblHouseStudents.

    1.  Base the form solely on the tblHouseStudents table and bind a combo box to the HouseID column, setting the control up to show the house name or similar in its drop down by hiding the HouseID column in the usual way.

    2.  Include an unbound combo box in the form to select a class.

    3.  To select a student use a combo box bound to the StudentID column.  Show the student names in the control's list by hiding the StudentID column.  In the RowSource of this control join the tblStudents and tblYearClassStudents tables on StudentID, and correlate the combo box with the  cboClass control by referencing it as a parameter on the YearClassID column.  Requery this combo box in the AfterUpdate event procedure of the unbound cboClass control.

    For examples of broadly analogous operations, using correlated combo boxes, you might like to take a look at StudentLog.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file you'll find correlated combo boxes used extensively in the forms/subforms.  An example is the combo box to register a student for a course in the registrations subform in the students form.  The RowSource property of the combo box is:

    PARAMETERS [Forms]![frmStudents]![cboAcademicYear] TEXT,
    [Forms]![frmStudents]![StudentID] LONG;
    SELECT Courses.CourseID, Courses.CourseName
    FROM Courses INNER JOIN StudentYears
    ON Courses.AcademicYear = StudentYears.AcademicYear
    WHERE Courses.AcademicYear=[Forms]![frmStudents]![cboAcademicYear]
    AND StudentYears.StudentID=[Forms]![frmStudents]![StudentID]
    ORDER BY Courses.CourseName;

    This restricts the available courses to those offered in the academic year for which the student is being registered to courses.  In the parent form cboAcademicYear is an unbound control whose list is restricted by a parameter to those academic years for which a student is enrolled, and StudentID is the primary key of the parents form's recordset, which is a simple query on the Students table, returning the rows in LastName.FirstName order.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, August 9, 2017 11:33 PM
    • Marked as answer by Gordon Swan Sunday, August 13, 2017 10:16 PM
    Wednesday, August 9, 2017 11:28 PM

All replies

  • Hi Gordon Swan,

    do you mean you are using this query to load data in the form?

    you can see that it is a select query.

    which can only use to fetch the data.

    you can cannot insert or modify the data using this query.

    try to change your logic for inserting data. 

    you can try to use VBA to insert new data from the form.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 9, 2017 8:20 AM
    Moderator
  • I assume that you are trying to insert rows into tblHouseStudents.

    1.  Base the form solely on the tblHouseStudents table and bind a combo box to the HouseID column, setting the control up to show the house name or similar in its drop down by hiding the HouseID column in the usual way.

    2.  Include an unbound combo box in the form to select a class.

    3.  To select a student use a combo box bound to the StudentID column.  Show the student names in the control's list by hiding the StudentID column.  In the RowSource of this control join the tblStudents and tblYearClassStudents tables on StudentID, and correlate the combo box with the  cboClass control by referencing it as a parameter on the YearClassID column.  Requery this combo box in the AfterUpdate event procedure of the unbound cboClass control.

    For examples of broadly analogous operations, using correlated combo boxes, you might like to take a look at StudentLog.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file you'll find correlated combo boxes used extensively in the forms/subforms.  An example is the combo box to register a student for a course in the registrations subform in the students form.  The RowSource property of the combo box is:

    PARAMETERS [Forms]![frmStudents]![cboAcademicYear] TEXT,
    [Forms]![frmStudents]![StudentID] LONG;
    SELECT Courses.CourseID, Courses.CourseName
    FROM Courses INNER JOIN StudentYears
    ON Courses.AcademicYear = StudentYears.AcademicYear
    WHERE Courses.AcademicYear=[Forms]![frmStudents]![cboAcademicYear]
    AND StudentYears.StudentID=[Forms]![frmStudents]![StudentID]
    ORDER BY Courses.CourseName;

    This restricts the available courses to those offered in the academic year for which the student is being registered to courses.  In the parent form cboAcademicYear is an unbound control whose list is restricted by a parameter to those academic years for which a student is enrolled, and StudentID is the primary key of the parents form's recordset, which is a simple query on the Students table, returning the rows in LastName.FirstName order.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, August 9, 2017 11:33 PM
    • Marked as answer by Gordon Swan Sunday, August 13, 2017 10:16 PM
    Wednesday, August 9, 2017 11:28 PM