Invalid Use of Null

arholly · February 1st, 2007, 10:47 AM

Hi Everyone,
I am working on a form where unfortunately, there is a lot of null information. So, when I go to populate my form, I'm getting "Invalid use of Null". I know there is going to be a null field, but I want it to make the fields Null. Is there a way to work around it.

Explanation: I have a old_course_Code which is going to be blank for a lot of old courses. I want it to take the null field to the update form I'm using and keep it null so the field can then be updated in the update field.

Is this making any sense?

Best Regards,
Arholly

mmcdonal · February 2nd, 2007, 08:38 AM

I don't think you can make a field Null. What are you doing that is giving you the error? When you say you go to populate your form, what does that mean?

mmcdonal

arholly · February 2nd, 2007, 10:16 AM

Quote:

quote:Originally posted by mmcdonal
I don't think you can make a field Null. What are you doing that is giving you the error? When you say you go to populate your form, what does that mean?

Okay, it is one of the forms we've worked on previously in this forums. I've had to add three new fields (first_session, last_session, old_course). Well, many of those fields we don't have information on, so they are null. So, when I am in my lookup form and go to click on the update record button, it tells me invalid use of null because one or more of those fields is null.

mmcdonal · February 2nd, 2007, 10:32 AM

Oh, what is the code in the look up form?

You can account for Nulls in your code so that the look up bypasses them when they are Null, OR, you can put some value in the fields when the record is created, or now with an Update query. You can even use an empty string, like " ", which is not Null even though it is empty.

mmcdonal

arholly · February 2nd, 2007, 10:49 AM

Quote:

quote:Originally posted by mmcdonal
Oh, what is the code in the look up form?

You can account for Nulls in your code so that the look up bypasses them when they are Null, OR, you can put some value in the fields when the record is created, or now with an Update query. You can even use an empty string, like " ", which is not Null even though it is empty.

Code:

Private Sub Button_Update_Course_Click()
On Error GoTo Err_Button_Update_Course_Click

sCourseID = Me.CourseID
sSchool = Me.School
sProg = Me.Program
sCourse_Code = Me.Course_Code
sCourse_Desc = Me.Course_Description
sCDPM = Me.CDPM
sSession_Start = Me.Session_Start
sLast_Session_Taught = Me.Last_Session_Taught
sOld_Course_Code = Me.Old_Course_Code

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm_Step2_Course_Update"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Button_Update_Course_Click:
    Exit Sub

Err_Button_Update_Course_Click:
    MsgBox Err.Description
    Resume Exit_Button_Update_Course_Click

End Sub

The s_variables are public values I'm using, as per your suggestion.

mmcdonal · February 5th, 2007, 08:18 AM

Okay, you have to check for nulls for each variable before you try to assign a value to it. Something like this (I know there is more elegant code):

Private Sub Button_Update_Course_Click()
On Error GoTo Err_Button_Update_Course_Click

'This should never be null so ok code
sCourseID = Me.CourseID

'This also should never be null.
sSchool = Me.School

If IsNull(Me.Program) Or Me.Program = "" Then
   sProg = " "
Else
   sProg = Me.Program
End If

If IsNull(Me.Course_Code) Or Me.Course_Code = "" Then
   sCourse_Code = " "
Else
   sCourse_Code = Me.Course_Code
End If

If IsNull(Me.Course_Description) Or Me.Course_Description = "" Then
   sCourse_Desc = " "
Else
   sCourse_Desc = Me.Course_Description
End If

etc.... throughot the variables that will get a Null value now and then...

Did that help?

mmcdonal