Solved

Dynamic checkboxes in access data form

Posted on 2007-03-19
16
373 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 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 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

776 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