Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Form Problem (2003)

    I have a form (XrayMain) containing a subform (XrayDetail). A user has created a new record today, but when they went to edit the record in the main form, the record wasn't there. I looked in the tables, and although there was no record in the XrayMain table, there was in the XrayDetail table. The linking of these tables has always been ok. What could have gone wrong, and how can I correct it? I've compact and repair, with no joy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Problem (2003)

    Activate the Database window.
    Select Tools | Relationships...
    Double click the join line between XrayMain and XrayDetail.
    Is "Enforce Referential Integrity" ticked?
    If not, users can create orphan records in the XrayDetail table.
    You will have to delete the orphan records or give them a parent before you can enforce referential integrity.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Form Problem (2003)

    Thanks Hans. I deleted the problem orphan records, and the form seems ok, but it wouldn't let me select Enforce Referential Integrity.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Problem (2003)

    It is important to enforce referential integrity. If you don't, you'll keep on having problems.

    If you deleted all orphan records and still can't enforce referential integrity, there may be detail records with an invalid foreign key. You can use the Find Unmatched Query Wizard to create a query that returns all detail records that do not have a match in the main table; you can then repair or delete these records.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Form Problem (2003)

    Deleted all orphan records, and enforced referential integrity. When viewing or amending records in the form, if we create new record, the message 'you cannot add or change a record because a related record is required in table 'release note x-ray'. I have linked Xray Main Serial No (One) to Xray Details Serial No (Many).

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Problem (2003)

    You shouldn't create a new record in the subform while the main form is on a blank new record. Create a new record in the main form first, before creating a new record in the subform.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Form Problem (2003)

    The Serial Number on both forms is Autonumber. When a purchase order is entered on the main form, the serial number is created. They then click into the subform to enter the details. Thats when I'm getting the problem.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Problem (2003)

    The serial number in the details tables should *not* be an AutoNumber field, but a Number (Long Integer) field. It will be assigned a value automatically by the link between the main form and subform through the Link Master Fields and Link Child Fields properties.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Form Problem (2003)

    Thanks Hans. Sorted it.

Posting Permissions

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