Call Tracker MS Access Template

Terry Echols

New Member
Joined
Jul 14, 2015
Messages
38
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.

CompanyLast NameFirst NameE-mail AddressBusiness PhoneMobile PhoneFax NumberAddress
CityStateZIPCodeCountry
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
LariviereAlfredalfredlariviere@aol.com95444724447862908987
Alfred LarivierePerezGus95444724449548010549
Banyanwood ApartmentsToppertSue95468374259548829993PO Box 51618Lighthouse PointFL33074-1618USA
Banyanwood ApartmentsSmithRichard95468374259549348282PO Box 51618Lighthouse PointFL33074-1618USA
SchwarzbergBarrybarry@mavenpm.com5613501916PO BOX 812410Boca RatonFL33481-2410USA
OlivaClaudiocno1234@comcast.net9545570392
BlakeDarran954649366895452763155570 NE 28TH AvenueFort LauderdaleFL33308USA
WeberDennis954649792395464919232801 Oak Tree DriveOakland ParkFL33309-6707USA

<tbody>
</tbody>

Thanks,
Terry Echols
 

Forum statistics

Threads
1,081,702
Messages
5,360,738
Members
400,594
Latest member
Frothingslosh

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top