Help with Tables / Forms (1 Viewer)

OrangeEDC

Registered User.
Local time
Today, 10:16
Joined
Dec 29, 2002
Messages
17
I am trying to create a Contact List that allows me to track incoming requests for support.

I anticipate the following fields:

ContactID
First Name
Last Name
Address
Phone
Fax
Date of Contact

Purpose of Contact
Action Taken

I expect that the same contact will call several times. Therefore, I don't want to have to input the Contact info everytime. If John Jones calls 3 times, I need to make sure that the contact John Jones is only listed 1 time, but that his calls (3) are listed each time.

I am having the hardest time figuring this out. I am just starting to learn Access in depth.

Thanks
 

bjackson

Registered User.
Local time
Today, 23:46
Joined
Jan 3, 2003
Messages
404
perhaps you should look at your table set up

you need a table of contacts

ContactID
First Name
Last Name
Address
Phone
Fax

then a table of say Actions

ActionId
ContactId
Date of Contact
Purpose of Contact
Action Taken

relate the tables 1 to many using ContactId
 

OrangeEDC

Registered User.
Local time
Today, 10:16
Joined
Dec 29, 2002
Messages
17
Thank you!

I will try this out immediately!
 

OrangeEDC

Registered User.
Local time
Today, 10:16
Joined
Dec 29, 2002
Messages
17
bjackson,

Thank you for the assistance. I prepared the tables as you suggested and I populate the tables through a form (I used the design wizard).

The tables are set up as follows:

Contact ID
First Name
Last Name
Address
Phone Number

Reference ID
Purpose
Action
Date of Call


The problem I have is with this is:

If John Jones calls (first time) I use the form and type in all his contact information. Lets give John Jones the Following Data

Contact ID {6}
First name: John
Last Name: Jones
Address: 13 Washington Avenue
Phone Number: (201)555-1212

Reference ID {13}
Purpose: Called for property listing information
Action: Sent property listing to Mr. Jones for all properties in excess ofd 10,000 s.f.
Date: March 1, 2003

Now, if Mr. Jones calls me two days later, I would complete the form again to gather his information. But, what I really want is the for the form to repopulate the form fields (Contact ID, FName, LName, Address, Phone) so i don't have to retype his contact info.

If I continue doing it the same way, then Mr. Jones will end up with Multiple Contact ID's, when in fact he should only have one Contact ID. Although, He will have multiple Reference IDs (one Ref ID for each time he calls for service).

I am not sure what to do from this point forward.

Thank you for your support and patience.
 

bjackson

Registered User.
Local time
Today, 23:46
Joined
Jan 3, 2003
Messages
404
there is an excellent db under access database wizards
which you might want to look at

it is called contacts and it does similiar things to what you are trying to do.maybe you could use that db and change it
to suit your needs as a lot of your work will already be done
for you
 

bjackson

Registered User.
Local time
Today, 23:46
Joined
Jan 3, 2003
Messages
404
i have changed the data entry form for
you to give you some idea of what this db has to offer

see what you think, its in access 2000
 

OrangeEDC

Registered User.
Local time
Today, 10:16
Joined
Dec 29, 2002
Messages
17
Mr. Jackson:

Thank you for your help! I did review the tables / form, but there is one problem.

On the contact form, once you select the caller by the first name the Last Name shows up. However the other fields (ie Address, State, Region, Etc. ). don't change to reflect what was previously added to the records in the table.

Example: (table includes ID, First Name, Last Name, Address, State)

ID 001, Paul, Jones, 1 North Street, Connecticut
ID 002, Eddi, James, 2 South Street, New York
ID 003, Mike, Marks, 3 West Street, California
ID 004, Pete, Jacks, 4 East Avenue, Florida

We'll go to the Record # 5 and within the First Name Field there is a drop down field that allows you to select the First Name of the contact. When you select "Paul", "Jones" will come up in the Last Name Field. This is correct, but the Address, and State Fields don't automatically "fill in' to thier respective fields.

If I know why the Last Name is linked to the first name, then i can extend the link to the other fields. What I can't figure out is how you did it!

I know I am getting closer to the solution, though.
 
Last edited:

bjackson

Registered User.
Local time
Today, 23:46
Joined
Jan 3, 2003
Messages
404
have a look at the after update event for the
first name combo box

