run-time error 94, invalid use of null

[spartansFC]

Hi

I've created a form on my database which works perfectly and i need to create another form which is pretty much identical but uses different fields. The new form is to set up a staff rota for the entire year. The user enters:

lngClubName (cmbClubname)
lngClubStaffName (cmbClubStaffName)
ysnMonActive (check box)
lngRotaMonStaffActiveID (cmbMonStaffActive)
lngRotaMonStaffLocateID (cmbMonStaffLocate)
ysnTueActive (check box) ......
dteStartDate
dteEndDate

so the users enters all the information above, enters the start and end date, the piece of code kicks in and populates a table for all the mondays from the start to end date.

So what happens now is the code gets to a certain point and i get the invalid use of null, when i hover over the highlighted piece of code, it says the value is 0? i even tried adding a default value for the check boxes so that there was a value in it, but it still gets stopped at the same line.

the code is:

Private Sub cmdStaffRotaBuilder_Click()
Dim datThis As Date
Dim dteStartDate As Date
Dim dteEndDate As Date
Dim strSQL As String
Dim lngRotaClubsID As Long
Dim lngRotaClubStaffID As Long
Dim ysnRotaMonCheck As CheckBox
Dim lngRotaMonStaffActiveID As Long
Dim lngRotaMonStaffLocateID As Long
Dim ysnRotaTueCheck As CheckBox
Dim lngRotaTueStaffActiveID As Long
Dim lngRotaTueStaffLocateID As Long
Dim ysnRotaWedCheck As Checkbox
Dim lngRotaWedStaffActiveID As Long
Dim lngRotaWedStaffLocateID As Long
Dim ysnRotaThurCheck As CheckBox
Dim lngRotaThurStaffActiveID As Long
Dim lngRotaThurStaffLocateID As Long
Dim ysnRotaFriCheck As CheckBox
Dim lngRotaFriStaffActiveID As Long
Dim lngRotaFriStaffLocateID As Long
Dim db As DAO.Database
Dim IntDOW As Integer 'day of week
Dim IntDIM As Integer 'day of week

If Me.grpRepeats = 2 Then
        If Not CheckDates() Then
            Exit Sub
        End If
    End If
    'If Not CheckTimes() Then
     '   Exit Sub
    'End If

    If Me.chkDay02 = "No" Then
        MsgBox "You must tick the box next to End Date.", vbOKOnly + vbInformation, "Tick Box"
        Me.chkDay02.SetFocus
        Exit Sub
     End If
    
       
    lngRotaClubsID = Me.cmbStaffRota
    lngRotaClubStaffID = Me.cmbStaffNameRota
    ysnRotaMonCheck = Me.MonCheck
    lngRotaMonStaffActiveID = Me.cmbMonStaffActive
    lngRotaMonStaffLocateID = Me.cmbMonStaffLocation
