Solved Help with handling of Null

[allen675]

Hello,

So far with the very kind help of people of this forum website, I have pieced together the below code and it is working

I am new to VBA and although dabbled in the past never got this involved.

Essentially the code below takes information for a specific lead/customer from three separate tables. Of the three, two pull lead details and notes and transfers this information to the body of an Outlook template stored under the current project folder. The third pulls the file names stored in ContactProofT table for the same lead and attaches the proofs to the same email. This folder is also under the current project.

So the next step I would like to look at is the handling of null values for details, notes and attachments. For example if the end user speaks to the lead on the second call attempt and identifies that the client does not wish to be helped then this lead will never get to the third call attempt and so therefore the value of this field would be null. The same for the attachments, there wont always be proofs of contact to attach to an email.

Could all you forum'ers, that know what you are doing, unlike me, have a look at the code and see how we/I can accomplish this please?

Many thanks in advance

Private Sub ClientStatus_Change()
Dim sStatus As String
sStatus = Me!ClientStatus & ""
If sStatus <> "NPW - No Contact" And sStatus <> "NPW - Gone Elsewhere" And sStatus <> "NPW - Unable to Place" Then
    Exit Sub
End If
If MsgBox("Would you like to send a refund request for this lead?", vbQuestion + vbYesNo + vbDefaultButton1, "Request refund?") = vbNo Then
    Exit Sub
End If
    
Me.Refresh

Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
Dim strSQL As String
Dim clientRST As Variant
Dim salesRST As Variant
Dim strTable As String
Dim i As Variant
Dim strPaths() As String


strSQL = "SELECT [CustomerID], [Broker], [Lead_Date], [Client_FN], [Client_SN], [Email_Address], [Mobile_No], [Email_Sent], [SMS/WhatsApp_Sent], [Phone_Call_#1], [Phone_Call_#2], [Phone_Call_#3] FROM Client" _
            & " WHERE CustomerID = " & Forms!CopyExistingLeadF!CustomerID
Set clientRST = CurrentDb.OpenRecordset(strSQL)

