How to Edit Records when some fields are "Many to 1" relationship

[Palto Fondberg]

I have a table, tblCustomerContacts, containing unique customerID's
Another table is tblContactNamesAtCustomer, which could contain lots of
contacts for one customer.

In other words, a customer contact [eg: Microsoft plc] with unique customer
ID [eg: 666] could have many contacts [eg: Bill Gates, John Brown, Peter
Smith]

tblContactNamesAtcustomer is in the format:

Autonumber CustomerID ContactFirstName ContactSurname
1 666 Bill
Gates
2 666 John Brown
3 666 Peter Smith


I want to set up the ability to edit all customer details. This works fine
so far for all fields in tblCustomerContacts - all I've done is created a
form with unlocked fields
directly linked to tblCustomerContacts, giving the user to edit the address,
telephone number etc etc. But I want to be able to edit the contact names
too.

My problem is how to display ALL of the possible existing
tblContactNamesAtCustomer records AT ONCE [say in a list box which is
editable] in this same form - I can get the first 'Bill Gates' name to
appear, but for Customer ID 666 you need to cycle through all the
tblContactNamesAtCustomer records ie: [autonumber 1,2 and 3], to get 'Bill
Gates' , 'John Brown' and 'Peter Smith' all appearing in turn.

can anyone help please?

 

[Allen Browne]

Use a form with a subform.

The main form will be bound to tblCustomerContacts.
The subform will be bound to tblContactNamesAtCustomer.
Show the subform in Continuous View or Datasheet View.

You now choose any customer in the main form, and the subform displays the
contacts, one per row.

 

[Palto Fondberg]

Thanks Allen

Yes, I had used the subform methos already - but for some reason my brain
wasn't working!! - I didn't think of using Continuous View or Datasheet
View, doh!!!

thank you

Use a form with a subform.

The main form will be bound to tblCustomerContacts.
The subform will be bound to tblContactNamesAtCustomer.
Show the subform in Continuous View or Datasheet View.

You now choose any customer in the main form, and the subform displays the
contacts, one per row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table, tblCustomerContacts, containing unique customerID's
Another table is tblContactNamesAtCustomer, which could contain lots of
contacts for one customer.

In other words, a customer contact [eg: Microsoft plc] with unique
customer ID [eg: 666] could have many contacts [eg: Bill Gates, John
Brown, Peter Smith]

tblContactNamesAtcustomer is in the format:

Autonumber CustomerID ContactFirstName ContactSurname
1 666 Bill Gates
2 666 John Brown
3 666 Peter Smith


I want to set up the ability to edit all customer details. This works
fine so far for all fields in tblCustomerContacts - all I've done is
created a form with unlocked fields
directly linked to tblCustomerContacts, giving the user to edit the
address, telephone number etc etc. But I want to be able to edit the
contact names too.

My problem is how to display ALL of the possible existing
tblContactNamesAtCustomer records AT ONCE [say in a list box which is
editable] in this same form - I can get the first 'Bill Gates' name to
appear, but for Customer ID 666 you need to cycle through all the
tblContactNamesAtCustomer records ie: [autonumber 1,2 and 3], to get
'Bill Gates' , 'John Brown' and 'Peter Smith' all appearing in turn.

can anyone help please?