Call Tracker MS Access Template

[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