Relationships and Form Query Problem

[Confused]

I mainly have four tables. Customers, Conacts, Customer System Inventory,
and Testing.

I built a query on my new Testing Table with Contacts (Customers that are on
the Customer System Inventory list can test) . I based a form on this query
and when I select the customer from a combo box I created by Select Distinct
Name etc. I then get #Name? in those fields from the Contacts Table.

My relationships are Customers to Contacts based on Customer ID. Customers
to Customer System Inventory based on Customer ID. Contacts to Testing
based on Contact ID. No records are in Contacts or Testing tables yet.

I know it is trying to pull the Contact ID, but I don't know how to make
this work.
Please...How should I do this?

 

[Jeanette Cunningham]

The user would first select a customer from either a drop down or a previous
form.
Now show the user the contacts for the selected customer.
Let user select which contact for the test.

You could have a main form for the contact selected and a subform for the
tests for that contact. Perhaps a combo to choose the contact.

It's also possible to have cascading combos where the user selects a
customer, then the next combo shows only contacts for that customer.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Confused]

There are no records yet in contacts to select. I'm just trying to query the
tables together, so that a user can 1) select the Company Name from the
Select Distinct From CustomerSystemsInventory, 2) They could then type the
Contact Name they are working with in the contact fields from the query etc.
But even though the main Customers form is related to the Contacts Form,
the contacts field do not seem to recognize the customer ID.

Can I not do this based on this query? I thought a query was to pull
different fields together from different tables?

 

[Confused]

The way I have it now, when I select a Customer from the drop down box it
puts the name on the form with the customer ID. But when I try to save or
change records I get the error-The Microsoft Jet Database could not find a
related field in the table field contacts with key matching field Contact ID.

 

[John W. Vinson]

The way I have it now, when I select a Customer from the drop down box it
puts the name on the form with the customer ID. But when I try to save or
change records I get the error-The Microsoft Jet Database could not find a
related field in the table field contacts with key matching field Contact ID.

Well? Is there a related field in the table field contacts with matching field
Contact ID?

Please post the names and relevant fields of your tables, and indicate the
Recordsources of your forms and the Master/Child Linking Field properties of
your subform.

 

[Confused]

I'm not using a subform. I have a main Customers Table with customer ID that
relates to a Contacts table. (This worked fine for a form/subform
relationship). I then have a new Testing Table which I just created and
added a field Customer ID and related it back to the main customes table. I
did the same thing with Customer Inventory Table joining it to the Customers
table based on the Customer ID.

The form's record source is TestingwContacts Query. What I'm wanting to
accomplish is just have fields on the Testing table on the form, but also
have the fields from the contacts Table.

On the form I created a combo that selects Distinct Customer ID from the
System INventory TAble (Only customers that have systems can Test.) It then
pulls down the Customer name and Customer ID, but the Contact part doesn't
know which customer this is. I thought it would since there is a
relationship between customers and Contacts based on Customer ID. And
Customer System Inventory relates to Customers.

In summary how should I set up the testing form so the fields are in sync.
Thank you John! I was hoping you would reply...