multiple checkboxes to update table

[accessdummy]

hi, i've a form with 2 combo boxes (month and year) and 2 buttons "save" and "get". I'm trying to figure out how to use that button to perform 4 tasks.

task 1:

when i select the month and the year from the combo boxes and i click the button "get", the form should populate the number of checkboxes equavalent to the number of days of that month. e.g: if i select feb 2008, i should have 29 checkboxes (due to leap year) or i select apr 2005, i should have 30 checkboxes.

assuming that i've done the date validation function.. like jan, mar, may, jul, aug, oct and dec should return 31 days, leap year etc.

task 2:

then how should i assign the date value to the checkbox? say if i check on the 5 th box, it should return me a value of 05/09/2005 (assuming i select sept in my month combo box and 2005 in my year combo box earlier on)

task 3:

how should i code the label for my checkboxes? i wan to have 2 labels on top of the checkbox. the first label to show the name of the day (e.g: mon, tue etc) and the second label to show the day (e.g: 1, 2, 3)

task 4:

not sure if this task is a tall order.. but nonetheless, i need to consult the experts here. let's say when i check multiple checkboxes at one go, how to update into the table i want?

e,g: i check 9 boxes (e.g: 01/09/05, 03/09/2005, 04/09/2005... 23/09/2005) then when i click the button "save", i should have nine records in my table designated to save the data.

pls help. thanks.

 

[ScottGem]

Save what data? What's not clear is what, exactly you are trying to accomplish here. But I suspect you would be better off using a Multi-select listbox rather then checkboxes.

 

[accessdummy]

this is my date validation code for "get" button

Dim strDate As String
Dim RetDate As String 'act as buffer

strDate = "01/" & Me.ComboMonth & "/" & Me.ComboYear 'to capture the date

Select Case Month(strDate)
Case 1, 3, 5, 7, 8, 10, 12:
RetDate = 31
Case 2:
If Year(strDate) / 4 = Int(Year(strDate) / 4) Then
RetDate = 29
Else
RetDate = 28
End If
Case Else:
RetDate = 30
End Select

'not sure of the coding to populate the checkboxes but i think for loop is good. pls advise

Dim i As String

For i = 1 To i = RetDate
'what's the coding to populate checkboxes? how should i assign the date value to the checkbox?
' such that when i check on the checkbox, it should return me the date value. e.g: the 10th chkbox should return 10/combomonth/comboyear
Next

===================
As for my "save" button

it should perform 'multiple' add record function such that when 10 records are checked, i should have 10 records saved in my table.

 

[ScottGem]

This doesn't answer my question. All your code does is return the number of days in the month and that can be done more easily using:

=Day(DateSerial(Me!comboYear,Me!comboMonth+1,0))

 

[accessdummy]

Save what data? What's not clear is what, exactly you are trying to accomplish here. But I suspect you would be better off using a Multi-select listbox rather then checkboxes.

save the date data..
as mentioned earlier.. if assume i check on the 5th check box .. it should return a value of 05/combomonth/comboyear

and if i check 10 checkboxes, i should have 10 records in my tbl.

 

[accessdummy]

basically what i'm trying to achieve here is this..

i will have 3 combo boxes.. 1st one is job types (e.g: managers, clerks, carpenters, welders), 2nd one is 12 months, 3rd is the years (from 2005 to 2030 e.g)

I will also have 2 buttons - "get" and "save" buttons.

so what happen is.. when i select the job type, month, and the year and i press the "get" button.. the details section of the form will display all the people under that job type and the checkboxes equivalent to the number of days of that chosen month. (30 chkboxes in sept, 31 chxboxes in jan e.g)

This is to capture the data of the people who have personal commitment and are unable to work on the dates.

is there a simpler way to do things?

my table will include 2 fields with no unique id, namely "dates", "name" only.

 

[accessdummy]

presumably i'm planning to use continuous form in detail section..

 

[accessdummy]

oh ya, i'm using Access 97 as well.. sorry that i didn't inform earlier.

 

[ScottGem]

I just built a similar database for the manager of my part time job. The way I did it was with a main form bound to the people table and a subform bound to an unavailable table. The person is selected on the main form (it could be filtered for job type). In the subform a record is input for each unavailable date. Since the unavailable dates are not that many its no big deal to just enter the records.

If you really want to have the user select from all the dates during that month, I would do it with a Listbox.

After selecting the Month and year you would populate the listbox using code like this:

Dim strList As String
Dim intDays As Integer
Dim dteCurr As Date

intDays = Day(DateSerial(Me!comboYear,Me!comboMonth+1,0))
dteCurr = DateSerial(Me!comboYear,Me!comboMonth,1)
strList = ""

For i = 1 To intDays
strList = Format(dteCurr,"mm/dd/yy") & ";"
dteCurr = dteCurr + i
Next i

strList = Left(strList,Len(strList)-1

Me!lstDates.RowSource = strList

The list box should be set to Valuelist and to allow multiple selections.

After the user has selected the dates, you would run code like:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strSQL2 As String
    Set frm = Form!frmMyForm
    Set ctl = frm!lbMultiSelectListbox
    strSQL = "INSERT INTO Employees (EmpID, UnavailDate) "
    For Each varItem In ctl.ItemsSelected
        strSQL2 = strSQL & "VALUES(" & Me!cboEmployee & ",#" & ctl.ItemData(varItem) & "#);"
        CurrentDB.Execute strSQL2
    Next varItem

That should add a record for the selected employee for each date chosen in the list box.

Disclaimer: This is untested code off the top of my head. It will require customization for your object names and may require some debugging to get to work properly. But the logic is sound so you should be able to get it to work.

 

[accessdummy]

thanks.. however, i still can't get the listbox to populate the values.. where should i put the coding? in a command button?

 

[accessdummy]

pls take a look at my form coding. thanks.. screwed up

 

[ScottGem]

No, My fault. Like I said it was untested and off the top of my head. I made 2 mistakes in the For Next loop. It should have been:


For i = 1 To intDays
strList = strList & Format(dteCurr, "dd/mm/yy") & ";"
dteCurr = dteCurr + 1
Next i

I was overwriting strList each time so the Value list only reflected the last entry and I was adding the value of i to the currdate which caused gaps.