Form/Subform

[Confused]

Tables:
Customers
Systems
Contacts

Relationship between Customers and Contacts, Customers and Systems, Systems
and Contacts.

Form/subform with Systems and Contacts-I want users to add contact for the
existing customers on our systems.

I then want to add a new record to systems. When I try to add new record I
populate the customer ID at the top and the subform automatically changes to
that record, which is what I expected. However, it tells me you cannot add
or change a record becuase a related record is required in Contacts. But
there is a related record! Where did I go wrong?

 

[Richard]

One place you may want to look is the subforms properties make sure they
match the parent form i.e. allows edits, allows additons and deletions. Also
I might do a compact and repair and look for a lock file that is left open
after closing your database, if there is one delete it and restart.

HTH
Richard

 

[KARL DEWEY]

When I try to add new record I populate the customer ID at the top and the
subform automatically changes to that record,
If your three tables have one-to-many relationship with Referential
Integerity and Cascade Update there should be no need to input customer ID to
add a new record in a subform.
Am I assuming correctly that Customers have many Systems and the Systems
have many Contacts?
If so you should have Customers/Systems as form/subform and then
Systems/Contacts as form/subform also.

 

[Confused]

I've got the referential integrity and cascade.

When I'm adding a new record to systems, I have to type the customer ID to
pull up the company to add the systems to, right???? I have Customer Systems
Inventory as the top form and contacts as the subform. I just want to add
existing customers to new systems...and then add the contact we talked to on
the subform. But it won't update. Is there a better/easier way?

 

[KARL DEWEY]

In what order are the three table related? Which is the top, middle, and
bottom?

 

[Confused]

By you asking the question, I think I'm starting to figure this out...

I had them in the wrong order. I've changed contacts to be the main form
and Customer Systems to be a subform. This works but I still have to retype
the customer name etc, when I add a system to the subform. (Most of the
contacts don't have systems, so I'm adding them as needed) Maybe this is
necessary? I thought the relationship would keep everything in sync. On
Northwind this doesn't seem necessary-on their subform they don't even list
the company names etc on the bottom, yet it all seems to populate to the
correct tables.

I kept the form I used as customer systems and put the subform as contacts.
I thought this would be a way for users to view what was existing and not
make any changes. There's probably a better way to do this?

Thanks for all your advice!

 

[KARL DEWEY]

I would have thought that Customers had Systems and those in turn had Contacts.

So you would these table related one-to-many --- Customers - Systems -
Contacts.

Your top level form would scroll through Customers to find the correct one.
Your subform would list their Systems. You may have a table of the types of
systems maintained - just list without who does what. The subform use a
combo to pick system type but then fill in other details such as location,
etc.

Access will go one more layer deep to a second subform for contacts for that
system.

 

[Confused]

Just need you to clarify one thing if possible, please. I have Customers
Table and Customer System Inventory Table, with one to many, cascade
additions, etc. I'm trying to add new systems to existing customers, so how
would I do this on the form to where I can select a customer as in a combo
box from the customers table, but have it populate to the Customer System
Inventory Table? Say it's a new customer that isn't on the system inventory
table yet, but it is on the Customer's table? What would be the form's
record source? That's what I can't figure out, like in Northwind they select
customers from the customer's table but it populates to the order's table.

 

[KARL DEWEY]

I'm trying to add new systems to existing customers
Customers and Customer System Inventory will be in a form/subform. Click in
Customer field and then FIND button to locate customer record OR use the
navagation buttons at the bottom of form. The subform will display all
system loaded for that customer.
Use the navagation button of the subform to add new record.
The subform will be bound to the Systems table. The subform can use a combo
to pick system type if you have built a table of system types.