Solved

Dynamic checkboxes in access data form

Posted on 2007-03-19
16
366 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

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

are they like this?

jan|feb|mar|
0
 

Author Comment

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

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 18752057

change this

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

  if me(ctl.name).name= marr(j) then
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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 119

Expert Comment

by:Rey Obrero
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now