Problem with subform?

[JK]

I have designed a database, that has Company Table, Customer Contact Table,
and what i am trying to do is have the Company table as the main table, and
the contact table whenever someone from that company contacts us. The
problem i am having is that I have got as far as designing the forms, and
followed some examples about autofilling the Company details so that i dont
need to type in company details again incase of error etc. What is happening,
I want to be able to see in subform format at the bottom of the screen, the
records where that company has made contact with us previously. For some
reason, this works when i create a test database, but when i go to use it in
theory in my company database it is not working. I am assuming there is a
problem with my relationships but I have no idea what is wrong? I cant see
anything wrong? I am only a reasonably new user, i have attended two courses,
but I dont know anything about code etc. Please help in basic terms??

 

[John Vinson]

I have designed a database, that has Company Table, Customer Contact Table,
and what i am trying to do is have the Company table as the main table, and
the contact table whenever someone from that company contacts us. The
problem i am having is that I have got as far as designing the forms, and
followed some examples about autofilling the Company details so that i dont
need to type in company details again incase of error etc. What is happening,
I want to be able to see in subform format at the bottom of the screen, the
records where that company has made contact with us previously. For some
reason, this works when i create a test database, but when i go to use it in
theory in my company database it is not working. I am assuming there is a
problem with my relationships but I have no idea what is wrong? I cant see
anything wrong? I am only a reasonably new user, i have attended two courses,
but I dont know anything about code etc. Please help in basic terms??

Without knowing how you have set up your database, it's all but
impossible to guess what might be wrong. Just a few ideas to
investigate:

- The Forms *are secondary*. You need to be sure that your Tables are
set up correctly first, or you'll never get it to work! In this case
you'll need a Company table with information about the company itself,
with a unique Company ID as its Primary Key; this might be an
autonumber field or some other field which will be unique (no two
companies can have the same ID), stable (i.e. a company name is NOT a
good idea since companies can change their name), and short. The
Contacts table should not contain ANY company information other than
the CompanyID, a foreign key of the same datatype as the Company
table's primary key (use Long Integer if the CompanyID is an
Autonumber). You should use the Relationships window to define a one
to many relationship from Companies to Contacts, checking the Enforce
Relational Integrity checkbox.

- Once you have the tables, base a Form on Companies and the Subform
on Contacts. The Master/Child Link Field should be the CompanyID.

- You should NOT need to "autofill" the company details; instead, you
want to be able to find the existing record for a company and display
it. A simple way to do this is to use the Combo Box Wizard on the
mainform to create a combo box, using the option "find an existing
record".

If you're still having trouble, please post the following information:

- The name and Primary Key of each table
- The relationship type from the Relationships window
- the Recordsource property of the mainform and the subform
- the Master and Child Link Fields
- the specific nature of the problem you're seeing

John W. Vinson[MVP]

 

[Duane Hookom]

This isn't very helpful "it is not working"...
What are the record sources of the main form and subform and how are these
related? Do you have any values in the Link Master/Child properties?

 

[JK]

Thank you for your help.

There are two tables, Company - PK CompanyID & Contacts - PK ContactID with
CompanyID as foreign key.

The relationships type is 2:Include all records from tbl company & only
those from tbl contact where joined fields are equal.

The row source for Company ID in the form is Company Table

Please explain how to set up the parent child relationship, as I have never
done this before and I didnt learn about this in my courses that i did.

The problem is that on the main form when i use the combo box it selects the
company but the other details in the contacts form do not come up in the
subform.

 

[Duane Hookom]

You would normally set the record source of your main form to the Company
table. Then add a subform to the main form. The subform would have a record
source based on the Contacts table. You would set the Link Master and Link
Child properties of the subform control to CompanyID. This would default the
records in the subform to contain the same CompanyID as the CompanyID value
in the main form.

You might want to review the Orders and Orderdetails form and subform in
Northwind.mdb.