Good afternoon,

I am in charge of creating an access database to log phone calls. I have looked at the Contact Management and the Call Tracker templates but neither seem to offer the features that I am looking for and it seems that I would be best served by building one from scratch. I am new to access but have been reading these forums and other websites for a few weeks to gain some background on database design in MS Access 2007.

I have two tables. One to store information about callers and another to store information about calls. Please refer to them below:

tbl_calls
CID (primary key)
calls_PID (foreign key)
calls_date (date/time) Default = Now()
calls_description (memo)
calls_resolution (memo)

tbl_people
PID (primary key)
people_first (text)
people_last (text)
people_phone1 (text)
people_phone2 (text)
people_otherID (text)



One of my goals is to be able to open a person's record and view all the calls he/she has made to my office. Right now, I have one-to-many relationship {enforced referential integrity, cascaded update related fields, and with join type 3} withboth tables between PID (the primary key from tbl_people) and calls_PID (foreign key in tbl_calls).

Question 1:
What is the best relationship to form between these two tables?
I am having difficulty figuring out the best method for inputing data.

Ideally, I'd like the give the receiptionist the ability to type in a person's name to search tbl_people for a matching record using the following criteria: first name, last name, and/or telephone number. If the search finds a matching record, she should be able to hit a "Add call" button that will bring up a form to add a row to tbl_calls. If the search comes back without any results, she should be able to hit a "New person" button that takes her to a form to add a row to tbl_people and then persents her with an "Add call" button.

Question 2:
Any tips on the best way to setup the queries and forms that I need to accomplish what I described above?

Any help is GREATLY appreciated!

This post has been edited by inneedofaccesshelp: Jul 26 2010, 12:44 PM

What you want is very simple. Make a form for both of the tables. In Access 2007 you can do this with a click. In earlier versions use the wizard. Then on the frm_calls form replace the pid with a combobox on the tbl_persons. Also put in a default value for the date.

Enter the people on the tbl_people form. Receptionist opens the frm_calls form, clicks on *, and enters the information. You can put a button on frm_calls to open frm_people for new people. The combo should provide your type-in function automatically.

Thanks for helping. I did what you said, but there is a problem. When I type a name in the combo box (on tbl_calls form), an error message occurs:

"The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits."

This is how I set the combo box up:

Control Source: calls_PID
Row Foruce: tbl_people
Row Source Type: Table/Query
Bound Column: 1
Limit to List: No
Allow Value List Edits: Yes

If I understand correctly, the combo box is giving me the message because I entered text; the control source is the AutoNumber field. How can I change this so that the receptionist types in the caller's name on the call form and if the name isn't found within tbl_people table, it provides a message that says, "Record not found for this caller. Please add a record" and it provides a button that opens to the frm_people form?

What is the Column Count of your Combo Box ?

tbl_people
PID (primary key)
people_first (text)
people_last (text)


You have a Column Count of 3
Bound Column is 1
Lengths 0;.75;.75

If you are using a Value List then the Row Source type would be value List as opposed to Table/Query

Does that help ?

See if the Attached helps, open frmpeople there are two records of people, if you click the list bnox to the right it will go to that persons record, if you need to enter a call for a New person that is not on the listbox to the right just add a New record to the People form. This is one of Many ways to handle it.

HTH

Thanks, but a problem is that we receive too many calls (I'm talking about 100+ per day) for that to really work. Do you mind taking a look at what I have uploaded here? I created a form that basically acts as a search (frm_people).

Our receptionist will be able to open that form when she answers a call to run the person's name through the filter. If no results are returned from the search, she will be able to click the "New" button. If the filter returns with a match, she will be able to hit "add call."

The problem I am now having is getting the buttons to work properly.

First, on frm_people, how can I get the hyperlink to "Add Call" to open frm_calls and have the caller's name automatically added into the combox box (so that the call is associated with the caller).

Second, I can't get either of the two command buttons on frm_add_people to work properly. One should give you the ability to save and clsoe and the other should give you the ability to save and add a new call associated with that person's record.

Any ideas?

 CR_Phone_Database_TEST_TEST_TEST.zip ( 98.32K ) Number of downloads: 20



forgot to upload. =(

No guarantees but I will take a look Yes (IMG:style_emoticons/default/smile.gif)

Thank you for helping me!

When you do Add Call, you need to open that form on a new record.

Sorry for the delay had to take lunch, see if the attached is close ????

In the file you uploaded, for some reason when I use frm_people to locate the caller, it now pops up with this error:

Type Mismatch

When I hit OK, it says this:

Macro name:
frm_people : cmdGo : OnClick : Embedded Macro

Condition:
True

Action Name:
SetTempVar

Arguments:
strSearch, Replace([Forms]![frm_people]![SearchBox],"""","""""")

OOps i'll take a look and get right back to you

Can't seem to repeat the error, have you tried a Compact and repair Database ? You should NOT have a control on your frm_people named Searchbox I changed that are you sure you are looking at the correct Database ?

Office Button>Manage>Compact and Repair Database

Have reattached as well

It didn't fix anything, but I think I found the problem...

The macro embedded on cmdGo is still pointing towards SearchBox.

BTW-- what is the benefit of having the search box that you added vs the one that I had placed in there? Just trying to assess the change...

No benefit really just a different way to basically do the same thing. Are you now getting the desired results?

I reverted back to the SearchBox...

I also added Save and Close buttons to the form frm_calls.

Everything is working except one feature. I am just running through the "process" that the receiptionist would use, described below.

(assume that the receiptionist just answers a call)

1. Open frm_people
2. Type in the person's name
3. If the person's name isnt' found, click the New Caller link that opens from_add_people
4. Once frm_add_people is filled out, click Save and Add button that opens frm_calls
5. Fill out the call information on frm_calls

The problem I am having lies within step 5: frm_calls doesn't seem to retain the information from the previous form. i.e. when I click "Save and Add Call" from frm_add_people, the person's name doesn't show up in frm_calls. I don't think this is associating the call with the person's record? Furthermore, I get this message when I try to save the call record: "The Microsoft Office Access database engine cannot find a record in the table 'tbl_people' with key matching fields(s) 'calls_PID'

I have a couple of meetings this morning however as soon as I get the chance I will see what I can do.

Ok thats OK

Typing in the persons name in the combo box at the top of the form does nothing ???

OK

OK

OK



I think I have it working now but you can be the judge of that ???

See Attached

BTW: If you don't mind telling us, what is your name ?

I ran emptied all of the tables of data and then compiled and repaired the database.

I ran through the procedures (search for person on frm_people, since there are no records I clicked on "(New)" from the datasheet view, which opens from)add_people. I added a person and then hit "Save and Add Call." On the form taht opens (frm_calls), the Caller combo box was blank (it should have been the name of the caller?). I filled in the rest of the form anyway and hit Save and Close, but it popped up with Run Time Error 2458?

When I hit debug, this is what it said in the visual basic editor:

Private Sub Text14_Click()
DoCmd.OpenForm "frm_Calls", acNormal, , , acFormAdd, acWindowNormal, Me.PID
DoCmd.Close acForm, "frm_people"

End Sub


????