Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing a Record (XP2002)

    I have encountered an error while trying to change records in a field. The message being returned is "You cannot add or change a record because a related record is required in the table "BUnit". How can make the necessary changes.

    I have two tables: Data and BUnit. The BUnit table is a list of business units and their descriptions. The Data table consists of approximately 13,000 records for each business unit. I would like to rename or change a business unit number but encounter the error message when I try to use find/replace.

    Any suggestions would be appreciated. MSHelp suggests to delete each record in the Data table. Is there a better way?

    Thanks,
    John

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing a Record (XP2002)

    After looking for options, I decided the best thing to do was delete the records.

    Step1. I extracted the data from the Data table via a query to an Excel file, changed the business unit there and saved the XL file.
    Step2. Delete the records generated from the query.
    Step3. Change the business number in the BUnit table
    Step4. Import the modified records from the XL file into the Data table.


    This was not as bad as I thought it would be nor did it take very long.

    John
    PS-If someone still has a different way of doing it, please post a response.

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

    Re: Changing a Record (XP2002)

    It shouldn't be necessary to involve Excel in this. If an intermediary table is necessary at all, you might as well use an Access table.

    You don't make it clear what you want to change where, but perhaps setting Cascading Updates and/or Cascading Deletes for the relationship between the BUnit and Data tables would take care of everything.

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing a Record (XP2002)

    Hans,

    The Data table contains data about various business units including the business unit number itself. Think of the Data table field headings as: auto-number BUnit, Field_1, Field_2 and amount. The BUnit table only contains the business unit number and description. I'm not familiar with Cascading Updates and/or Cascading Deletes, I'll have to read up on it.

    Thanks for the reply,
    John

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

    Re: Changing a Record (XP2002)

    I don't understand why BUnit is an autonumber field in the Data table. I would expect it to be an autonumber field in the BUnit table. Or perhaps, I don't understand your setup.

    To view, create and modify relationships, click the Relationships button on the toolbar (the database window must have focus for this), or select Tools | Relationships. You can add tables to this window, and create relationships between them by dragging a field from one table to the corresponding field in the other table. A dialog will appear with three check boxes:
    - Enforce Relational Integrity: Access will make sure that you can't create records with invalid link fields on the "many" side of the relationship.
    - Cascading updates: if you modify the link field on the "one" side of the relationship, the link field in related records on the "many" side will be updated too.
    - Cascading deletes: if you delete a record on the "one" side, related records on the "many" side will be deleted too.

Posting Permissions

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