Do While Not clientRST.EOF
    Set appOutlook = CreateObject("Outlook.application")
    Set MailOutlook = appOutlook.CreateItemFromTemplate(Application.CurrentProject.Path & "\RefundRequest.oft")
    
    strSQL = "SELECT NoteDate, Note" _
    & " FROM NoteHistory" _
    & " WHERE CustomerID = " & clientRST!CustomerID
    Set salesRST = CurrentDb.OpenRecordset(strSQL)
    
    ' TABLE COLUMNS
    strTable = "<table><th>"
    For i = 0 To salesRST.Fields.Count - 1
        strTable = strTable & "<td>" & "</td>"
    Next i
    strTable = strTable & "</th>"
    
    ' TABLE ROWS
    salesRST.MoveFirst
    While Not salesRST.EOF
        strTable = strTable & "<tr>"
        For i = 1 To salesRST.Fields.Count
            strTable = strTable & "<td>" & salesRST.Fields(i - 1).Value & "</td>"
        Next i
        strTable = strTable & "</tr>"
        salesRST.MoveNext
    Wend
    strTable = strTable & "</table>"
    salesRST.Close
    
    strSQL = "SELECT [FileName] FROM ContactProofT" _
    & " WHERE CustomerID = " & Forms!CopyExistingLeadF!CustomerID
    strPaths = Split(SimpleCSV(strSQL), ",")
    
    
    With MailOutlook
        .To = "test@test.com"
        .subject = "Refund Request"
        Dim x As Long
        For x = 0 To UBound(strPaths)
            .Attachments.Add CurrentProject.Path & "\ContactProofs\" & strPaths(x)
        Next
        
            
        ' REPLACE PLACEHOLDERS
        .HTMLBody = Replace(.HTMLBody, "%date%", clientRST![Lead_Date])
        .HTMLBody = Replace(.HTMLBody, "%first%", clientRST![Client_FN])
        .HTMLBody = Replace(.HTMLBody, "%surname%", clientRST![Client_SN])
        .HTMLBody = Replace(.HTMLBody, "%mobile%", clientRST![Mobile_No])
        .HTMLBody = Replace(.HTMLBody, "%email%", clientRST![Email_Address])
        .HTMLBody = Replace(.HTMLBody, "%Call1%", clientRST![Phone_Call_#1])
        .HTMLBody = Replace(.HTMLBody, "%Call2%", clientRST![Phone_Call_#2])
        .HTMLBody = Replace(.HTMLBody, "%Call3%", clientRST![Phone_Call_#3])
        .HTMLBody = Replace(.HTMLBody, "%unsuccessful%", clientRST!Email_Sent)
        .HTMLBody = Replace(.HTMLBody, "%message%", clientRST![SMS/WhatsApp_Sent])
        .HTMLBody = Replace(.HTMLBody, "%Broker%", clientRST![Broker])
        
        
        ' ADD SALES TABLE
        .HTMLBody = Replace(.HTMLBody, "%Notes%", strTable)
        
        .Display
    
    End With
    
    
    Set MailOutlook = Nothing
    clientRST.MoveNext
Loop
clientRST.Close
Set clientRST = Nothing
DoCmd.Close acForm, "CopyExistingLeadF"

End Sub

 

[The_Doc_Man]

The way you described this makes me pause. You said:

For example if the end user speaks to the lead on the second call attempt and identifies that the client does not wish to be helped then this lead will never get to the third call attempt and so therefore the value of this field would be null.

In a properly normalized database, there IS no third call attempt slot until someone actually MAKES a third attempt. If you have a fixed number of call slots, your DB is not normalized with respect to call slots. You should have a child table of call slots that has EXACTLY as many records as calls that were made. Your stats would be based on the child record with the earliest and the latest calls and a count of all calls. If your client calls 50 times, you 50 records. If your client never calls, you have no records.

Your more immediate problem would be that if your client declines further assistance, you would have to post a code that says "Declines assistance" (using whatever term you normally would use). After that (which SHOULD close the case), trigger a query or other action to load up a code for the extra slots AND load the time of the closure call. OR you can use the NZ function to test for nulls and let NZ load up some useful, meaningful, unequivocal value that applies in such cases.

 

[Pat Hartman]

Following Doc's advice will require changing the code that builds the message. The part where you populate the three calls would need to become a loop. Not sure how that would affect your template since I've never used one. If you can't make the template variable, then you need to figure out how you can communicate "more" as an option.

You can still normalize your schema since that is always right. You can then use a crosstab query to flatten it so you get the three + calls in a single row. The crosstab query wizard only allows you to pick three fields. You can fix this later by adding additional columns or joining to another table in the query.

In the relational world, there is never three of anything. Once you have more than one of something, you have many and many requires a child table.

 

[allen675]

Hi

Everything that you have said makes absolute sense and I do have child tables within my DB for example with notes, however, our sales process is for three calls max, one email and one message/WhatsApp and that's how I've built the lead input form & table.

If value is null for placeholders then I was thinking sometime along the lines of is null then value " " because it doesn't matter if the placeholders are blank

 

[GPGeorge]

Hi

Everything that you have said makes absolute sense and I do have child tables within my DB for example with notes, however, our sales process is for three calls max, one email and one message/WhatsApp and that's how I've built the lead input form & table.

If value is null for placeholders then I was thinking sometime along the lines of is null then value " " because it doesn't matter if the placeholders are blank

Your sales process is how you do things today. There's no guarantee that process will remain the same forever. Don't get locked into a design based on assumptions that can, and usually do, change over time.

Please take the advice regarding inappropriate table design seriously.

Not only that, the only reason we're discussing the problem of Null placeholders at all is that the table design CURRENTLY isn't even appropriate. It forces you to deal with something (the missing third call) that wouldn't come up in a properly normalized table design. Already, you're incurring extra work unnecessarily.

 

[June7]

If you want to substitute Null with empty string, try Nz(fieldname, "") or fieldname & ""

I don't disagree with the advice for normalization but db design also involves a balancing act between normalization and ease of data entry/output.
"Normalize until it hurts, denormalize until it works."

 

[allen675]

@June7 thank you for your input and defence having said that what the other commenters don't know yet, as I have not explained this, is that elsewhere in the DB this information populates a spreadsheet with defined call attempt cells which then gets fired off to the directors. I am sure there is some way of writing code to manipulate and format a spreadsheet but I am doing my best to shoehorn this database into a format they want and already use and following a sales process which hasn't changed in 20+ years(but never say never, I suppose it could). I don't disagree if I had my way I would have separate child table for the call attempts as I do with Notes & the file attachments which of course allow for unlimited records and avoid trying to work around this problem.

Not being an experienced DB designer or coder, would you be so kind to show me an example of the above ideas you have provided in the code that I have provided please?

 

[Pat Hartman]

our sales process is for three calls max

If you believe that, I have a bridge in Brooklyn for sale

Once you have more experience with design and development, you will look at this statement for what it is and then take appropriate defensive programming steps to allow the user to violate.

Note for those not from the tri-state area who might not have heard the statement used.

The Brooklyn Bridge — “If you believe that, I have a bridge in Brooklyn to sell to you”

Ever since I was a young person, I have used the phrase, “If you believe that, I have a bridge in Brooklyn to sell to you”. The phrase originated because for over a century people have fallen for the con that New York City is…

nycwalks.com

 

[allen675]

@Pat Hartman you see the things is I have an advantage over you, I actually work for the organisation I am putting this together for and our sales process is three calls so its not about belief, its a fact! Anyhow this isn't about how the business is run its about working with what I have been given and finding a solution.

I'm not planning on gaining any further experience but should I decide to I will ask someone else ;-)

Thanks for your input, it was very useful!

 

[Pat Hartman]

I didn't say that you misunderstood the requirements as described to you. I guess the sales process will never change Because businesses NEVER, EVER change procedures. So, you go right ahead and hardcode whatever you want to. Your successor will likely be the one to deal with your failure to take a simple safety play.

Feel free to ignore me in the future if that makes you feel good

 

[allen675]

Well that's why I'm here Pat for the code, not business advice. Should things change and I decide to leave the business well then that's not my problem is it! Sorry but my crystal ball fell out of the car this morning while I was crossing the Brooklyn Bridge and broke so I cant predict the future anymore and I suspect neither can you.

 

[MarkK]

 

[allen675]

Feel free to ignore me in the future if that makes you feel good

I think you'll find Pat, that I have agreed with you so not ignoring you. Just looking for a solution for what I have rather than re-inventing the wheel.

 

[Pat Hartman]

Should things change and I decide to leave the business well then that's not my problem is it!

Hope your employer never sees this. You do know that nothing ever goes away from the internet, right?

 

[allen675]

I'm self employed Pat so wouldn't make any difference and anyhow if they saw it probably wouldn't bother them anyway.

Good comparison aircraft pilot and db designer I can see the similarities

Just because someone doesn't want to do it a certain way shouldn't open them up to abuse, which is exactly what has happened here.

I've said I agree with you but that's not what is wanted and asked for your help to resolve what I have.

What exactly do you want from me Pat? Do you want me to say okay I'll get the directors to change the sales process to fit the db or do you want me to put something into a db that we don't need or want because it might upset those that I've asked for help?

 

[GPGeorge]

I'm self employed Pat so wouldn't make any difference and anyhow if they saw it probably wouldn't bother them anyway.

Good comparison aircraft pilot and db designer I can see the similarities

Just because someone doesn't want to do it a certain way shouldn't open them up to abuse, which is exactly what has happened here.

I've said I agree with you but that's not what is wanted and asked for your help to resolve what I have.

What exactly do you want from me Pat? Do you want me to say okay I'll get the directors to change the sales process to fit the db or do you want me to put something into a db that we don't need or want because it might upset those that I've asked for help?

Sorry you feel it is abuse. It can feel that way, I'm sure, when someone tells you that you've invested a huge amount of time in a less than optimal design. You have done that, though, and telling you it's a less than optimal design is not intended to be hurtful; it's intended to help you correct the problem.
You might get a kick out of this blog post, or it might just tick you off. Pay particular attention to the third point about "Practically Perfect".

 

[allen675]

@GPGeorge well worded and a much better approach

I am upset with the approach not by the fact that I've been told its not a perfect design after all on several occasions I have admitted I agree. I have what I have and can only work with what I am given. I am not going to do something in a way that is not asked. That may be something a professional DB designer/company would do and highlight the design pitfalls after all that's what they are paid to do.

There is no need for pointless links to fruitless information about bridges, from someone who thinks their wit is clever, and certainly no need for pictures suggesting that I am telling someone how to do their job. Its childish, unnecessary and quit frankly rude.

Its a three call process always has been and for the foreseeable future will be, I CANT CHANGE THAT. The DB elsewhere populates THEIR spreadsheet which has three cells, Call 1, Call 2 and Call 3 and will happily do so even with null values. I now would like help, not criticism, to put in place to deal with the null values in the code provided in the initial post, can this be done yes or no. If it can then great please show me, if not then I will not proceed any further and tell them I cant help.

 

[GPGeorge]

@GPGeorge well worded and a much better approach

I am upset with the approach not by the fact that I've been told its not a perfect design after all on several occasions I have admitted I agree. I have what I have and can only work with what I am given. I am not going to do something in a way that is not asked. That may be something a professional DB designer/company would do and highlight the design pitfalls after all that's what they are paid to do.

There is no need for pointless links to fruitless information about bridges, from someone who thinks their wit is clever, and certainly no need for pictures suggesting that I am telling someone how to do their job. Its childish, unnecessary and quit frankly rude.

Its a three call process always has been and for the foreseeable future will be, I CANT CHANGE THAT. The DB elsewhere populates THEIR spreadsheet which has three cells, Call 1, Call 2 and Call 3 and will happily do so even with null values. I now would like help, not criticism, to put in place to deal with the null values in the code provided in the initial post, can this be done yes or no. If it can then great please show me, if not then I will not proceed any further and tell them I cant help.

We're getting closer to the point. The PROCESS is three calls. True. That doesn't mean you design the table exactly the way you design a spreadsheet. And that's where the problem comes in. Three fields in an Access relational database table labelled Call1, Call2, Call3 is the problem. That's a spreadsheet design. That is not a relational database design. The fact that there are three calls is not the key to getting it right. The fact is that Access--and all other relational databases--work best when you create a table like this:

tblContact
=======
PrimaryKey
ProspectID -- Foreign key to the Prospect table
ContactDate
ContactType -- one of three currently

Each time you make a contact, you add a row with the date and type (call, email or DM). The table can have from 0 to 6 rows for each Prospect. You do not have to enter a record until you actually make that contact. You can add a constraint to limit calls per Prospect to no more than three and emails to no more than two, or whatever the rule is.

Unfortunately, the current method (modelled after spreadsheets) has a design that FORCES you to account for all five six contacts, one way or the other. Either you enter something or there is a null in an existing field in that Prospect record. And that, in turn, adds the complication of having to check for those nulls.

tblProspect
========
ProspectID
Call1
Call2
Call3
Email1
Email2
DM

The reason the problem here exists has nothing directly to do with the business process. It has to do with the table design. Related but different things. You don't have to change the business model to support the table, no. You should change the table design to more closely align with the relational database model, as opposed to the spreadsheet model.

You don't have to do it. More than once over the years I've seen cases where there is so much data invested in a less-than-appropriate table design that a full re-factoring is more work than simply putting a little band-aid on it.

The beauty of the relational database design is that no extra coding has to happen to account for tasks that are allocated, but not completed. All you have to do is add the task when it occurs, Done.

Again, no one intentionally tried to abuse anyone. It's a matter of combined multiple decades of experience here. We've seen this exact problem dozens of times. We probably aren't as patient as we should be when we see it yet again. That's not your fault.

 

[allen675]

@GPGeorge Thank you all makes sense. I will have a re look at the code I have elsewhere i.e. the code that populates the spreadsheet and see if this can be changed to work with a new child table. If it involves more work than I was expecting then I will just scrap the whole thing.

Can't understand why no one will help me with the code I have really can't understand that.

 

[GPGeorge]

@GPGeorge

Spoiler: etc

Thank you all makes sense. I will have a re look at the code I have elsewhere i.e. the code that populates the spreadsheet and see if this can be changed to work with a new child table. If it involves more work than I was expecting then I will just scrap the whole thing.

Can't understand why no one will help me with the code I have really can't understand that.

In that blog post I linked, one of the points I raised is exactly this one.