Loop through control

[OlszanskiA]

I have a form with 6 check boxes. The user can select any number of check
boxes, each which refer to a specific phrase, and combines the phrases
together into one scentence. I have this working but want it to be more
functional with better code.

I have:
If Me.Check1 = -1 then
Phrase1 = "Strong interpersonal skills"
End
Etc. for each Checkbox

Each checkbox is refered to by seqential numbers (Me.Check1, Me.Check2, etc.)

Question: Is there a way to combine this into one statement such as a Select
Case within a loop?
For example:
For num = 0 to 6
Select Case Me.Check(num)
Case 1
Phrase1 = "strong interpersonal skills"
Etc...
End Select

When I refer to Me.Check(num) it is not recognized. Any ideas?

Despite my searching through help, Balters book, and these forums, I've come
up empty handed!

Thank you!!

 

[Allen Browne]

You could put the messages into an array, and loop through the controls like
this:
Dim strControl As String
Dim i As Integer
Dim strOut as String
Dim strPhrase(1 To 6) As String

strPhrase(1) = "strong personal skills"
strPhrase(2) = "complete upstart"

For i = 1 to 6
strControl = "Check" & i
If Me(strControl).Value Then
strOut = strOut & strPhrase(i)
End If
Next

In the long-term, it would be simpler to create a table with 6 records (the
numbers and the text), so that more items can be added and the text of the
comments can be edited. Remove the check boxes from your existing table.
Instead use a junction table to record the comments

Student table:
StudentID AutoNumber
Surname
FirstName
...

Comment table:
CommentID Autonumber
Comment the text, such as "strong personal skills"

StudentComment table:
StudentID relates to Student.StudentID
CommentID relates to Comment.CommentID

Now you can create a main form bound to the Student table, with a subform
bound to the StudentComment table. In the subform, you have a combo showing
the choices, and you create as many rows as apply to that student.

You can now do all these things without any code at all:
- Create a report based on the 3 tables (comments for students).
- Change the wording of the comments.
- Add more comments.

 

[OlszanskiA]

Wow! Thank you! After 3 + days of trying, I was getting frustrated. Your help
is greatly appreciated!
Thanks