Doesn't Execute if OldValue or Value is Null

I am using the following code in the Before Update Event of a field.

Private Sub Suffix_BeforeUpdate(Cancel As Integer)

    '///////////////////////////////////////////////////////
    '/ Records changes made to LastName field if           /'
    '/ Employee is CPRP Certified when the changes         /'
    '/ are made.  CPRP Certified is "Certified", "Medical  /'
    '/ Restrictions" or "Temporary Decertification" or     /'
    '/ "Interim Certified" These changes effect the CDPR   /'
    '/ and the Key List Roster                             /'
    '///////////////////////////////////////////////////////
   
    If Me.CertificationType = "Certified" Or Me.CertificationType = "Interim Certified" Or Me.CertificationType = "Medical Restrictions" Or Me.CertificationType = "Temporary Decertification" Then
   
        CurrentDb.Execute "INSERT INTO tblChanges (EmpNum, ChangeMade, MadeWhen, LoginName) VALUES('" & Me.EmpNo & "','" & "Name Changed/Updated" & "','" & Now() & "','" & [Forms]![frmUserInfo].[LoginName].Value & "')"
   
            If Not IsNull([CertifyingOfficial]) Then
           
                CurrentDb.Execute "INSERT INTO tblChanges (EmpNum, ChangeMade, MadeWhen, FieldOldValue, FieldNewValue, PrintCDPRCO, ListValue) VALUES ('" & Me.EmpNo & "','" & "Name Changed/Updated" & "','" & Now() & "','" & Me.Suffix.OldValue & "','" & Me.Suffix.Value & "','" & -1 & "','" & Me.CertifyingOfficial & "')"
               
            End If
   
            If Not IsNull([AminOffical]) Then
           
                CurrentDb.Execute "INSERT INTO tblChanges (EmpNum, ChangeMade, MadeWhen, FieldOldValue, FieldNewValue, PrintCDPRAO, ListValue) VALUES ('" & Me.EmpNo & "','" & "Name Changed/Updated" & "','" & Now() & "','" & Me.Suffix.OldValue & "','" & Me.Suffix.Value & "','" & -1 & "','" & Me.AminOffical & "')"
               
            End If
           
            If Not IsNull([LockKeySeries]) Then
           
                CurrentDb.Execute "INSERT INTO tblChanges (EmpNum, ChangeMade, MadeWhen, FieldOldValue, FieldNewValue, PrintKeyList, ListValue) VALUES ('" & Me.EmpNo & "','" & "Name Changed/Updated" & "','" & Now() & "','" & Me.Suffix.OldValue & "','" & Me.Suffix.Value & "','" & -1 & "','" & Me.LockKeySeries & "')"
           
            End If
   
    End If

End Sub

Works great unless the OldValue or New Value is Null.  Why?  How do I make it capture that change too?

Thanks!
Lena
LVL 10
LenaWoodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ColosseoCommented:
Hi

if the value is null do you just want to store an empty string?

if so you could use the nz function to change the null values to empty strings

for example where ever you have Me.Suffix.Value put NZ(Me.Suffix.Value,"") and for Me.Suffix.OldValue NZ(Me.Suffix.OldValue,"")

If Me.Suffix.Value is null it will be replaced with an empty string

HTH

Scott
0
stevbeCommented:
Is the problem with CertificationType?

I would make sure the record is saved first and then test for Null and then use a select case to make the IF less cumbersome ...


'force record to be saved
Me.Dirty = False

If Len(Me.CertificationType.Value & vbNullString) = 0 Then
    Msgbox "no updates will happen"
Else
    Select Case Me.CertificationType.Value
        Case "Certified", "Interim Certified", "Medical Restrictions", "Temporary Decertification"
                   ' run all of your SQL exeutes here
    End Select

I would also change how I test for null because it is possible to be Empty or Zero Length String ...

from

If Not IsNull([LockKeySeries]) Then

to

If Len([LockKeySeries] & vbNullString) > 0 Then

Steve
0
LenaWoodAuthor Commented:
The Certification Type isn't the problem...although I will try your advice in checking for null.  The Certification Type, Certifying Offical, AminOfficial, and LockKeySeries are combo boxes with the Limit To List set to yes....but just in case ;-)

I will also try the NZ suggestion.  I will let you know how it goes.

Thanks to you both for the quick response!
Lena
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pique_techCommented:
Are the table fields you're trying to save the audit records TO configured to accept NULLs?  Does the form have any validation on those fields that prevents NULLs?
0
stevbeCommented:
If you use CurrentDB.Execute you should set the Options parameter to dbFailOnError (you can use 128) if you do not have a reference to the Microsoft DAO 3.X Object Library) so that the procedure will throw an error if anything goes wrong. The way you have it now it will silently fail.

Steve
0
LenaWoodAuthor Commented:
Not everyone has a Suffix on the end of their name...therefore most records will have that field null.  I have not configured the fields in the table to not except nulls.

Lena
0
LenaWoodAuthor Commented:
I am not sure how to use dbFailOnError...please explain :-)
0
stevbeCommented:
.Execute has 2 parameters: the first is the SQL to execute and the second tells it to returbn errors ...

CurrentDB.Execute "INSERT INTO ...", dbFailOnError

Steve

0
stevbeCommented:
one way to trouble shoot SQL statements is to print the SQL to the immediate window and then paste that into a query and try to run it, this will usually give you betyter information on why it is not working ...

Dim strSQL As String

strSQL = "INSERT INTO tblChanges (EmpNum, ChangeMade, MadeWhen, LoginName) VALUES('" & Me.EmpNo & "','" & "Name Changed/Updated" & "','" & Now() & "','" & [Forms]![frmUserInfo].[LoginName].Value & "')"

