Call Tracker MS Access Template

[terryechols1]

I'm working from MS Access "Call Tracker" template and I need some help.

What I'm Using:
MICROSOFT OFFICE 365
ACCESS 2016 MSO (16.0.6965.2053) 32-bit

On the form "Call Details" I need to add two extra fields (4 total but the other two are simple text fileds): Under "Called In By" I need to add "Called In By Phone" and "Bill To" both of which are stored in both "Customers" and "Customers Extended" tables - [Customers Extended] is the SQL for the combo box source. The source for the form is [Calls].

These three fields: [Called In By], [Called In by Phone], and [Bill To] I need to operate as one - meaning when a caller is selected the other two fields are populated automatically.

I did manage to get the two boxes pre-filled using the code below but it does not use any IF logic. I'm not sure where to put the IF logic - here in the VBA or in the SELECT statement for the cbobox (shown below).

Private Sub cboCalledInBy_AfterUpdate()
   Me.txtCalledInByPhone = Me![cboCalledInBy].Column(3)
   Me.txtBillTo = Me![cboCalledInBy].Column(2)
End Sub
 

Here is the full scenario:

Customer calls in, employee taking the phone call clicks on the [cboCalledInBy] and selects a name from the list. I want the two remaining fields ([txtCalledInByPhone] & [txtBillTo]) to be filled in automatically but with IF logic.

If the person selected has a [Mobile Phone] entry in their record then [txtCalledInByPhone] should use that if not then use [Business Phone] and if both are blank then return empty. Then for the "Bill To" if the record selected as [cboCalledInBy] has a [Company] name then [txtBillTo] should fill in with it but if they don't I want it to fill in with the callers name that was first selected [cboCalledInBy].

The other two boxes are just text boxes for a Tenant Name and Number, nothing special for them.

The SELECT statement for the [cboCalledInBy] filed is:

SELECT [ID], [Customer Name], [Company], [Business Phone], [Mobile Phone] 
FROM [Customers Extended] 
ORDER BY [Company];
 

This raises the question: Customer Name becomes column 1, Company becomes column 2, Business Phone becomes column 3 and Mobile Phone becomes column 4. These are used in the VBA code shown above.

I suspect that the IF logic would have to be done on the STATEMENT so the column numbers in the VBA would be correct, I think?

The more I look for ways to accomplish this, the more I am lost.

Please Help!
Terry Echols

[jforbes]

Hopfully there isn't a typo in the following as I didn't use Access to help format or test this code, but this is representative of what I would do:

