Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Unanswered: One-To-One Relationship and Sub Forms

    For simplicity sake I have 2 tables - they hava a one-to-one relationship. I have created a form for the second table and it is a subform on the first tables form. All OK so far.

    I enter the primary key information in the main form and tab down to the sub form and enter a record of data - This works OK both tables get updated. However ACCESS allows the user to enter a second record in the subform - it correctly says at the commit stage that it cannot be added because of key integrity, but that is after all the data entry has been made to the second record - Is there any way of only allowing the user to enter one record.

    Have tried setting add record to no but this prevents a new record being added to the second table when a new record is added to table one.

    All help appreciated - I am aware of db normalisation but the long and short is that I need a one-to-one relationship as the number of field will exceed 255).


  2. #2
    Join Date
    Feb 2004
    This may or may not work for you.

    If the subform is not a datasheet then you can set some properties on the subform
    On the Form properties sheet for the subform set the following

    Format Tab
    Default View = Single Form
    Views Allowed = Form
    Scroll Bars = Neither
    Navigation Buttons = No

    Other Tab
    Cycle = Current Record

    This will keep most of the users from creating another record.

    Then use code in the before update event to block updates if there is a record allready - just in case.

  3. #3
    Join Date
    Mar 2004

    AZ KC your a star

    Thanks for that - 4 hours of headache and you sort in 15 mins - thanks I am now off to bed !!!!

Posting Permissions

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