Invalid use of Null - microsoft.public.access.formscoding

Hello, I have a form with a check box, date field and comment field. When the user fills it out and hits submit, it sends out an email. If I don't click the check box I get an "Invalid use of null" error. I'm not sure of the proper way to resolve this. Here is my code: Public Sub btnNotice_Click() On Error GoTo Err_btnNotice_Click Dim stApproved As Variant Dim stComments As String Dim stCompleteDate As String Dim stStatusReportID As String Dim stListCode As String Dim stListName As Variant If Me.Approved = True Then stApproved = "yes" Else stApproved = "no" End If stStatusReportID = Me.StatusReportID stListCode = Me.Parent!ListCode stListName = Me.Parent!ListName.Column(1) stCompleteDate = Me.DateQCComplete stComments = Me.Description stText = "Hello," & Chr$(13) & Chr$(13) & _ "An update has been QC'd." & Chr$(13) & Chr$(13) & _ "List Code: " & stListCode & Chr$(13) & Chr$(13) & _ "List Name: " & stListName & Chr$(13) & Chr$(13) & _ "Approved: " & stApproved & Chr$(13) & Chr$(13) & _ "Comments: " & stComments DoCmd.SendObject , , acFormatRTF, "email@domain.com", , , "QC complete", stText, 1 DoCmd.Close ' DoCmd.Close.frm003EditListInProcess Exit_btnNotice_Click: Exit Sub Err_btnNotice_Click: MsgBox err.Description Resume Exit_btnNotice_Click End Sub I'm working in MS Access 2003. Thank you, -- Marc

Marc Set a breakpoint at the top of your code, and trigger it without the checkbox. Step through, line by line, checking the values at each step. Where does the code break? What is the object that Access is finding a(n improper) Null in? Regards Jeff Boyce Microsoft Office/Access MVP "Marc" <Marc@discussions.microsoft.com> wrote in message news:F9B871D1-7397-497B-BA72-7D02791B3B7E@microsoft.com... > Hello, > > I have a form with a check box, date field and comment field. When the > user > fills it out and hits submit, it sends out an email. If I don't click the > check box I get an "Invalid use of null" error. I'm not sure of the proper > way to resolve this. > > Here is my code: > > > Public Sub btnNotice_Click() > On Error GoTo Err_btnNotice_Click > > Dim stApproved As Variant > Dim stComments As String > Dim stCompleteDate As String > Dim stStatusReportID As String > Dim stListCode As String > Dim stListName As Variant > > If Me.Approved = True Then > stApproved = "yes" > Else > stApproved = "no" > End If > stStatusReportID = Me.StatusReportID > stListCode = Me.Parent!ListCode > stListName = Me.Parent!ListName.Column(1) > stCompleteDate = Me.DateQCComplete > stComments = Me.Description > > stText = "Hello," & Chr$(13) & Chr$(13) & _ > "An update has been QC'd." & Chr$(13) & Chr$(13) & _ > "List Code: " & stListCode & Chr$(13) & Chr$(13) & _ > "List Name: " & stListName & Chr$(13) & Chr$(13) & _ > "Approved: " & stApproved & Chr$(13) & Chr$(13) & _ > "Comments: " & stComments > DoCmd.SendObject , , acFormatRTF, "email@domain.com", , , "QC > complete", stText, 1 > > DoCmd.Close > ' DoCmd.Close.frm003EditListInProcess > Exit_btnNotice_Click: > Exit Sub > > Err_btnNotice_Click: > MsgBox err.Description > Resume Exit_btnNotice_Click > > End Sub > > I'm working in MS Access 2003. > > Thank you, > -- > Marc

