Form Based on Query

  • Thread starter Thread starter Confused
  • Start date Start date
C

Confused

I have four tables. Customers to Contacts related by Customer ID. Customer
System Inventory related to Customers on Customer ID and a Testing Table I
want to use to keep track of customers testing from the System Inventory
Table.

I have a fom called Testing that I want to be able to select customers from
the Customer System Inventory. And then type contact information and have it
go back to the contact table. I've tried it several ways and the contact
name goes on the Contacts table, but it doesn't relate to any customer on the
customer's table. (It doesn't populate the customer ID).

How should I query these together so that the information uploads correctly.
What fields should I use to link these together? There are no records yet
on Contacts or Testing table. I really want to do this without subforms.

Any help is greatly appreciated.
 
On Tue, 26 May 2009 17:56:01 -0700, Confused

Especially as a beginner you don't want to tie one hand on your back
and try to make it work. "I don't want to use subforms" falls in that
category. The subform is THE easiest way to implement a form for data
in a one-to-many relationship.
As a test, create a new form using the wizard, and base it on
tblCustomerSystemInventory (do yourself a favor and drop the spaces
and other funny characters in object names). Take all the defaults and
create a form that is showing one record at a time. Then design that
form and drop the Contacts table on it. Answer the wizard's question
about how the data is related via CustomerID. Bingo, you have the
beginnings of a master/detail form without writing a single line of
code.

-Tom.
Microsoft Access MVP
 
On a query between Contacts and Testing Tables.

I got the below to work where I select Customers from the Customer System
Inventory based on Customer ID. The Customer ID field came from the Contacts
TAble on the query. I look at the Contacts table and it relates to the
correct Customer ID. The problem now is that I cannot type anything in the
Testing Fields on the form from the part of the query that is the testing
table.

What am I doing wrong!
 
multi-table queries are frequently not updateable. suggest you open the
query directly and try to edit a record - if you can't, there's your
problem. in which case, i'd suggest heeding MVP Tom van Stiphout's advice
elsewhere in this thread.

hth
 
Back
Top