?
Solved

Dynamic checkboxes in access data form

Posted on 2007-03-19
16
Medium Priority
?
394 Views
Last Modified: 2013-12-20
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


****************************************
0
Comment
Question by:x3o
  • 8
  • 8
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18751580
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
 

Author Comment

by:x3o
ID: 18751605
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18751638
post some data from field MONTHS

are they like this?

jan|feb|mar|
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:x3o
ID: 18751651
MONTHS
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec|
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18751783
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
 

Author Comment

by:x3o
ID: 18751914
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
 

Author Comment

by:x3o
ID: 18751981
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18752057

change this

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

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

Author Comment

by:x3o
ID: 18752130
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18752169
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18752225
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 18752236

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
 

Author Comment

by:x3o
ID: 18752247
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
 

Author Comment

by:x3o
ID: 18752260
AMAZING!!!!!

You just saved my yet another all nighter!!!

THanks so much...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18752274
what  {ELSE }  statement ?
0
 

Author Comment

by:x3o
ID: 18752282
Forget it...you got it with the last "oops"...all is well...
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question