If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Form/Subform
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? |
#2
|
|||
|
|||
Form/Subform
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 "Confused" wrote: 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? |
#3
|
|||
|
|||
Form/Subform
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" wrote: 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? |
#4
|
|||
|
|||
Form/Subform
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" wrote: 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" wrote: 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? |
#5
|
|||
|
|||
Form/Subform
In what order are the three table related? Which is the top, middle, and
bottom? "Confused" wrote: 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" wrote: 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" wrote: 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? |
#6
|
|||
|
|||
Form/Subform
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" wrote: In what order are the three table related? Which is the top, middle, and bottom? "Confused" wrote: 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" wrote: 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" wrote: 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? |
#7
|
|||
|
|||
Form/Subform
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" wrote: 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" wrote: In what order are the three table related? Which is the top, middle, and bottom? "Confused" wrote: 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" wrote: 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" wrote: 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? |
#8
|
|||
|
|||
Form/Subform
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" wrote: 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" wrote: 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" wrote: In what order are the three table related? Which is the top, middle, and bottom? "Confused" wrote: 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" wrote: 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" wrote: 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? |
#9
|
|||
|
|||
Form/Subform
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. Say it's a new customer that isn't on the system inventory table yet, Use the navagation button of the subform to add new record. What would be the form's record source? 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. |
Thread Tools | |
Display Modes | |
|
|