?
Solved

Doesn't Execute if OldValue or Value is Null

Posted on 2004-11-11
23
Medium Priority
?
473 Views
Last Modified: 2008-03-10
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
0
Comment
Question by:LenaWood
  • 9
  • 8
  • 4
  • +1
23 Comments
 
LVL 15

Expert Comment

by:Colosseo
ID: 12558023
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
 
LVL 39

Expert Comment

by:stevbe
ID: 12558052
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
 
LVL 10

Author Comment

by:LenaWood
ID: 12558147
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Expert Comment

by:pique_tech
ID: 12558152
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
 
LVL 39

Expert Comment

by:stevbe
ID: 12558204
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
 
LVL 10

Author Comment

by:LenaWood
ID: 12558229
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
 
LVL 10

Author Comment

by:LenaWood
ID: 12558256
I am not sure how to use dbFailOnError...please explain :-)
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 200 total points
ID: 12558270
.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
 
LVL 39

Expert Comment

by:stevbe
ID: 12558308
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
 
LVL 10

Author Comment

by:LenaWood
ID: 12558359
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
 
LVL 12

Accepted Solution

by:
pique_tech earned 1800 total points
ID: 12558394
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
 
LVL 10

Author Comment

by:LenaWood
ID: 12558400
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
 
LVL 15

Expert Comment

by:Colosseo
ID: 12558466
That sounds like it would work but you shouldnt have to.

are the fields set to allow zero length strings?

Scott
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12558470
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
 
LVL 10

Author Comment

by:LenaWood
ID: 12558513
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12558595
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
 
LVL 10

Author Comment

by:LenaWood
ID: 12558674
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
 
LVL 39

Expert Comment

by:stevbe
ID: 12558682
nice catch on the +   I usually only ever think of that when I am building Address labels.

Steve
0
 
LVL 10

Author Comment

by:LenaWood
ID: 12558723
The + is a new thing to me....must remember it :-)
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12558818
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
 
LVL 39

Expert Comment

by:stevbe
ID: 12558967
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
 
LVL 10

Author Comment

by:LenaWood
ID: 12559421
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
 
LVL 39

Expert Comment

by:stevbe
ID: 12563857
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question