Hi everyone I have a problem with my VB code not running when inserting triple state button value containing NULL values in the script bellow:
The check boxes are all triple state with default value of NULL but can contain a yes or no when required. This loop work fine when the value is yes or no but NULL results in an error. Is there any way for me to get NULL values inserted in my form without the error.
The error I get is Run-time error '3134' Syntax error in INSERT INTO statement.
Dim intweek As Integer
Dim dte As Date
Dim dteLOOP As Date
If Me.Combo59.ListIndex = -1 Then
ElseIf Nz(Me!ID, 0) = 0 Then
MsgBox "Cannot generate date when there is no record in main form"
Exit Sub
Else
intweek = Val(Trim(Replace(Me.Combo59, "Week", ""))) - 1
Me.txtWeek = intweek
If DCount("1", "[Loco Checklist]", "WeekNo=" & intweek & " And ID=" & Me.ID & " And [Loco Number]=" & Chr(34) & Me![Equipment No] & Chr(34)) > 0 Then
'do nothing, records for this week already been generated
Else
'get the first sunday for the week number
dte = WeekFirstDay(DateAdd("ww", intweek, DateSerial(Year(Now()), 1, 1)), vbSunday)
For dteLOOP = dte To dte + 7
CurrentDb.Execute "Insert Into [Winch] (" & _
"[Tip Barricade],[Safety Belt],[Grizzley],[Hammer and Hook],[ID], [Equipment],[DateS], [WeekNo]) " & _
"select " & _
Me![Check84] & "," & _
Me![Check84] & "," & _
Me![Check84] & "," & _
Me![Check84] & "," & _
Me!ID & "," & _
Chr(34) & Me![Text54].Value & Chr(34) & "," & _
"#" & Format(dteLOOP, "mm/dd/yyyy") & "#," & _
intweek
Next
Me![Winch].Form.Requery
End If
End If
The check boxes are all triple state with default value of NULL but can contain a yes or no when required. This loop work fine when the value is yes or no but NULL results in an error. Is there any way for me to get NULL values inserted in my form without the error.
The error I get is Run-time error '3134' Syntax error in INSERT INTO statement.
Dim intweek As Integer
Dim dte As Date
Dim dteLOOP As Date
If Me.Combo59.ListIndex = -1 Then
ElseIf Nz(Me!ID, 0) = 0 Then
MsgBox "Cannot generate date when there is no record in main form"
Exit Sub
Else
intweek = Val(Trim(Replace(Me.Combo59, "Week", ""))) - 1
Me.txtWeek = intweek
If DCount("1", "[Loco Checklist]", "WeekNo=" & intweek & " And ID=" & Me.ID & " And [Loco Number]=" & Chr(34) & Me![Equipment No] & Chr(34)) > 0 Then
'do nothing, records for this week already been generated
Else
'get the first sunday for the week number
dte = WeekFirstDay(DateAdd("ww", intweek, DateSerial(Year(Now()), 1, 1)), vbSunday)
For dteLOOP = dte To dte + 7
CurrentDb.Execute "Insert Into [Winch] (" & _
"[Tip Barricade],[Safety Belt],[Grizzley],[Hammer and Hook],[ID], [Equipment],[DateS], [WeekNo]) " & _
"select " & _
Me![Check84] & "," & _
Me![Check84] & "," & _
Me![Check84] & "," & _
Me![Check84] & "," & _
Me!ID & "," & _
Chr(34) & Me![Text54].Value & Chr(34) & "," & _
"#" & Format(dteLOOP, "mm/dd/yyyy") & "#," & _
intweek
Next
Me![Winch].Form.Requery
End If
End If