Debug.Print strSQL

DBEngine(0)(0).Execute strSQL, dbFailOnError


the immediate window can be opened from any code window with ctl+g keys.

Steve
0
LenaWoodAuthor Commented:
Scott....NZ didn't work :-(

CurrentDb.Execute "INSERT INTO tblChanges (EmpNum, ChangeMade, MadeWhen, FieldOldValue, FieldNewValue, PrintCDPRCO, ListValue) VALUES ('" & Me.EmpNo & "','" & "Name Changed/Updated" & "','" & Now() & "','" & Nz(Me.Suffix.OldValue, "") & "','" & Nz(Me.Suffix.Value, "") & "','" & -1 & "','" & Me.CertifyingOfficial & "')"

Steve....I will give that a try.
0
pique_techCommented:
CurrentDb.Execute "INSERT INTO tblChanges (EmpNum, ChangeMade, MadeWhen, FieldOldValue, FieldNewValue, PrintKeyList, ListValue) VALUES ('" & Me.EmpNo & "','" & "Name Changed/Updated" & "','" & Now() & "','" & Me.Suffix.OldValue & "','" & Me.Suffix.Value & "','" & -1 & "','" & Me.LockKeySeries & "')"

Why do you have everything in your VALUES list wrapped in quotes?  They don't all seem to be strings (for example, Me.EmpNo, Now()), and what you'll pass for NULL values will in fact be the empty string because
"'" & Me!Suffix.Value & "'" will evaluate to '' (two single quotes=empty string) when the .Value is null.  That could be a part of the problem:  your underlying table is expecting numbers or dates and you're passing strings.

And not to belabor a point:  on the NULL issue, perhaps your table IS configured to allow NULLs, but since you're not passing nulls but rather the empty string, is the table configured to allow zero length?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LenaWoodAuthor Commented:
The NZ works if I put something between the "" - like letters.  Maybe I could just put "Null"...then I would know it was null before.

Lena
0
ColosseoCommented:
That sounds like it would work but you shouldnt have to.

are the fields set to allow zero length strings?

Scott
0
stevbeCommented:
folllowing Scott's lead on field setup ... so Suffix in not Required and does Allow Zero Length correct?

I broke the sql down so it would be easier to read ... I think you need to surround Now with # instead of '. You also had -1 surrounded with ' and I don't think it needs any delimter.

strSQL = "INSERT INTO " & _
            "tblChanges " & _
               "( EmpNum, " & _
               "ChangeMade, " & _
               "MadeWhen, " & _
               "FieldOldValue, " & _
               "FieldNewValue, " & _
               "PrintCDPRCO, " & _
               "ListValue ) " & _
            "VALUES " & _
               "(' " & Me.EmpNo & "', " & _
               "'Name Changed/Updated', " & _
               "#" & Now() & "#, " & _
               "'" & Nz(Me.Suffix.OldValue, "") & "', " & _
               "'" & Nz(Me.Suffix.Value, "") & "', " & _
               "-1, " & _
               "'" & Me.CertifyingOfficial & "')"

DBEngine(0)(0).Execute strSQL, dbFailOnError

Steve
0
LenaWoodAuthor Commented:
pique,

No, my table didn't allow zero lengths for the fields....problem fixed without using NZ.  Thank you!

Going to go experiment more before making the final decision that it works!

Lena
0
pique_techCommented:
Just to follow on steveb's nicely thorough comment above, you CAN configure the field to pass null when the value is NULL rather than the empty string using Null Propagation with the "+" concatenation operator (remember this from a few days ago on another thread?).  Like so:

strSQL = "INSERT INTO " & _
            "tblChanges " & _
               "( EmpNum, " & _
               "ChangeMade, " & _
               "MadeWhen, " & _
               "FieldOldValue, " & _
               "FieldNewValue, " & _
               "PrintCDPRCO, " & _
               "ListValue ) " & _
            "VALUES " & _
               "(' " & Me.EmpNo & "', " & _         <-------------If this is really a number, take out the single quotes
               "'Name Changed/Updated', " & _
               "#" & Now() & "#, " & _
               ("'" + Me.Suffix.OldValue + "'") & ", " & _     <------These will evaluate to the NULL value and a comma
               ("'" + Me.Suffix.Value + "'") & ", " & _          <------when the record values are NULL
               "-1, " & _
               "'" & Me.CertifyingOfficial & "')"
0
LenaWoodAuthor Commented:
Pique - thanks for belaboring a point....I guess I needed a hit in the head :-)

Steve, thank you for answering a question I didn't even ask to start with.  This is an awesome place :-).

Off to find my next set of questions....stay tuned!
0
stevbeCommented:
nice catch on the +   I usually only ever think of that when I am building Address labels.

Steve
0
LenaWoodAuthor Commented:
The + is a new thing to me....must remember it :-)
0
pique_techCommented:
steveb:  address labels, and NAME SUFFIXES...   ; )

LenaWood--keep coming back, you're lots of fun to interact with.  You've a great sense of balance between "fix my problem now" and "help me understand the concepts".  Refreshing.
0
stevbeCommented:
I agree with pique ... you are a pleasure to work with  ... it is not often you will find me formatting SQL so it can actually be read :-)
0
LenaWoodAuthor Commented:
Awww you two are so nice.

If I need something done now I may stress (you know how fires grow haha) that but will always follow up with help me understand why.  No point in asking questions if you aren't willing to learn from them.  I really enjoy building databases and strive to learn something new every day.

Lena
0
stevbeCommented:
and on a general note ... I write every transaction 2 times ... 1 I let update "real" data and a second time to the audit trail table ... I use the .Fields collection of the recordset object of the form in a loop to write the audit.

Steve
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.