Private Sub cboCalledInBy_AfterUpdate()
   If Len(Nz(Me.txtCalledInByPhone.Value, "") = 0 Then
      Me.txtCalledInByPhone = Me![cboCalledInBy].Column(3)
   End If
 
   If Len(Nz(Me.txtBillTo.Value, "") = 0 Then
      Me.txtBillTo = Me![cboCalledInBy].Column(2)
   End If
End Sub

The above code should test to see if the fields are filled in first, and if they aren't it will populate them from values in the ComboBox.

[terryechols1]

@jforbes
I got a Compile error (Syntax Error).

If Len(Nz(Me.txtCalledInByPhone.Value, "") = 0 Then
If Len(Nz(Me.txtBillTo.Value, "") = 0 Then 

Both show in red.

I added the missing closing ")" to the end of the line to read:

If Len(Nz(Me.txtCalledInByPhone.Value, "")) = 0 Then
If Len(Nz(Me.txtBillTo.Value, "")) = 0 Then 

This seems to be working but I have a question for you. If the operator makes a mistake in the selection process then attempts to choose the correct CalledInBy customer from the list, the other two txt fields don't change.

Example:
First time it works - all information is pulled correctly from the Query and two txt fields are pre-filled correctly.

But:
If they try to change the CalledInBy to another person it does not change the other two values.

Also, on a different note. What we are working on - when they are in Form View - [cboCalledInBy] is a dropdown box showing the Customer Name and Company (column 1 and column 2). Is there a way to make them both work as searchable? Meaning - as it is now - if they know the persons name (i.e. Customer Name) they can just start typing it and it will pull the list to that name. I'd like them to be able to do that with the Company name as well. The SELECT statement is bound to column 1 which is Customer Name - any way to make column 1 & 2 work in this manner?

Thanks,
Terry Echols

P.S. Thanks for this code I have been going nuts reading everything I could to find out how to accomplish this to no avail so much much appreciated.

[jforbes]

Both of those points can be addressed, but they are going to get a little tricky.

There are a couple approaches to your first point which boils down to only using the values from the ComboBox if the current TextBox has no value or if the Value matches what was previously selected in the ComboBox. Why this gets tricky, is that your user could flip around and select an endless amount or records on the Combobox and there isn't a built in method in Access to track all the different Values that are selected. There is an .OldValue property that could work here, but I don't think it will as the .OldValue only updates on the Save of a record and the User could select a value in the ComboBox multiple times before the .OldValue gets updated, which would make the .OldValue unreliable. So a slightly more complex way is to create some public variables to hold the current ComboBox values and update them when ever the value changes. Then the Public variables can be referred to, to decide on what to do.

Again, there maybe errors with the code, so you may have to debug it. This would be the code for the Form if I were to do it. Note the top two lines are Form level variable declarations:

Private mCalledInByPhone As String
Private mBillTo As String
 
Private Sub cboCalledInBy_AfterUpdate()
 
    Dim sCalledInByPhone As String
    Dim sBillTo As String
 
    sCalledInByPhone = Nz(Me.txtCalledInByPhone.Value, "")    
    If mCalledInByPhone = sCalledInByPhone Or Len(sCalledInByPhone ) = 0 Then
        Me.txtCalledInByPhone.Value = Me.cboCalledInBy.Column(3)
    End If
 
    sBillTo = Nz(Me.txtBillTo.Value, "")    
    If mBillTo = sBillTo Or Len(sBillTo ) = 0 Then
        Me.txtBillTo.Value = Me.cboCalledInBy.Column(2)
    End If
 
    Call SaveCurrentCalledInValues
 
End Sub
 
Private Sub Form_Current()
    Call SaveCurrentCalledInValues
End Sub
 
Private Sub SaveCurrentCalledInValues()
    mCalledInByPhone = Nz(Me.cboCalledInBy.Column(3), 0)
    mBillTo = Nz(Me.cboCalledInBy.Column(2)"")
End Sub

So in the above the method SaveCurrentCalledInValues saves off the current values of the ComboBox into Form level variables to that previous values are readily available after the ComboBox's value is changed. This save was put into it's own method, so that it can be called in multiple places, like the OnCurrent event as well as the AfterUpdate.

Again, you may have to tweak the code to get it working.

To your second point, that is a big one and if you need to discuss it much, you should start a new thread as we like to keep threads here to one topic because it makes for clean and easy to read threads. This is a similar question for your second point, you may want to look at it to see if it gives you any ideas: choose item in a ComboBox as we Type the contained text...

[terryechols1]

Hi:

I've finally had some time to put this code to the test and have an issue I need help with.

The current code:

Private mCalledInByPhone As String
Private mBillTo As String
 
Private Sub cboCalledInBy_AfterUpdate()
 
    Dim sCalledInByPhone As String
    Dim sBillTo As String
 
    sCalledInByPhone = Nz(Me.txtCalledInByPhone.Value, "")
    If mCalledInByPhone = sCalledInByPhone Or Len(sCalledInByPhone) = 0 Then
        Me.txtCalledInByPhone.Value = Me.cboCalledInBy.Column(3)
    End If
 
    sBillTo = Nz(Me.txtBillTo.Value, "")
    If mBillTo = sBillTo Or Len(sBillTo) = 0 Then
        Me.txtBillTo.Value = Me.cboCalledInBy.Column(2)
    End If
 
    Call SaveCurrentCalledInValues
 
End Sub
 
Private Sub Form_Current()
    Call SaveCurrentCalledInValues
End Sub
 
Private Sub SaveCurrentCalledInValues()
    mCalledInByPhone = Nz(Me.cboCalledInBy.Column(3), 0)
    mBillTo = Nz(Me.cboCalledInBy.Column(2), "")
End Sub

What is supposed to happen is the CalledInByPhone and BillTo are based on the cboCalledInBy. The select statement is:

SELECT [ID], [Customer Name], [Company], [Business Phone], [Mobile Phone] FROM [Customers Extended] ORDER BY [Customer Name]; 

Making it Customer Name (col 1), Company (col 2) and here is the tricky part, to use col 3 as it is now is using Business Phone. I need it to use if logic to select the stored value. I need it to use Mobile Phone is there is one stored for the customer if not use Business Phone.

The code now is only pulling Business Phone (col 3). What would I need to do to either the select statement or the VBA code to make it use the if/then or logic?

Thanks,
Terry Echols

[jforbes]

This might do it for you, depending on how your data is structured, if you have Blanks instead of Nulls in your Phone Numbers, the NZ will need to be changed to an If Statement or an IIF().

The idea behind this is to use Column 4 instead of Column 3 whenever Column 3 is Null. There are two places where Column 3 is used, so they would both need to test for Nulls.

Private mCalledInByPhone As String
Private mBillTo As String
 
Private Sub cboCalledInBy_AfterUpdate()
 
    Dim sCalledInByPhone As String
    Dim sBillTo As String
 
    sCalledInByPhone = Nz(Me.txtCalledInByPhone.Value, "")
    If mCalledInByPhone = sCalledInByPhone Or Len(sCalledInByPhone) = 0 Then
       Me.txtCalledInByPhone.Value = Nz(Me.cboCalledInBy.Column(3), Me.cboCalledInBy.Column(4))
    End If
 
    sBillTo = Nz(Me.txtBillTo.Value, "")
    If mBillTo = sBillTo Or Len(sBillTo) = 0 Then
        Me.txtBillTo.Value = Me.cboCalledInBy.Column(2)
    End If
 
    Call SaveCurrentCalledInValues
 
End Sub
 
Private Sub Form_Current()
    Call SaveCurrentCalledInValues
End Sub
 
Private Sub SaveCurrentCalledInValues()
    mCalledInByPhone = Nz(Me.cboCalledInBy.Column(3), Me.cboCalledInBy.Column(4))
    mBillTo = Nz(Me.cboCalledInBy.Column(2), "")
End Sub

[terryechols1]

There are going to be null values in both business and mobile phone fields.

Which Nz statements will have to be changed to IIf?

And one more question has arisen now that I'm implementing the system.

When a customer does not have a company name I'd like the txtBillTo to use the cboCalledInBy name, how would I do that exactly?

I'm good, usually, at re-working code but designing it is another animal altogether.

[jforbes]

You'll probably be fine with the NZ() method as it sounds like that is what you are using. Just for clarification on the Null vs Blank scenario, a little light reading: What is the difference between "" and Null?


The following code is pretty similar but the First Line is expecting Nulls, Line 3 would also handle Blanks. Why wouldn't you just use the Line 3? ... in this case it is just a preference really, but sometimes it does matter.

= Nz(Me.cboCalledInBy.Column(3), Me.cboCalledInBy.Column(4))
to
= Iff(Len(Me.cboCalledInBy.Column(3)) > 0, Me.cboCalledInBy.Column(3), Me.cboCalledInBy.Column(4))

When a customer does not have a company name I'd like the txtBillTo to use the cboCalledInBy name, how would I do that exactly?

I thought that scenario was already being addressed. Are you asking to do something that isn't being done already?

[terryechols1]

I am so lost in trial and error. There are two instances of

Nz(Me.cboCalledInBy.Column(3), Me.cboCalledInBy.Column(4))

One in the code for [cboCalledInBy_AfterUpdate()] and one in the [Private Sub SaveCurrentCalledInValues()]

Do I change them both? I did but got errors. When I used the new IIF code in the Sub for SaveCurrent... I got runtime error 94, invalid use of NULL.

I have tried the new code in the SaveCurrent... Sub and not in the AfterUpdate... and get error 94; if I reverse that I get no errors but the code isn't working.

This is the code that is running now, it throws no errors but doesn't work:

Private mCalledInByPhone As String
Private mBillTo As String
 
Private Sub cboCalledInBy_AfterUpdate()
 
    Dim sCalledInByPhone As String
    Dim sBillTo As String
 
    sCalledInByPhone = Nz(Me.txtCalledInByPhone.Value, "")
    If mCalledInByPhone = sCalledInByPhone Or Len(sCalledInByPhone) = 0 Then
       Me.txtCalledInByPhone.Value = Nz(Me.cboCalledInBy.Column(3), Me.cboCalledInBy.Column(4))
    End If
 
    sBillTo = Nz(Me.txtBillTo.Value, "")
    If mBillTo = sBillTo Or Len(sBillTo) = 0 Then
        Me.txtBillTo.Value = Nz(Me.cboCalledInBy.Column(2), Me.cboCalledInBy.Column(1))
 
    End If
 
    Call SaveCurrentCalledInValues
 
End Sub
 
Private Sub Form_Current()
    Call SaveCurrentCalledInValues
End Sub
 
Private Sub SaveCurrentCalledInValues()
    mCalledInByPhone = IIf(Len(Me.cboCalledInBy.Column(3)) > 0, Me.cboCalledInBy.Column(3), Me.cboCalledInBy.Column(4))
    mBillTo = IIf(Len(Me.cboCalledInBy.Column(2)) > 0, Me.cboCalledInBy.Column(2), Me.cboCalledInBy.Column(1))
End Sub

I've tried moving things around, adding watches and generally tried to "watch/see" what the code is doing to no avail. I'm thoroughly lost now.

The code above doesn't work the way I need it to and can't figure out why. On the surface it appears it would be doing what it should but it's not.

As to the txtBillTo being filled in if Company is NULL/Blank that isn't working either with the new or the old code.

I'm so lost now.

Terry

[jforbes]

How about this then, I went and created an example database. In it is the example we have been working with.

While I was creating it, I realized that you might not want what we were working on. ... That I might have not fully understood what you were attempting to do. There are a lot of ways that stuff like what we are doing can be accomplish and for different goals. The goal I originally understood and which is quite possibly what you are after is to save off the values of the Customer along with the call. This is a common practice, that way if the phone numbers or addresses change over time, then there is a record of what was used. This is often done in Invoicing so that there is a complete record of who and what was invoiced. So in the Example Database, this approach is utilized on the "CallDetails_SaveAllDetails" Form.

The other common approach is utilizing full normalization by only storing the Key to the Customer Table and then only displaying the other information from the Customer on the Form. I made up one of this style in the Form named "CallDetails_JustTheBasics". It uses unbound controls to display the other information from the Customer table.

[terryechols1]

Mr Forbes:

You are correct in that I don't want the "Customer/Customer Extended" table data changed only retrieved. The customer info should stay the same in those tables but the calls details written/stored in the "Calls" table can be different.

I tried the new code this morning "SaveAllDetails" but I'm getting error 94 and it stops on this line:

mCalledInByPhone = IIf(Len(Nz(Me.cboCalledInBy.Column(3), "")) > 0, Nz(Me.cboCalledInBy.Column(3), ""), Me.cboCalledInBy.Column(4))

Thank you so much for the help, BTW. It is much appreciated.

Terry

[jforbes]

I think if you replace the line with the following it should work:

mCalledInByPhone = IIf(Len(Nz(Me.cboCalledInBy.Column(3), "")) > 0, Nz(Me.cboCalledInBy.Column(3), ""), Nz(Me.cboCalledInBy.Column(4),""))

I'm not a fan off IIf() statements, but it seemed like the best fit for what is going on here. If I remember correctly, one of the drawbacks of an IIF() is that the entire line is evaluated, so if a Null shows up for any part of an IIF() it can wreak havoc on the entire line.

You may already know this, but if you run into something like this and want to troubleshoot it, you can place a breakpoint on the line, by right-clicking it and selecting Toggle|Breakpoint. Then you can use the mouse to hover over the line and see what values the different parts hold. My guess in this case if you hovered over the last part it would tell you it's a Null. Debugging is a fairly deep subject with a steep learning curve, but it is a valuable tool. If your interested in it, this link is a good place to start: Debugging in VBA

[terryechols1]

Thanks for the Debugging info I have tried to find good resources on that but came up short. I'll take an in-depth look at the info at that link when I have more time.

An update:
When I first implemented the code the first thing I need to do was search for an address, that is when the error was thrown. But I have been using the code for a couple of hours now and have not had another issue - YET.

I can't tell you how much I appreciate your help, information and code. Thank you.

Terry

[terryechols1]

Update:

I was wrong about the error 94. It gets thrown ONLY when I add a new customer.

This form works by a pop up message when a name is typed in for Called In By that is not in the table asking if we want to add it. Say yes and the Customer Details form opens with the name prefilled. After adding all the customer details and closing the customer form the cursor goes back to the Called In By field, after hitting tab is when the error is thrown - BUT - it is only thrown on the Mobile Phone entry. If I add the number to the Business Phone field no error is thrown only if I add the number to Mobile Phone is it a problem.

Any idea on this one? This is the only issue I can find after a few hours or working with this code.

Terry

[terryechols1]

Update:

I spoke too soon. All is working EXCEPT where a Mobile Phone is used. If there is a Business Phone no problems what so ever. The 94 error is thrown when either the situation I described above takes place, or, as I just found out, when I tried to update a call record to a different customers that, of course, has only a mobile phone listed, the error was thrown again.

So, as you say, you don't like using the IIF statements, I concur now, what would be an alternative to use to accomplish my goals?

In a nutshell, it seems to throw the 94 error on any customers that has ONLY a mobile phone listed in the Customers table, or as I'm using, the Customer Extended Query.

Terry

[jforbes]

I have a feeling that it's missing some NZ()

Maybe this would work?

Option Compare Database
Option Explicit
 
Private mCalledInByPhone As String
Private mBillTo As String
 
Private Sub cboCalledInBy_AfterUpdate()
 
    Dim sCalledInByPhone As String
    Dim sBillTo As String
 
    If Nz(Me.cboCalledInBy.Value, 0) > 0 Then
        sCalledInByPhone = Nz(Me.txtCalledInByPhone.Value, "")
        If mCalledInByPhone = sCalledInByPhone Or Len(sCalledInByPhone) = 0 Then
           Me.txtCalledInByPhone.Value = IIf(Len(Nz(Me.cboCalledInBy.Column(3), "")) > 0, Nz(Me.cboCalledInBy.Column(3), ""), Nz(Me.cboCalledInBy.Column(4), ""))
        End If
 
        sBillTo = Nz(Me.txtBillTo.Value, "")
        If mBillTo = sBillTo Or Len(sBillTo) = 0 Then
            Me.txtBillTo.Value = Nz(Me.cboCalledInBy.Column(2))
        End If
    Else
        Me.txtCalledInByPhone.Value = ""
        Me.txtBillTo.Value = ""
    End If
 
    Call SaveCurrentCalledInValues
 
End Sub
 
Private Sub Form_Current()
    Call SaveCurrentCalledInValues
End Sub
 
Private Sub SaveCurrentCalledInValues()
    If Nz(Me.cboCalledInBy.Value, 0) <> 0 Then
        mCalledInByPhone = IIf(Len(Nz(Me.cboCalledInBy.Column(3), "")) > 0, Nz(Me.cboCalledInBy.Column(3), ""), Nz(Me.cboCalledInBy.Column(4), ""))
        mBillTo = Nz(Me.cboCalledInBy.Column(2), "")
    Else
        mCalledInByPhone = ""
        mBillTo = ""
    End If
End Sub

[jforbes]

Oh, I forgot to address your question about alternatives to IIF().

IIF() is great to use here and there as you can compact a thought onto one line in VBA. It also has it's uses in SQL and Expressions

But, the following is functionally equivalent to the previous versions, and bit easier to read and troubleshoot:

Option Compare Database
Option Explicit
 
Private mCalledInByPhone As String
Private mBillTo As String
 
Private Sub cboCalledInBy_AfterUpdate()
 
    Dim sCalledInByPhone As String
    Dim sBillTo As String
    Dim sPhone As String
    Dim sMobile As String
 
    If Nz(Me.cboCalledInBy.Value, 0) > 0 Then
        sCalledInByPhone = Nz(Me.txtCalledInByPhone.Value, "")
        If mCalledInByPhone = sCalledInByPhone Or Len(sCalledInByPhone) = 0 Then
            sPhone = Nz(Me.cboCalledInBy.Column(3), "")
            sMobile = Nz(Me.cboCalledInBy.Column(4), "")
            If Len(sPhone) > 0 Then
                Me.txtCalledInByPhone.Value = sPhone
            Else
                Me.txtCalledInByPhone.Value = sMobile
            End If
        End If
 
        sBillTo = Nz(Me.txtBillTo.Value, "")
        If mBillTo = sBillTo Or Len(sBillTo) = 0 Then
            Me.txtBillTo.Value = Nz(Me.cboCalledInBy.Column(2))
        End If
    Else
        Me.txtCalledInByPhone.Value = ""
        Me.txtBillTo.Value = ""
    End If
 
    Call SaveCurrentCalledInValues
 
End Sub
 
Private Sub Form_Current()
    Call SaveCurrentCalledInValues
End Sub
 
Private Sub SaveCurrentCalledInValues()
 
    Dim sPhone As String
    Dim sMobile As String
 
    If Nz(Me.cboCalledInBy.Value, 0) <> 0 Then
        sPhone = Nz(Me.cboCalledInBy.Column(3), "")
        sMobile = Nz(Me.cboCalledInBy.Column(4), "")
        If Len(sPhone) > 0 Then
            mCalledInByPhone = sPhone
        Else
            mCalledInByPhone = Me.txtCalledInByPhone.Value = sMobile
        End If
        mBillTo = Nz(Me.cboCalledInBy.Column(2), "")
    Else
        mCalledInByPhone = ""
        mBillTo = ""
    End If
 
End Sub
 

[terryechols1]

I tried both sets of code on the same 4 customers for testing. I selected customers with:

Mobile Phone Only w/company - pulls company but not phone
Both Mobile & Business Phone w/company - pulls business phone and company
Mobile Only no/company - pulls no phone number
Both Mobile & Business Phone no/company - pulls business phone

Both sets of code does the same thing - it won't pull the mobile number. Same if I create a new customers from the call details form. If I create a new call and type a name not in the customers table it takes me to the customer details page to create them. If only a mobile phone is entered the call details form doesn't use it.

I poured over both sets of code and can't see why it's not pulling the mobile phone.

Neither sets of code is throwing the 94 error again - that's good. Now that damn pesky mobile phone...

Thanks,
Terry

[terryechols1]

I played around with debugging and it appears to me that the mobile phone is being completely ignored. Every value is NULL when you hover over the variable where mobile phone should be pulled. I don't exactly understand why.

Terry

[jforbes]

Sounds like progress.

Things to check:

• Make sure the Mobile Number is visible in the dropdown list for cboCalledInBy
• Make sure the .ColumnCount for cboCalledInBy is set to 5. (This needs to be equal to the number of columns for your ComboBox)
• Make sure the Mobile Number is included in the RowSource of cboCalledInBy

[terryechols1]

It's working but I do want to make one change. I want to pull the Mobile Phone by default so IF a customer has both I'd like to pull Mobile. It's working now except for those customers that have both. It pulls the Business but I want it to pull the Mobile (if one exist).

I had the column count as 3 to hide the phone numbers, I didn't know that that would block the VAB code from working on those columns. I have them all defined now and simply used 0" to still hide them.

Getting there.

Thanks

[jforbes]

Yeah, understanding how Access implements the ComboBox takes a little while and some trial an error.

To make the Mobile Number the Default, all you should have to do is change this:

If Len(sPhone) > 0 Then

to:

If Len(sMobile) = 0 Then

in two places.

[terryechols1]

I had thought about that change but wasn't entirely sure if that would do it. Glade to know I was on the right track though.

Yay! Brilliant. I can't thank you enough.

I will be posting a new thread for a separate issue. I hope you'll chime in on it once I get it posted. Although, you may want to set up the Access Call Tracker template for this one.

I would compact and send you my database that we've been working on but I don't know how.

Thanks again.
Terry