Relationships Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database to track customer complaints and issues. I have the
following set up so far as tables:
Customers table:
Customer ID (AutoNumber)
First Name, Last Name, Address, CityStZip, Phone, Email

Complaints table:
Customer ID (Number - Long Integer)
and then my complaint fields

I have a relationship set up between the two using Customer ID. It is
Customers to Complaints, I have Enforce Referential Integrity, Cascade Update
Related Fields and Cascade Delete Related Records checked.

My problem is that the record in the Complaints Table is not created until
you enter the first piece of information in the table.
Is there a way to have that particular record created as soon as you create
the record in the Customers table? There is a field in the Complaints table
(resolved) that I need to use as a filter, so that any complaints that have
been resolved won't come up in the form.
 
Hi Steve,
My problem is that the record in the Complaints Table is not
created until you enter the first piece of information in the table.
Click to expand...

Why is this a problem? A one-to-many relationship is suppose to accomodate zero to many related
records. What if you want to enter a customer into your database, but they're very satisfied with
your company. It doesn't seem to me that you'd want to automatically enter a complaint record
for such a customer.
Is there a way to have that particular record created as soon
as you create the record in the Customers table?
Click to expand...
Sure, this can be done, but I cannot imagine why you would want to do this. You cannot do this by
entering data directly into the table; you'll need to design a form with subform for this
purpose. With a proper form / subform arrangement, if you first enter a customer record in the
form, and then click into your subform, a new record will be created automatically if you dirty
the subform record (ie. enter anything).

There is a field in the Complaints table (resolved) that I need to use
as a filter, so that any complaints that have been resolved won't come
up in the form.
Click to expand...
The recordsource for the subform needs to be a query (not a table). The query should include a
criteria for the Resolved field. Assuming this field is a Yes/No datatype, the criteria would
be: Resolved: = 0 (false).


Tom
___________________________________


I am creating a database to track customer complaints and issues. I have the
following set up so far as tables:
Customers table:
Customer ID (AutoNumber)
First Name, Last Name, Address, CityStZip, Phone, Email

Complaints table:
Customer ID (Number - Long Integer)
and then my complaint fields

I have a relationship set up between the two using Customer ID. It is
Customers to Complaints, I have Enforce Referential Integrity, Cascade Update
Related Fields and Cascade Delete Related Records checked.

My problem is that the record in the Complaints Table is not created until
you enter the first piece of information in the table.
Is there a way to have that particular record created as soon as you create
the record in the Customers table? There is a field in the Complaints table
(resolved) that I need to use as a filter, so that any complaints that have
been resolved won't come up in the form.
 
The department at work that I am creating this for handles and deals with
customer complaints. So, that is why I am needing this.
 
Okay. Will the idea of simply creating a form with subform work for you? If not, please explain
why it won't work.

You can try this procedure in the parent form, although I really don't think it should be
necessary. This requires a reference set to the Microsoft DAO 3.6 Object Library (Access
2000/2002/2003). Use the 3.51 library for Access 97.

Tom
_______________________________

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()

Dim strSQL As String

strSQL = "INSERT INTO Orders ( [Customer ID] ) Values (" & Me.[Customer ID] & ")"

CurrentDb.Execute strSQL, dbFailOnError


End Sub

__________________________________


The department at work that I am creating this for handles and deals with
customer complaints. So, that is why I am needing this.
 
Correction:

strSQL should be:

strSQL = "INSERT INTO Complaints ( [Customer ID] ) Values (" & Me.[Customer ID] & ")"

not insert into Orders. I was using a table structure from the Northwind sample database for
some quick testing, but I neglected to rename the Orders table to Complaints.

Tom
__________________________________


Okay. Will the idea of simply creating a form with subform work for you? If not, please explain
why it won't work.

You can try this procedure in the parent form, although I really don't think it should be
necessary. This requires a reference set to the Microsoft DAO 3.6 Object Library (Access
2000/2002/2003). Use the 3.51 library for Access 97.

Tom
_______________________________

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()

Dim strSQL As String

strSQL = "INSERT INTO Orders ( [Customer ID] ) Values (" & Me.[Customer ID] & ")"

CurrentDb.Execute strSQL, dbFailOnError


End Sub

__________________________________


The department at work that I am creating this for handles and deals with
customer complaints. So, that is why I am needing this.
 
I have it set in a query to filter out any records that are marked
"resolved", but the problem is that if the record in the Complaints Table
hasn't been completed, when the record will not show up, since it doesn't
exist. The customer record will not even show up.
Since I am going to want the Complaint Record for EVERY customer, is it
possible to have it create the linked Complaint report automatically? I also
hope to set it up so that a customer record could be searched for, and if the
customer record exists, then simply create a new complaint from the customer
input screen.

Maybe it would help to explain it a little better how I have it set up right
now. I have form 1 -- Customer information. I then have form 2 -- Complaint
information which displays the customer information as well as the complaint
information and call records.
 
Hi Steve,

If I am understanding you correctly--and I don't know if I am--your query to filter out any
records that are marked "resolved" serves as the recordsource for the customer's form? The
resolved field is found in the Complaints table. In other words, you are trying to suppress the
customer record from being displayed in the customer's form, if Resolved = True. Is this
correct? The problem I see with this strategy is that it could very easily lead to several
duplicate entries in the customers table, each time they file a complaint. The reason is that a
person entering data would not see the existing customer record for this customer, if a past
complaint was marked as resolved.

If I have understood you correctly, remove the Resolved criteria from the query that serves as
the recordsource for the Customer's form (form 1).

You should be able to use the form wizard to create your desired linked form. Select the
appropriate fields from the Customer's table in the first screen of the wizard. Before clicking
the next button, select the Complaints table in the dropdown and then add the appropriate fields
to the list. When you click the next button, you should see a form with subform as a sample. At
this point, you can select an option to create Linked forms if you don't want the default form /
subform arrangement.


Tom
__________________________________


I have it set in a query to filter out any records that are marked
"resolved", but the problem is that if the record in the Complaints Table
hasn't been completed, when the record will not show up, since it doesn't
exist. The customer record will not even show up.
Since I am going to want the Complaint Record for EVERY customer, is it
possible to have it create the linked Complaint report automatically? I also
hope to set it up so that a customer record could be searched for, and if the
customer record exists, then simply create a new complaint from the customer
input screen.

Maybe it would help to explain it a little better how I have it set up right
now. I have form 1 -- Customer information. I then have form 2 -- Complaint
information which displays the customer information as well as the complaint
information and call records.
 
Steve said:
I am creating a database to track customer complaints and issues. I
have the following set up so far as tables:
Customers table:
Customer ID (AutoNumber)
First Name, Last Name, Address, CityStZip, Phone, Email

Complaints table:
Customer ID (Number - Long Integer)
and then my complaint fields

I have a relationship set up between the two using Customer ID. It is
Customers to Complaints, I have Enforce Referential Integrity,
Cascade Update Related Fields and Cascade Delete Related Records
checked.

My problem is that the record in the Complaints Table is not created
until you enter the first piece of information in the table.
Is there a way to have that particular record created as soon as you
create the record in the Customers table? There is a field in the
Complaints table (resolved) that I need to use as a filter, so that
any complaints that have been resolved won't come up in the form.
Click to expand...

It would seem to me that you would want to look for cases where there
are no open complaints rather than trying to close a case that does not
exist.
 
Back
Top