Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Unanswered: Form with Subform - nested Tab controls

    Form "frmMain_0" uses tblEmployee as recordsource with fields from tblEmployee. The subform (tblTabsHR_test) also uses tblEmployee as r/s. It contains a tab control with 1 field from tblEmployee. As you can see this has been dramatically simplied to isolate the problem. (See frmMain for original form with nested Tab controls.)

    When I add a record from the main form (bypassing subform), a record is added successfully to tblEmployee. If fill out both main and subform fields, I get: "The changes you requested on the table were not successful because they would create duplicate values in the index, primary key, or relationship..." even though the referenced fields are from the same table/record.

    It apears the subform, doesn't know a new record has been created in the main form. If I leave new record after completing the main form and then return, I can fill out the subform and it saves the data to the new record. Just can't fill them out simultaneously.

    The master/child links on the subform appear correct. Interestingly, I added the linked field (Login) to the subform. As I add a new user on the main form the Login populates in the subform field, but the error still occurs. If I change the idexing to allow duplicates, it adds to records to the table. One with an empty Login.

    Thanks.
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Remove the Recordsource from the main form and instead have an unbound "search" combobox on the form (call the field: FData). This combobox contains the record fields you want to search on (usually the IDField is the 1st column). ie. Set it to show all the ID values already entered into the system. You can use multiple columns to display the other columns such as Name, City, etc...but the 1st column is IDField (you can make the width = 0) is the value returned for FData.

    1. In the subform's recordsource query put in criteria under the ID column such as: =Forms!MyMainFormName!FData.
    2. In the Afterupdate event of the FData field on the main form, put in this code: me.mysubformname.requery

    That's it.

    To add records, you'll do this on the subform. The main form acts like a "shell" whereby you have your search fields. Create an "Add Record" button on the main form with this code behind the onClick event:
    me.mysubform.setfocus
    docmd.gotorecord,,acnewrecord
    me.MyFirstField.setfocus

    The reason you're getting an error is because you have 2 forms (ie. main form and subform) both based on the same table (recordset) and both forms are open at the same time. This is frowned upon and causes problems although you may get away with a Refresh command on the main form after you enter your value. Ideally though, you never want 2 open forms based on the same table.
    Last edited by pkstormy; 03-30-09 at 23:52.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2009
    Posts
    5
    Created an additional related table for the fields in the tabbed subform and it works. Thanks again.

Posting Permissions

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