Call Tracker MS Access Template

[Terry Echols]

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:

MICROSOFT OFFICE 365
ACCESS 2016 MSO (16.0.6965.2053) 32-bit

OBJECT ONE:

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.

OBJECT TWO - APPLIES HERE AND TO THE CUSTOMER DETAILS FORM (MORE BELOW)

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.

OBJECT THREE

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.

MACRO CODE:

I MADE THIS AN ON LOAD EVENT

OpenReport
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.

OBJECT FOUR (CONTINUE FROM TWO)

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.

[TABLE="class: grid, width: 1306"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]E-mail Address[/TD]
[TD]Business Phone[/TD]
[TD]Mobile Phone[/TD]
[TD]Fax Number[/TD]
[TD]Address
[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]ZIPCode[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]Home Solutions Property Management[/TD]
[TD]Schawe[/TD]
[TD]Elizabeth[/TD]
[TD]accounting@home-solutions.com[/TD]
[TD]9545453027[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Home Solutions Property Management[/TD]
[TD]Pakala[/TD]
[TD]John[/TD]
[TD]john@home-solutions.com[/TD]
[TD]9545453027[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Home Solutions Property Management[/TD]
[TD]Perman[/TD]
[TD]Joshua[/TD]
[TD]joshua@home-solutions.com[/TD]
[TD]9545453027[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]All Year Cooling[/TD]
[TD]Romeo[/TD]
[TD]Julie[/TD]
[TD]julie@aycair.com[/TD]
[TD]9545664644[/TD]
[TD]9548715752[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]All Year Cooling[/TD]
[TD]Smith[/TD]
[TD]Tom[/TD]
[TD][/TD]
[TD]9545664644[/TD]
[TD]9547738619[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lariviere[/TD]
[TD]Alfred[/TD]
[TD]alfredlariviere@aol.com[/TD]
[TD]9544472444[/TD]
[TD]7862908987[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alfred Lariviere[/TD]
[TD]Perez[/TD]
[TD]Gus[/TD]
[TD][/TD]
[TD]9544472444[/TD]
[TD]9548010549[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banyanwood Apartments[/TD]
[TD]Toppert[/TD]
[TD]Sue[/TD]
[TD][/TD]
[TD]9546837425[/TD]
[TD]9548829993[/TD]
[TD][/TD]
[TD]PO Box 51618[/TD]
[TD]Lighthouse Point[/TD]
[TD]FL[/TD]
[TD]33074-1618[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]Banyanwood Apartments[/TD]
[TD]Smith[/TD]
[TD]Richard[/TD]
[TD][/TD]
[TD]9546837425[/TD]
[TD]9549348282[/TD]
[TD][/TD]
[TD]PO Box 51618[/TD]
[TD]Lighthouse Point[/TD]
[TD]FL[/TD]
[TD]33074-1618[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Schwarzberg[/TD]
[TD]Barry[/TD]
[TD]barry@mavenpm.com[/TD]
[TD][/TD]
[TD]5613501916[/TD]
[TD][/TD]
[TD]PO BOX 812410[/TD]
[TD]Boca Raton[/TD]
[TD]FL[/TD]
[TD]33481-2410[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oliva[/TD]
[TD]Claudio[/TD]
[TD]cno1234@comcast.net[/TD]
[TD][/TD]
[TD]9545570392[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Blake[/TD]
[TD]Darran[/TD]
[TD][/TD]
[TD]9546493668[/TD]
[TD][/TD]
[TD]9545276315[/TD]
[TD]5570 NE 28TH Avenue[/TD]
[TD]Fort Lauderdale[/TD]
[TD]FL[/TD]
[TD]33308[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Weber[/TD]
[TD]Dennis[/TD]
[TD][/TD]
[TD]9546497923[/TD]
[TD]9546491923[/TD]
[TD][/TD]
[TD]2801 Oak Tree Drive[/TD]
[TD]Oakland Park[/TD]
[TD]FL[/TD]
[TD]33309-6707[/TD]
[TD]USA[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
Terry Echols