Try setting the default value of the check box to 0 (for False). It would have helped had you identified the check box field. I assume it is Me.Approved. BTW, you could simplify your coding in the message body by using vbCrLf instead of Chr$(13). In the Sub's declarations: Dim stCrLf as String stCrLf = vbCrLf & vbCrLf Then: stText = "Hello," & stCrLf & _ "An update has been QC'd." & stCrLf & _ "List Code: " & stListCode & stCrLf & _ etc. "Marc" <Marc@discussions.microsoft.com> wrote in message news:F9B871D1-7397-497B-BA72-7D02791B3B7E@microsoft.com... > Hello, > > I have a form with a check box, date field and comment field. When the > user > fills it out and hits submit, it sends out an email. If I don't click the > check box I get an "Invalid use of null" error. I'm not sure of the proper > way to resolve this. > > Here is my code: > > > Public Sub btnNotice_Click() > On Error GoTo Err_btnNotice_Click > > Dim stApproved As Variant > Dim stComments As String > Dim stCompleteDate As String > Dim stStatusReportID As String > Dim stListCode As String > Dim stListName As Variant > > If Me.Approved = True Then > stApproved = "yes" > Else > stApproved = "no" > End If > stStatusReportID = Me.StatusReportID > stListCode = Me.Parent!ListCode > stListName = Me.Parent!ListName.Column(1) > stCompleteDate = Me.DateQCComplete > stComments = Me.Description > > stText = "Hello," & Chr$(13) & Chr$(13) & _ > "An update has been QC'd." & Chr$(13) & Chr$(13) & _ > "List Code: " & stListCode & Chr$(13) & Chr$(13) & _ > "List Name: " & stListName & Chr$(13) & Chr$(13) & _ > "Approved: " & stApproved & Chr$(13) & Chr$(13) & _ > "Comments: " & stComments > DoCmd.SendObject , , acFormatRTF, "email@domain.com", , , "QC > complete", stText, 1 > > DoCmd.Close > ' DoCmd.Close.frm003EditListInProcess > Exit_btnNotice_Click: > Exit Sub > > Err_btnNotice_Click: > MsgBox err.Description > Resume Exit_btnNotice_Click > > End Sub > > I'm working in MS Access 2003. > > Thank you, > -- > Marc

Marc <Marc@discussions.microsoft.com> wrote: > Hello, > > I have a form with a check box, date field and comment field. When the user > fills it out and hits submit, it sends out an email. If I don't click the > check box I get an "Invalid use of null" error. I'm not sure of the proper > way to resolve this. > > Here is my code: > [...] Set the Default Property of the check box to False. HTH Matthias Kl�y -- www.kcc.ch

Thanks everyone for the quick replies. I tried Me.Approved.DefaultValue = 0 and Me.Approved.DefaultValue = False but am still getting the error. What am I missing? and thanks Bruce for the code cleanup tip! -- Marc "BruceM" wrote: > Try setting the default value of the check box to 0 (for False). It would > have helped had you identified the check box field. I assume it is > Me.Approved. > BTW, you could simplify your coding in the message body by using vbCrLf > instead of Chr$(13). In the Sub's declarations: > > Dim stCrLf as String > stCrLf = vbCrLf & vbCrLf > > Then: > > stText = "Hello," & stCrLf & _ > "An update has been QC'd." & stCrLf & _ > "List Code: " & stListCode & stCrLf & _ > etc. > > "Marc" <Marc@discussions.microsoft.com> wrote in message > news:F9B871D1-7397-497B-BA72-7D02791B3B7E@microsoft.com... > > Hello, > > > > I have a form with a check box, date field and comment field. When the > > user > > fills it out and hits submit, it sends out an email. If I don't click the > > check box I get an "Invalid use of null" error. I'm not sure of the proper > > way to resolve this. > > > > Here is my code: > > > > > > Public Sub btnNotice_Click() > > On Error GoTo Err_btnNotice_Click > > > > Dim stApproved As Variant > > Dim stComments As String > > Dim stCompleteDate As String > > Dim stStatusReportID As String > > Dim stListCode As String > > Dim stListName As Variant > > > > If Me.Approved = True Then > > stApproved = "yes" > > Else > > stApproved = "no" > > End If > > stStatusReportID = Me.StatusReportID > > stListCode = Me.Parent!ListCode > > stListName = Me.Parent!ListName.Column(1) > > stCompleteDate = Me.DateQCComplete > > stComments = Me.Description > > > > stText = "Hello," & Chr$(13) & Chr$(13) & _ > > "An update has been QC'd." & Chr$(13) & Chr$(13) & _ > > "List Code: " & stListCode & Chr$(13) & Chr$(13) & _ > > "List Name: " & stListName & Chr$(13) & Chr$(13) & _ > > "Approved: " & stApproved & Chr$(13) & Chr$(13) & _ > > "Comments: " & stComments > > DoCmd.SendObject , , acFormatRTF, "email@domain.com", , , "QC > > complete", stText, 1 > > > > DoCmd.Close > > ' DoCmd.Close.frm003EditListInProcess > > Exit_btnNotice_Click: > > Exit Sub > > > > Err_btnNotice_Click: > > MsgBox err.Description > > Resume Exit_btnNotice_Click > > > > End Sub > > > > I'm working in MS Access 2003. > > > > Thank you, > > -- > > Marc > > >

