Multiple values for a field in a form

[HansBades]

I have a continuous form. One field of the record can have multiple values. I want to avoid having a separate rows to enter in the multiple values for that field. I would prefer the user could simply check or select the multiple values from that field in the row to represent that many to many relationship. I want the table to update accordingly which of course would have multiple records with the same fields except for that one field where it would list one record for each value chosen for that field. I don't want to use the multivalue field. I hear that is controversial and won't migrate to sql server. Not sure if that is true but I don't want to use it for other reasons as well. What can I do to avoid having to create multiple rows for essentially the same values in the record except for one field? Thank you.

[ssanfu]

I have a continuous form. One field of the record can have multiple values. I don't want to use the multivalue field.

Whew.... You shouldn't use MVF's. That breaks the Normalization Rules.

I hear that is controversial and won't migrate to sql server. Not sure if that is true but I don't want to use it for other reasons as well.

This is true. MFV's are a construct of M$ and do not transfer/convert to other types of databases easily.

What can I do to avoid having to create multiple rows for essentially the same values in the record except for one field?

You haven't told us enough about your project/design.


One way would be to use another (related) table to store the values. For example, if you had a contacts table and you wanted to store ALL of the Contacts phone numbers. I would have a design like this:
(just an example)

The field "PhoneNumberType" would have options:
Home (Land Line),
Cell,
Fax,
Office

These options would/should be in a separate table (look up TABLE) to make it easy to add/delete options.

This is how the tables would be designed/related. Then comes the issue of how to enter data. Normal would be a Main form/Sub form design.
But it sounds like you have the form in continuous form view.

Maybe you would expound more on what your vision of the dB is? And/Or post a copy of your dB?

[ranman256]

use a sub-table (child) that can hold many records for the master record.

[mike60smart]

DO NOT try to put multiple values in a field.

EACH value should be a record in a separate table