Need help where form will require two values to show.

[mpaulbattle]

On our current form we have our:

Customers | Joined Date | Trainers (Free text)
ABC | mm/yyyy | Jane, Doe

I am looking for a way to have multiple values (if needed) to be listed in the Trainers by normalizing so the users can't abbreviate, misspell or add something crazy in there.

I created a table for trainers
tblTrainers:
TrainerID - AutoNumber


TrainerName - Short Text

I was thinking of making it multi selection, but I am reading that is not the preferred method.

Any suggestions?

[CJ_London]

change your free text trainers field to a long called TrainerFK or similar, that will link to your trainers table

then in your form, change the textbox to a combobox using the trainers table as it's rowsource (hide the column with the PK)

if you are saying you have a many to many relationship between customers and trainers, you need a join table instead

tblCustomers
customerPK
customerName
...
...

tblTrainers
TrainerPK
TrainerName
...
...

tblCustomerTrainers
CustomerTrainerPK
customerFK
trainerFK

and on the customer form you would have a subform based on the customertrainers table - with a combo based on the trainers table to select trainers

or on the trainers form you would have a subform based on the customertrainers table - with a combo based on the customers table to select customers