[COLOR=red]    [b]ysnRotaTueCheck = Me.TueCheck[/b][/color]
    lngRotaTueStaffActiveID = Me.cmbTueStaffActive
    lngRotaTueStaffLocateID = Me.cmbTueStaffLocation
    ysnRotaWedCheck = Me.WedCheck
    lngRotaWedStaffActiveID = Me.cmbWedStaffActive
    lngRotaWedStaffLocateID = Me.cmbWedStaffLocation
    ysnRotaThurCheck = Me.ThurCheck
    lngRotaThurStaffActiveID = Me.cmbThurStaffActive
    lngRotaThurStaffLocateID = Me.cmbThurStaffLocation
    ysnRotaFriCheck = Me.FriCheck
    lngRotaFriStaffActiveID = Me.cmbFriStaffActive
    lngRotaFriStaffLocateID = Me.cmbFriStaffLocation
    
    
    'dteStartDate = Me.frmOccupancyBuildNewSubMonthYear.Form.TempMonthStartDate
    'dteEndDate = Me.frmOccupancyBuildNewSubMonthYear.Form.TempMonthEndDate
      
    Set db = CurrentDb
    
    If Me.grpRepeats = 2 Then 'need to loop through dates
        For datThis = Me.txtStartDate To Me.txtEndDate
            IntDIM = GetDIM(datThis)
            IntDOW = Weekday(datThis)
            If Me("chkDay" & IntDIM & IntDOW) = True Or _
                    Me("chkDay0" & IntDOW) = True Then
                strSQL = "INSERT INTO tblStaffRotaBuilderTemp (" & _
                    "tscRotaDate, tscRotalngClubsID, tsclngRotaClubStaffID, " & _
                    "tscysnRotaMon, tsclngRotaMonActive,  tsclngRotaMonLocate,  " & _
                    "tscysnRotaTue, tsclngRotaTueActive,  tsclngRotaTueLocate,  " & _
                    "tscysnRotaWed, tsclngRotaWedActive,  tsclngRotaWedLocate,  " & _
                    "tscysnRotaThur, tsclngRotaThurActive,  tsclngRotaThurLocate,  " & _
                    "tscysnRotaFri, tsclngRotaFriActive,  " & _
                    "tsclngRotaFriLocate ) " & _
                    "Values(#" & Format(datThis, "yyyy\/mm\/dd") & "#," & lngRotaClubsID & ", " & _
                    lngRotaClubStaffID & ", " & ysnRotaMonCheck & ", " & lngRotaMonStaffActiveID & ", " & _
                    lngRotaMonStaffLocateID & ", " & ysnRotaTueCheck & ", " & lngRotaTueStaffActiveID & ", " & _
                    lngRotaTueStaffLocateID & ", " & ysnRotaWedCheck & ", " & lngRotaWedStaffActiveID & ", " & _
                    lngRotaWedStaffLocateID & ", " & ysnRotaThurCheck & ", " & lngRotaThurStaffActiveID & ", " & _
                    lngRotaThurStaffLocateID & ", " & ysnRotaFriCheck & ", " & _
                    lngRotaFriStaffActiveID & ", " & lngRotaFriStaffLocateID & ")"
                    'lngRotaFriStaffLocateID & ")"
                    
                    'IIf(IsNull(varNotes), "Null", """" & varNotes & """") & ")"
                db.Execute strSQL, dbFailOnError
            End If
            Next
        Else
            strSQL = "Update tblStaffRotaBuilderTemp Set tscRotalngClubsID = " & lngRotaClubsID & _
             ", tsclngRotaClubStaffID = " & lngRotaClubStaffID & _
             ", tscysnRotaMon = " & ysnRotaMonCheck & _
             ", tsclngRotaMonActive = " & lngRotaMonStaffActiveID & _
             ", tsclngRotaMonLocate = " & lngRotaMonStaffLocateID & _
             ", tscysnRotaTue = " & ysnRotaTueCheck & _
             ", tsclngRotaTueActive = " & lngRotaTueStaffActiveID & _
             ", tsclngRotaTueLocate = " & lngRotaTueStaffLocateID & _
             ", tscysnRotaWed = " & ysnRotaWedCheck & _
             ", tsclngRotaWedActive = " & lngRotaWedStaffActiveID & _
             ", tsclngRotaWedLocate = " & lngRotaWedStaffLocateID & _
             ", tscysnRotaThur = " & ysnRotaThurCheck & _
             ", tsclngRotaThurActive = " & lngRotaThurStaffActiveID & _
             ", tsclngRotaThurLocate = " & lngRotaThurStaffLocateID & _
             ", tscysnRotaFri = " & ysnRotaFriCheck & _
             ", tsclngRotaFriActive = " & lngRotaFriStaffActiveID & _
             ", tsclngRotaFriLocate = " & lngRotaFriStaffLocateID & ""
             '", tscdteSessionEndDate = " & Me.frmOccupancyBuildNewSubMonthYear.Form.TempMonthEndDate_txtbox & _
             '", tscdteSessionStartDate = " & Me.frmOccupancyBuildNewSubMonthYear.Form.TempMonthStartDate_txtbox & ""
            
            db.Execute strSQL, dbFailOnError
    End If
    Me.frmStaffRotaBuilderTemp.Requery
    MsgBox "Temporary Staff Rota built. " & _
    "You can now edit the staff rota and " & _
    "append to the permanent staff rota.", vbOKOnly + vbInformation, "Temp staff rota complete"
End Sub

So i've highlighted in red where the code gets stuck, the only difference i can think of is i'm using check boxes in this version of the form which is why the code think's the value is null.

The check box might not be ticked as a person might not work on that day, each data represents a week commencing date, the i have a tick box for each day of the week.

So what am i doing wrong

Mikie

 

[PHV]

What about this ?
ysnRotaTueCheck = Nz(Me.TueCheck, 0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[spartansFC]

Thanks PHV, your suggestion worked a treat