Multiple entries with checkboxes

[ptrifile]

Hi, looking for a little help. I have a form with 6 checkboxes on it, one for each day of the week. I also have 2 textboxes for a StartTime and EndTime. If every check box is checked (Monday through Saturday) i would like an entry in the table for each one stating the following:


Dayofweek startime endtime
Monday 9:00 5:00
Tuesday 9:00 5:00
Wednesday 9:00 5:00
Thursday 9:00 5:00
Friday 9:00 5:00
Saturday 9:00 5:00

If say only Monday and Tuesday were checked i would only like the following stored in the Table:
Monday 9:00 5:00
Tuesday 9:00 5:00

I have played around with the insert into clause and have come close but have not gotten the desired results. Anyone have any suggestions?


Thanks for any help!

Paul

 

[Andrzejek]

If I would do it in Excel's Form, I would have Captions of my check boxes: Monday, Tuesday, ..., and this code:

Dim cntr As MSForms.Control

For Each cntr In Me.Controls
    If TypeOf cntr Is MSForms.CheckBox Then
        If cntr.Value = True Then
            Debug.Print "Insert into " & cntr.Caption
        End If
    End If
Next cntr

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ptrifile]

Thanks Andy, below is the code that i currently have and its half working, the days are populating as i wish as separate entries. I'm sure there is a MUCH better way to write the code but again i have it half working. What I am missing is how to get the entered data from the 2 text boxes (starttime and endtime) into the record for each day that is checked. Any Suggestions?

Private Sub Command20_Click()
If Me.chkMon = True Then
DoCmd.RunSQL "insert into tblshifts ([shiftday]) values ('Monday')"
If Me.chktue = True Then
DoCmd.RunSQL "insert into tblshifts ([shiftday]) values ('Tuesday')"
End If
End If
End Sub

Thanks!

Paul

 

[ptrifile]

I think I got this working. I used:

DoCmd.RunSQL "insert into tblshifts ([shiftday], [starttime]) values ('Monday','" & StartTime & "')"

Thanks for the help!

Paul

 

[SkipVought]

Hmmmmm????

No DATE, like a week of date, associated with these values in your table?

 

[ptrifile]

Skip, no I do not need a DATE associated with these as they are being used as shifts that are simply available for ANY given day.....

I ran into a problem with the code that I used when I added the ENDTIME to the insert into code. (Run-time error '3134': Syntax error in INSERT INTO statement.

Can you see anything wrong with the code below?

DoCmd.RunSQL "insert into tblshifts ([shiftday], [starttime], [endtime) values ('Monday','" & StartTime & "','" & EndTime & "')"

Thanks for the help!

Paul

 

[ptrifile]

Got it, never mind. I missed the ] after endtime.

 

[Andrzejek]

I would try something like this:

Dim cntr As Control
Dim strSQL As String

For Each cntr In Me.Controls
    If TypeOf cntr Is CheckBox Then
        If cntr.Value = True Then
            strSQL = "insert into tblshifts (shiftday, starttime, endtime) values ('" & cntr.Caption
 & "','" & StartTime & "','" & EndTime & "')" 
            DoCmd.RunSQL strSQL
        End If
    End If
Next cntr

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

I would use the following which assumes all check boxes are named using the same pattern.

[highlight #FCE94F][/highlight]

Private Sub Command20_Click()
    Dim intDay as Integer
    Dim strSQL as String
[highlight #FCE94F][COLOR=#A40000]    'assumes starttime and end time are datetime fields, if not replace # with '
    'I would also store the weekday number rather than the day name but that's just my preference[/color][/highlight]
    For intDay = 2 To 6  'monday through friday
        If Me("chk" & Format(intDay,"ddd")) = True Then
            strSQL = "insert into tblshifts (shiftday, StartTime, EndTime) values ('" & _
                Format(intDay,"dddd") & "',#" & Me.StartTime & "#,#" & Me.EndTime & "#)"
            Currentdb.Execute strSQL, dbFailOnError
        End If
    Next
End Sub

Duane
Hook'D on Access
MS Access MVP