Call Tracker MS Access Template

Terry Echols

New Member
Jul 14, 2015
I'm working on a tracker for my service desk calls and came across the MS Access Call Tracker. I have managed to get virtually everything working almost the way I need but need some help.

I wanted to upload a zip file with sample data but can't find how to upload files here on this forum.

Here are the details for my request:

ACCESS 2016 MSO (16.0.6965.2053) 32-bit


I have downloaded and setup an Access template called "Call Tracker". I have managed to do everything I want but need help on a few things.

As I was playing around with the database, I realized I would have to start over to get it all working the way I want and with more beneficial naming. Since I'm starting clean I thought it would be easier for you/someone to help me from a clean setup.

If you setup the template you can see how it works with tables and coding. If you open the form "Call Details" I'll explain what I want to do and you can help guide me to the proper places/coding to use.

On this form I want to add two extra fields (4 total but the other two are simple): Under "Caller" I need to add "Called In By Phone" and "Bill To" both of which are store in both "Customers" and "Customers Extended" tables.

These three fields: "Caller", "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 figure out how to do this but I need it to use IF logic.

Here is the full scenario:

Customer calls in, employee taking the phone call clicks on the "Caller" cboBox and selects a name from the list. I want the two remaining fields (Called In By Phone & Bill To) to be filled in but with the following IF logic (or whatever it needs to be to make it work)

If the person clicked on has a "Mobile Phone" entry then use that if not then use "Business Phone". Then for the "Bill To" if the record/person selected as "Caller" has a company name then "Bill To" should fill in with it but if they don't I want it to fill in with the callers name.

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


One other thing. On the "Caller" cboBox I have stretched it to show 3 columns for "Customer Name", "Company" and "Email Address". I can't seem to make the "Company" searchable. I would like the phone person to be able to go to a company name quickly just as they can with a customer name but I can't seem to get it to work searching by company name. And if possible I'd like to group the list by "Company" so all of one company shows together. Reason for this is a few of our companies has multiple people that call in to us so I want it easier for the person inputing the data to find the company/person quickly. I have not found a way to group them together yet.

All information for the "Customer" is being pulled from the "Customers Extended" table.

So what I'm trying to do is make the "Caller", "Called In By Phone" and "Bill To" to all operate as one. All will be based on the first selection for "Caller" and incorporate the IF logic.

So basically I need help on code for the "Calls" part of the database. First to have the "Caller" also pre-fill the "Called In By Phone" and the "Bill To". Now, not all customers have a company to use as the "Bill To" so in those instances the "Bill To" uses the "Caller" name. The phone number to use will be either "Business Phone" or "Mobile Phone" but default to use "Mobile Phone" if one is stored.

The way this template seems to work is the form "Call List" is pulled up first. From this form the data entry person can click "new" for the form "Call Details" to be pulled up to use or they can type in "Datasheet" view. So, I'm assuming all of the info from form "Call Details" should also be available and stored in the "Call List" form. But, since they can type directly into "Datasheet" view all the "called in by", "called in by phone", and "bill to" would also need to work as one field if possible. (I could always make them use the form "Call Details" if fields acting as one or pre-filling other fields, will not work from the "Datasheet" view.


Also, I have created a report based on the exiting "Open Calls by Assigned To" report. I need to filter the records by today's date. Basically they will print this report every day and I only want to pull records for the current day/date. Everything I have tried does not work. The date filed to compare to is called "Opened Date". Anything for today/the day the report is printed should be pulled and ONLY those dated today. I don't mind if it pops up a box first asking for them to type in the date in the form x/x/xxxx.

I managed to get this to work with the macro below but when I move from "Report View" to "Print Preview" it generates a pop-up box "No Current Record". If I keep clicking the "OK" button it will eventually show the print preview. I don't know what I've done wrong.



Report Name Today's Calls by Assigned To
View Report
Filter Name
Where Condition =[Opened Date]>=Date()
Window Mode Normal

I know I should put in some error checking but honestly I'm not sure what to do there.


On the Customer Details form you'll notice a search box at the top left. I would like to accomplish the follwing if possible:

1. Group together the list by "Company" - or is there a way to sort this list?
2. Make the "Company" in the list searchable while still allowing searching on "Customer Name"
3. This is the same thing I want to do on the "Call Details" form for the "Caller" cboBox.

I did manage to get the two boxes pre-filled using the code below but it does not use any IF logic. The IF statements can be put in this section I assume, I defer to you guys for the best place for the code or how to accomplish the task.

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

Below is a small sampling of my Customers. (I can't find how to upload an Excel sheet here so forgive the botched job of pasting below) - SINCE I COULD UPLOAD A FILE, I'VE REMOVED THE FIELDS HERE THAT AREN'T BEING USED.

CompanyLast NameFirst NameE-mail AddressBusiness PhoneMobile PhoneFax NumberAddress
Home Solutions Property ManagementSchaweElizabethaccounting@home-solutions.com9545453027
Home Solutions Property ManagementPakalaJohnjohn@home-solutions.com9545453027
Home Solutions Property ManagementPermanJoshuajoshua@home-solutions.com9545453027
All Year CoolingRomeoJuliejulie@aycair.com95456646449548715752
All Year CoolingSmithTom95456646449547738619
Alfred LarivierePerezGus95444724449548010549
Banyanwood ApartmentsToppertSue95468374259548829993PO Box 51618Lighthouse PointFL33074-1618USA
Banyanwood ApartmentsSmithRichard95468374259549348282PO Box 51618Lighthouse PointFL33074-1618USA
SchwarzbergBarrybarry@mavenpm.com5613501916PO BOX 812410Boca RatonFL33481-2410USA
BlakeDarran954649366895452763155570 NE 28TH AvenueFort LauderdaleFL33308USA
WeberDennis954649792395464919232801 Oak Tree DriveOakland ParkFL33309-6707USA


Terry Echols

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back