Dynamic checkboxes in access data form

I have a field in a table "MONTHS" which has values listed with a month's name abbreviated and separated by pipes "|" as follows:

monthname | monthname | monthname | etc...

I need to be able to edit the values in that field using check boxes, so that the user can select the months in which a certain task takes place.  

Example:  I need to clean the furnace in January, April, June and November.  
Therefore when I pull up the "furnace" record, the MONTHS table shows:
jan|apr|jun|nov

I want to be able to do the following:

1)  Have 12 checkboxes on the form (for each month) which are checked or unchecked according to the data stored in the MONTHS field of the TASKS table
2)  Have the ability to change the data stored in the MONTHS field of the TASKS table by checking or unchecking the text boxes

So far I have tried a number of things, but this is where I am so far...
************************************************
Private Sub Form_AfterUpdate()

If [me]![MONTHS] Like "jan" & "*" Then
Set [me]![JAN] = True
End If

End Sub


****************************************
x3oAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
can you post some data from tables TASKS and MONTHS (include field names)
can't visualize how your data in the tables look like.
0
x3oAuthor Commented:
Task Table Fields:  unique, PROP_NUM, ITEM_NUM, TASK, TASK2, MONTHS

Task, Task2 and MONTHS are all text boxes

MONTHS is just a field in the TASK table.  

Its bad database design, but I just inherited this project and don't know how to extract the months data into their own table without breaking the whole application.  I would be happy to do it but don't have a deep enough understanding to visualize before I undertake something like that...
0
Rey Obrero (Capricorn1)Commented:
post some data from field MONTHS

are they like this?

jan|feb|mar|
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

x3oAuthor Commented:
MONTHS
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec|
0
Rey Obrero (Capricorn1)Commented:
dim mArr, sMon as string,j, ctl as control
sMon=Left(Me.Months,len(me.months)-1)

mArr=split(smon,"|")

for j=0 to ubound(mArr)
     for each ctl in me.controls
        if ctl.controltype=acCheckbox then
            if me(ctl.name)= marr(j) then
               me(ctl.name).value=true
            end if
        end if
     next

next

0
x3oAuthor Commented:
Can you clarify a little for me?  How do I use this code?  Do I place it in the visual basic Event Procedure?

I assume I need to change the name of the check boxes to match the output of this?
0
x3oAuthor Commented:
I understand that if "NAME" equals marr(j) then the checkbox will be set to true...but it doesn't seem to change anything.
0
Rey Obrero (Capricorn1)Commented:

change this

  if me(ctl.name)= marr(j) then
 
 to

  if me(ctl.name).name= marr(j) then
0
x3oAuthor Commented:
FIrst of all - Thank you so much for taking the time to help me!  I have been struggling with this for a very long time and appreciate your efforts!

Ok.  I added the code to the OnLoad statement and it seems to work, but doesn't update or change as I cycle through the records.  Also, it doesn't "uncheck" when the item isn't listed...

Private Sub Form_Load()
Dim mArr, sMon As String, j, ctl As Control
sMon = Left(Me.MONTHS, Len(Me.MONTHS) - 1)

mArr = Split(sMon, "|")

For j = 0 To UBound(mArr)
     For Each ctl In Me.Controls
        If ctl.ControlType = acCheckBox Then
            If Me(ctl.NAME).NAME = mArr(j) Then
               Me(ctl.NAME).Value = True
            End If
        End If
     Next

Next
End Sub

1)  I know I need an "ELSE" statement, but when I put it in the editor is automatically adding a colon ":" after the word ELSE:

2)  To get the checkboxes to update dynamically as the month field changes, do I need to put this function in the OnChange for every individual checkbox?
0
Rey Obrero (Capricorn1)Commented:
use the Current event of the form

Private Sub Form_Current()
Dim mArr, sMon As String, j, ctl As Control
sMon = Left(Me.MONTHS, Len(Me.MONTHS) - 1)

mArr = Split(sMon, "|")

For j = 0 To UBound(mArr)
     For Each ctl In Me.Controls
        If ctl.ControlType = acCheckBox Then
            If Me(ctl.NAME).NAME = mArr(j) Then
               Me(ctl.NAME).Value = True
            End If
        End If
     Next

Next

End Sub
0
Rey Obrero (Capricorn1)Commented:
try this one


Private Sub Form_Current()

Dim sMon, ctl As Control
sMon = "jan|feb|mar"

    For Each ctl In Me.Controls
        If ctl.ControlType = acCheckBox Then
             If InStr(sMon, Me(ctl.Name).Name) > 0 Then
                Me(ctl.Name).Value = True
                Else
                Me(ctl.Name).Value = False
            End If
        End If
    Next
end sub

0
Rey Obrero (Capricorn1)Commented:

oops

Private Sub Form_Current()

Dim sMon, ctl As Control
sMon = Left(Me.MONTHS, Len(Me.MONTHS) - 1)

    For Each ctl In Me.Controls
        If ctl.ControlType = acCheckBox Then
             If InStr(sMon, Me(ctl.Name).Name) > 0 Then
                Me(ctl.Name).Value = True
                Else
                Me(ctl.Name).Value = False
            End If
        End If
    Next
end sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
x3oAuthor Commented:
Awesome!  It works...

Last Question:

Do I add the ELSE statement to the same function or to a different function?

If to the same, how do I call it?  It keeps adding a ":" colon after ELSE which throws an error and ELSEIF doesn't seem to work either...
0
x3oAuthor Commented:
AMAZING!!!!!

You just saved my yet another all nighter!!!

THanks so much...
0
Rey Obrero (Capricorn1)Commented:
what  {ELSE }  statement ?
0
x3oAuthor Commented:
Forget it...you got it with the last "oops"...all is well...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.