Hi Jeff, I should have tried your idea first. It appears my problem is also in the "comments" field. It looks like setting the default value of the check box (approved) solved that problem. Thanks for your help. -- Marc "Jeff Boyce" wrote: > Marc > > Set a breakpoint at the top of your code, and trigger it without the > checkbox. Step through, line by line, checking the values at each step. > Where does the code break? > > What is the object that Access is finding a(n improper) Null in? > > Regards > > Jeff Boyce > Microsoft Office/Access MVP > > "Marc" <Marc@discussions.microsoft.com> wrote in message > news:F9B871D1-7397-497B-BA72-7D02791B3B7E@microsoft.com... > > Hello, > > > > I have a form with a check box, date field and comment field. When the > > user > > fills it out and hits submit, it sends out an email. If I don't click the > > check box I get an "Invalid use of null" error. I'm not sure of the proper > > way to resolve this. > > > > Here is my code: > > > > > > Public Sub btnNotice_Click() > > On Error GoTo Err_btnNotice_Click > > > > Dim stApproved As Variant > > Dim stComments As String > > Dim stCompleteDate As String > > Dim stStatusReportID As String > > Dim stListCode As String > > Dim stListName As Variant > > > > If Me.Approved = True Then > > stApproved = "yes" > > Else > > stApproved = "no" > > End If > > stStatusReportID = Me.StatusReportID > > stListCode = Me.Parent!ListCode > > stListName = Me.Parent!ListName.Column(1) > > stCompleteDate = Me.DateQCComplete > > stComments = Me.Description > > > > stText = "Hello," & Chr$(13) & Chr$(13) & _ > > "An update has been QC'd." & Chr$(13) & Chr$(13) & _ > > "List Code: " & stListCode & Chr$(13) & Chr$(13) & _ > > "List Name: " & stListName & Chr$(13) & Chr$(13) & _ > > "Approved: " & stApproved & Chr$(13) & Chr$(13) & _ > > "Comments: " & stComments > > DoCmd.SendObject , , acFormatRTF, "email@domain.com", , , "QC > > complete", stText, 1 > > > > DoCmd.Close > > ' DoCmd.Close.frm003EditListInProcess > > Exit_btnNotice_Click: > > Exit Sub > > > > Err_btnNotice_Click: > > MsgBox err.Description > > Resume Exit_btnNotice_Click > > > > End Sub > > > > I'm working in MS Access 2003. > > > > Thank you, > > -- > > Marc > > >

DefaultValue applies only to new records. You would need to change existing records to have them reflect the value. If you use DefaultValue you must apply it before the record is first saved. "Marc" <Marc@discussions.microsoft.com> wrote in message news:A71A0984-BF28-4661-950C-F1B19DB1A705@microsoft.com... > Thanks everyone for the quick replies. I tried > Me.Approved.DefaultValue = 0 > and > Me.Approved.DefaultValue = False > but am still getting the error. What am I missing? > > and thanks Bruce for the code cleanup tip! > > -- > Marc > > > "BruceM" wrote: > >> Try setting the default value of the check box to 0 (for False). It >> would >> have helped had you identified the check box field. I assume it is >> Me.Approved. >> BTW, you could simplify your coding in the message body by using vbCrLf >> instead of Chr$(13). In the Sub's declarations: >> >> Dim stCrLf as String >> stCrLf = vbCrLf & vbCrLf >> >> Then: >> >> stText = "Hello," & stCrLf & _ >> "An update has been QC'd." & stCrLf & _ >> "List Code: " & stListCode & stCrLf & _ >> etc. >> >> "Marc" <Marc@discussions.microsoft.com> wrote in message >> news:F9B871D1-7397-497B-BA72-7D02791B3B7E@microsoft.com... >> > Hello, >> > >> > I have a form with a check box, date field and comment field. When the >> > user >> > fills it out and hits submit, it sends out an email. If I don't click >> > the >> > check box I get an "Invalid use of null" error. I'm not sure of the >> > proper >> > way to resolve this. >> > >> > Here is my code: >> > >> > >> > Public Sub btnNotice_Click() >> > On Error GoTo Err_btnNotice_Click >> > >> > Dim stApproved As Variant >> > Dim stComments As String >> > Dim stCompleteDate As String >> > Dim stStatusReportID As String >> > Dim stListCode As String >> > Dim stListName As Variant >> > >> > If Me.Approved = True Then >> > stApproved = "yes" >> > Else >> > stApproved = "no" >> > End If >> > stStatusReportID = Me.StatusReportID >> > stListCode = Me.Parent!ListCode >> > stListName = Me.Parent!ListName.Column(1) >> > stCompleteDate = Me.DateQCComplete >> > stComments = Me.Description >> > >> > stText = "Hello," & Chr$(13) & Chr$(13) & _ >> > "An update has been QC'd." & Chr$(13) & Chr$(13) & _ >> > "List Code: " & stListCode & Chr$(13) & Chr$(13) & _ >> > "List Name: " & stListName & Chr$(13) & Chr$(13) & _ >> > "Approved: " & stApproved & Chr$(13) & Chr$(13) & _ >> > "Comments: " & stComments >> > DoCmd.SendObject , , acFormatRTF, "email@domain.com", , , "QC >> > complete", stText, 1 >> > >> > DoCmd.Close >> > ' DoCmd.Close.frm003EditListInProcess >> > Exit_btnNotice_Click: >> > Exit Sub >> > >> > Err_btnNotice_Click: >> > MsgBox err.Description >> > Resume Exit_btnNotice_Click >> > >> > End Sub >> > >> > I'm working in MS Access 2003. >> > >> > Thank you, >> > -- >> > Marc >> >> >>

