[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Dynamic checkboxes in access data form

Posted on 2007-03-19
16
Medium Priority
?
391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…
Suggested Courses

649 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