How can I change this to allow null values?

I am using the function below to update a access table. Of course it works fine as long as every field is accounted for. But if 1 value is left NULL on the form, it errors out. Is there any way to make this work, while allowing the user to leave fields blank? And, yes the table allows NULLS for all values.

Sub UpdateJob(ID, JID, Site, Phone, Fax, Addr, City, State, Zip, PMID, SID, GCID, Notes, IIA)
set conn = server.createobject ("adodb.connection")
conn.open "SouthernPan"
conn.Execute "update JobSites set " _
& "JobSiteID = '" & JID & "', " _
& "JobSite = '" & Site & "', " _
& "SitePhone = '" & Phone & "', " _
& "SiteFax = '" & Fax & "', " _
& "SiteAddress = '" & Addr & "', " _
& "SiteCity = '" & City & "', " _
& "SiteState = '" & State & "', " _
& "SiteZip = '" & Zip & "', " _
& "ProjectManagerID = '" & PMID & "', " _
& "SuperinendentID = '" & SID & "', " _
& "GCID = '" & GCID & "', " _
& "Notes = '" & Notes & "', " _
& "IsInActive = " & IIA _
& " where SiteID = " & ID
End Sub

Comments

  • could you check whether any argument is null ? if null,assign a default value like zero before you call the function.
    faustine
    --------



    : I am using the function below to update a access table. Of course it works fine as long as every field is accounted for. But if 1 value is left NULL on the form, it errors out. Is there any way to make this work, while allowing the user to leave fields blank? And, yes the table allows NULLS for all values.
    :
    : Sub UpdateJob(ID, JID, Site, Phone, Fax, Addr, City, State, Zip, PMID, SID, GCID, Notes, IIA)
    : set conn = server.createobject ("adodb.connection")
    : conn.open "SouthernPan"
    : conn.Execute "update JobSites set " _
    : & "JobSiteID = '" & JID & "', " _
    : & "JobSite = '" & Site & "', " _
    : & "SitePhone = '" & Phone & "', " _
    : & "SiteFax = '" & Fax & "', " _
    : & "SiteAddress = '" & Addr & "', " _
    : & "SiteCity = '" & City & "', " _
    : & "SiteState = '" & State & "', " _
    : & "SiteZip = '" & Zip & "', " _
    : & "ProjectManagerID = '" & PMID & "', " _
    : & "SuperinendentID = '" & SID & "', " _
    : & "GCID = '" & GCID & "', " _
    : & "Notes = '" & Notes & "', " _
    : & "IsInActive = " & IIA _
    : & " where SiteID = " & ID
    : End Sub
    :
    :

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion