Add multiple checkbox names into single table field

[thawstone]

I’m constructing a form with checkboxes next to names. I’d like to have the checked names input serially into a single table field like:

IssuedTo
Name1, Name2, Name3

There are 8 possible Names. Is there way to do this without a long complicated If-Else-Then function?

 

[Cronk]

That would be very bad practice to store multiple values in the one field. For a start, it makes it difficult to determine which records say Name3 is included on. You'd have to write code to append the names for the ticked check boxes into the table and then code to deconstruct the field containing the names when the record was being displayed in the form.

A better way is to have 8 Yes/No fields in your table. But this has pitfalls if the number of users increases. You would have to add additional fields for each new user as well as check boxes on your form. Also if there are changes in Personnel.

The best solution is to have 3 tables, one the original that holds the records for whatever the people worked on, a people table, and a junction table which would contain up to 8 (or as many as you'd like) records for each object ID and a different PersonID for each person who worked on the object.

Search this site for junction tables. It's a common topic.

 

[thawstone]

Cronk, here's what I was looking for:

Dim Value As String
Value = "Account Mgr, "

If (IsNull(Me.IssuedTo)) Then
Me.IssuedTo = Value
Else: Me.IssuedTo = Me.IssuedTo + Value
End If

I just cut and pasted for each of 8 Click events.
If I need to retrieve the individual names, I'll use the Like function

 

[Pat Hartman]

Cronk's advice is correct. You are making work for yourself by violating standard normalization practice. First Normal form says all columns should be atomic. That means that they should contain one and only one value.

PS - While the LIKE predicate is very handy, it is also very expensive since it usually prevents the database engine from using an index to find records. If the engine cannot use an index, it must do a full table scan. That means it has to read each and every row in the table to find out if the mushed field contains *John*.

 

[Cronk]

And if the OP clears the check box, what happens then? And what happens if it's re-ticked?

As I said, bad practice.

 

[thawstone]

You're right, of course. I've never come across the need for a junction table before but I'll give it a shot. Looks interesting.

Thanks