you will see code there that says
Me.LastName = Me.FirstName.Column(1)
Me.Address = Me.FirstName.Column(3)
Me.WorkPhone = Me.FirstName.Column(11)


you need to add code there to to fill in
each text box that you want to see information in.
postalcode is in column 6 of the combo box named FirstName
so you would add,
Me.PostalCode = Me.FirstName.Column(6)

if you intend to use that db (which i think suits your purpose)

then i assume you will make a lot of those text boxes
invisible-i wouldnt delete them you never know when you may
need another field.

thats why i havent filled in all the code,i dont know what
fields you intend to use

hope this helps
 

bjackson

Registered User.
Local time
Today, 23:46
Joined
Jan 3, 2003
Messages
404
i should add that when you look at the query that is
the rowsource for the combo box firstname
the first column is number 0
the 2nd column is 1
the 3rd column is 2
etc etc
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:46
Joined
Feb 19, 2002
Messages
42,970
An autolookup query will do this for you without any coding. Simply join the lookup table to the main table and select whatever columns you need from the lookup table (contact). Change the recordsource of the form to use this query. If it is already referencing a query and you change that query, delete its name, move the cursor out of the recordsource property box and then re-add the query name. This little dance forces Access to refresh the form's fields collections. It should be done whenever you change the form's query.

When you use this technique, the fields of the lookup table are actually bound to the form. I recommend that you set the locked property of these fields to Yes to prevent accidental updates.

On the form, when you select a contact from the contact combo, all his info will automatically fill the bound columns.

You can read all about this process in help. Just look up "autolookup query".
 

OrangeEDC

Registered User.
Local time
Today, 10:16
Joined
Dec 29, 2002
Messages
17
Pat and BJack;

This should probably be very easy, but I am getting extremely frustrated.

I would like to thank you for your help and I am going to continue working on this. I printed out your suggestions and I am just having difficulty making heads or tails out of your suggestions.

I will get back to you as I progress. Take care.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:46
Joined
Feb 19, 2002
Messages
42,970
Access comes with some templates that you can customize. A2K has a contact management template and a service call management template and I think earlier versions may also. There are also templates available on the web. Follow the link from within Access.
 

OrangeEDC

Registered User.
Local time
Today, 10:16
Joined
Dec 29, 2002
Messages
17
Pat;

Thank you for the quick reply. I have to say that I am very impressed with the level of interest these boards have, along with the professionalism shown by most if not all the posters.

It can defintiely be overwhelming for someone new to Access, like myslef. I truly appreciate the patience that is shown on these boards.

I have purchased a Book called "Access 2002 Bible" by Cary Sprague and Michael Irwin. I am reading this along with checking out Microsoft Help as you and Bjackson have suggested.

Thank and take care.

Perhaps you can explain something to me:

If I have a Contact table called TblContact with 4 fields called:
ContactID
FirstName
LastName
FullName

If I enter
ContactID: 001
FirstName: James
lastName: Jackson

How would I get Full Name to automatically show: Jackson, James

C-ya round campus:)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:46
Joined
Feb 19, 2002
Messages
42,970
You should not store fullname in the table. Whenever you want to see fullname, you can "calculate" it in your query, form, or report. I find that the best place is the query.

Select FirstName & " " & LastName As FullName, ....
From YourTable;

If you use this query as the recordsource for a form or report, you can choose the FullName field from the recordsource just as if it had been stored in the table.

If you include FirstName, LastName, and the "calculated" FullName in the query used as the recordsource for a form, when you change the value of either FirstName or LastName and leave the field, Access will automatically update the field that shows FullName.
 

OrangeEDC

Registered User.
Local time
Today, 10:16
Joined
Dec 29, 2002
Messages
17
bjackson & Pat Hartman:

I just wanted to let you know that my form is complete. It works terrific. You guys were awesome.

It took a little bit of figuring, but there were a couple things that you fellas said that did not immediately click in my head. Then I noticed bjacks' comment about the Me.LastName = Me.FirstName.Column(1) and all that I needed to add the code for each field I needed to fill in.

I also ran this though a query as Pat Suggested and BAM! It worked.

Just so all you know, I have never done anything with Access except Mail Merges and now I built myself this neat little Call / Contact Database.

Thank you for all your efforts. If anyone wants a copy of it let me know and I'll be happy to post it!
 

Users who are viewing this thread

Top Bottom