Makes sense. Thanks. It looks like it's working. Thanks again, -- Marc "BruceM" wrote: > DefaultValue applies only to new records. You would need to change existing > records to have them reflect the value. If you use DefaultValue you must > apply it before the record is first saved. > > "Marc" <Marc@discussions.microsoft.com> wrote in message > news:A71A0984-BF28-4661-950C-F1B19DB1A705@microsoft.com... > > Thanks everyone for the quick replies. I tried > > Me.Approved.DefaultValue = 0 > > and > > Me.Approved.DefaultValue = False > > but am still getting the error. What am I missing? > > > > and thanks Bruce for the code cleanup tip! > > > > -- > > Marc > > > > > > "BruceM" wrote: > > > >> Try setting the default value of the check box to 0 (for False). It > >> would > >> have helped had you identified the check box field. I assume it is > >> Me.Approved. > >> BTW, you could simplify your coding in the message body by using vbCrLf > >> instead of Chr$(13). In the Sub's declarations: > >> > >> Dim stCrLf as String > >> stCrLf = vbCrLf & vbCrLf > >> > >> Then: > >> > >> stText = "Hello," & stCrLf & _ > >> "An update has been QC'd." & stCrLf & _ > >> "List Code: " & stListCode & stCrLf & _ > >> etc. > >> > >> "Marc" <Marc@discussions.microsoft.com> wrote in message > >> news:F9B871D1-7397-497B-BA72-7D02791B3B7E@microsoft.com... > >> > Hello, > >> > > >> > I have a form with a check box, date field and comment field. When the > >> > user > >> > fills it out and hits submit, it sends out an email. If I don't click > >> > the > >> > check box I get an "Invalid use of null" error. I'm not sure of the > >> > proper > >> > way to resolve this. > >> > > >> > Here is my code: > >> > > >> > > >> > Public Sub btnNotice_Click() > >> > On Error GoTo Err_btnNotice_Click > >> > > >> > Dim stApproved As Variant > >> > Dim stComments As String > >> > Dim stCompleteDate As String > >> > Dim stStatusReportID As String > >> > Dim stListCode As String > >> > Dim stListName As Variant > >> > > >> > If Me.Approved = True Then > >> > stApproved = "yes" > >> > Else > >> > stApproved = "no" > >> > End If > >> > stStatusReportID = Me.StatusReportID > >> > stListCode = Me.Parent!ListCode > >> > stListName = Me.Parent!ListName.Column(1) > >> > stCompleteDate = Me.DateQCComplete > >> > stComments = Me.Description > >> > > >> > stText = "Hello," & Chr$(13) & Chr$(13) & _ > >> > "An update has been QC'd." & Chr$(13) & Chr$(13) & _ > >> > "List Code: " & stListCode & Chr$(13) & Chr$(13) & _ > >> > "List Name: " & stListName & Chr$(13) & Chr$(13) & _ > >> > "Approved: " & stApproved & Chr$(13) & Chr$(13) & _ > >> > "Comments: " & stComments > >> > DoCmd.SendObject , , acFormatRTF, "email@domain.com", , , "QC > >> > complete", stText, 1 > >> > > >> > DoCmd.Close > >> > ' DoCmd.Close.frm003EditListInProcess > >> > Exit_btnNotice_Click: > >> > Exit Sub > >> > > >> > Err_btnNotice_Click: > >> > MsgBox err.Description > >> > Resume Exit_btnNotice_Click > >> > > >> > End Sub > >> > > >> > I'm working in MS Access 2003. > >> > > >> > Thank you, > >> > -- > >> > Marc > >> > >> > >> > > >

Check for last record

315

Utf

displaying embedded image in unboud form

418

Utf

If Then Statement

Utf

Determine if user is a member of a group in Access security

277

Utf

How prevent form close when 'BeforeUpdate' cancelled

216

Utf

Help: Shell out to File Explorer

202

Utf

Application.FileSearch 2007 Reference

321

Utf

showing a subform based on the main form

109

Utf

Vb code working one time

109

Utf

change number field to text field

119

Utf

8/20/2012 12:23:13 AM