Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

RE: Relationship Problem With Updating Related Fields

Discussion in 'Information Technology' started by Ken Sheridan, Jul 28, 2006.

  1. Ken Sheridan

    Ken Sheridan
    Expand Collapse


    You won't be able to use a cascade update referential operation here. In
    fact you can't enforce referential integrity between Companies and Contacts
    at all as you are deleting rows from the former when they become customers.
    To allow the enforcement of referential integrity you'd first have to delete
    all matching rows from Contacts before you could delete the row from

    You can however achieve much the same ends by updating Contacts before you
    delete the row from Companies. I assume this is done from a Companies form
    in which the account number is entered and code or a macro behind the form
    execute the delete and append queries. So what you need to do is execute an
    UPDATE query before these which updates the account number column in Contacts
    for all rows which match the current companyID. In code it would go like

    Dim cmd As ADODB.Command
    Dim strSQL As String

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText

    strSQL = "UPDATE tblContacts & _
    " SET acctnumber = " & Me.acctnumber & _
    " WHERE CompanyID = " & Me.pkCompanyID

    cmd.CommandText = strSQL

    I've called the foreign key in Contacts CompanyID so you'd have to change
    this if you use a tag such as 'fk' as a prefix to the column name. I've
    assumed that acctnumber and the companyID primary and foreign keys are all
    number data types rather than text. If any are text you'd need to amend the
    code which builds the SQL statement so that the values are wrapped in quotes.

    Ken Sheridan
    Stafford, England

    "BDP III" wrote:

    > Ken,
    > Thanks for your comments, however NO all Customers might not be in the
    > Company table. The company table consist of contacts from trade shows and
    > other data sources it would be more a "sales leads" table really. We will be
    > adding our current customers in the customers table with company information
    > held in that table only.
    > i would have had a seperate database for our "leads" but my supervisors
    > wants all the data in one database so our sales staff does not have to open
    > and close different databases while makeing / taking calls.
    > I am still haveing trouble just creating a realtionship with update related
    > fields integraty in place. any idea's?
    > Barry
    > "Ken Sheridan" wrote:
    > > Are all customers companies? If so you have a classic Type/Sub-Type
    > > situation here in that the customers entity type is a sub-type of the
    > > companies entity type. Types/Sub-Types (sometimes referred to as
    > > Classes/Sub-Classes) are characterized by the latter sharing all the
    > > attributes of the former, i.e. the Companies entity type has attributes
    > > common to all companies regardless of whether the company is a customer or
    > > not. The customers sub-type has attributes only relevant to customers, e.g.
    > > a customer account number.
    > >
    > > Types/Sub-Types are modelled by tables in a one-to-one relationship with
    > > columns representing the appropriate attributes of each, so CompanyID would
    > > be the primary key of Companies, and would also be the primary key of
    > > Customers. In customers it would also be a foreign key referencing the
    > > primary key of Companies. It follows therefore that while it can be an
    > > autonumber column in the former, it cannot in the latter, where it is a
    > > straightforward long integer number data type.
    > >
    > > When a company becomes a customer a new row is inserted into Customers with
    > > a CompanyID value matching that in Companies. No deletion from Companies is
    > > necessary, or indeed permissible as it would offend referential integrity.
    > >
    > > Contacts would have a CompanyID foreign key column referencing CompanyID in
    > > Companies.
    > >
    > > There might well be other sub-types of Companies. The obvious candidate
    > > would be Suppliers which would have attributes relevant only to the Suppliers
    > > entity type, all other attributes common to all sub-types of Companies being
    > > in Companies.
    > >
    > > With this logical model companies are identified as customers by virtue of
    > > their having a matching row in Customers. A company could of course be both
    > > a customer and a supplier by having matching rows in both Customers and
    > > Suppliers.
    > >
    > > Ken Sheridan
    > > Stafford, England
    > >
    > > "BDP III" wrote:
    > >
    > > > I am having a problem with a realtionship. I have three tables which are
    > > > involved.
    > > >
    > > > 1) tblCompanies (non-customers)
    > > > 2) tblContacts
    > > > 3) tblCustomers
    > > >
    > > > the pk in tblcompanies is pkComapnyID with a one to many relationship to
    > > > tblContacts. If a "company" becomes a customer an accoutn number is filled
    > > > in field acctnumber, I am needing the realtionship to cascade update
    > > > tblcontacts when this account number is entered. When I change my
    > > > relationship to include the acctnumber field I get a message that reads
    > > > "no unique index for the referenced field of the primary table". However I
    > > > have the properties set to Yes(no Duplicates),Yes in both tables with the
    > > > same field size and data type.
    > > >
    > > > Once the record has an account number in tblCompanies it will moved (append
    > > > query followed by delete query) to tblCustomers. tblCustomers is linked to
    > > > tblContacts using the acctnumber field.
    > > >
    > > > If anyone can help with the updating related field issue it would be a big
    > > > help.
    > > >
    > > >

    > >
  2. Loading...

    Similar Threads Forum Date
    Relationship With Creator Blogs Oct 17, 2015
    I'm Open-minded About Romantic Relationships. How Do I Explain To Family? Love & Marriage Aug 22, 2015
    Arts/Society What Are Your Thoughts On The Interaction Of Male / Female Relationship Of Marriage? Language, Arts & Culture Oct 15, 2013
    Relationship Advice Love & Marriage Sep 30, 2012
    Need Advice. Sikhi In Relationships Love & Marriage Nov 17, 2011

Since you're here... we have a small favor to ask...     Become a Supporter      ::     Make a Contribution     

Share This Page