Multiple Fields Same Record Source

[Cameron Piper]

All,

I am working on a contact management database where we
will be tracking a lot of different information about
client's phone numbers. I have created two different
tables. One is called "tblclient" and I will be storing
all related client data here. In another table
called "tblphone" I will be storing all the phone number
information. Because we are storing so much information
about the client's phone numbers, we broke it into a
different table rather than storing it with the client
information. Each phone number has 10 different fields
associated with it. Ideally I would only like to store
information for the phone numbers that I have for each
client and give each one a unique ID rather than storing
10 phone numbers and all the related fields in a table
(even if I don't have that type of phone number for the
client)and associating them only with one contact.

What would be the best way to handle the input of the
information? Would one form be best where there were
fields to create 10 different phone numbers? (I have
tried this but when I use the combo box to select the
type of phone number it changes all 10 types) Would it
be better to somehow create an afterupdate event that
after entering phone #1, the blanks for phone #2 would
automaticaly associate with a new unique phone ID #.

Any help on this matter would be greatly appreciated!!

Cameron Piper
Coldwell Banker Burnet Realty
(e-mail address removed)

 

[Pavel Romashkin]

I think you have a good design for your problem. Let me recap:
1) Clients
Client_ID
Other_Info
2) Phones
Record_ID
Client_ID
PNumber_Type_ID
Other_Info
3) Types
PNumber_Type_ID
PNumber_Type_Desc

Now, you have a main form based on Table 1, and a subform that uses
table 2 as a record source, linked on Client_ID. You can use the same
subform again (have 2 subforms) in a datasheet view to show all phones
so far entered for the particular client.

Pavel

 

[Cameron Piper]

Pavel or Others,

I went ahead and tried to create a form but I am still
having problems. I created a subform and a primary form
and went ahead and tried to make it work. At the bottom
of the form I put the contactID of which I wanted the
phone numbers linked. After that I added two subforms
with the phone number information. I opened the form and
proceded to give it a test run. I selected the type of
phone number on the main form tabbed over and enter the
phone number information and tabbed to the next tab stop
which was the first subform. So far so good. I picked
the second type of phone number and tabbed over to enter
the information, I was able to enter the phone
information, but when I tried to tab onto the next
subform, I was prompted with a long warning essentially
saying that I would be creating a duplicate entry.

Because I am new to this you may need to explain things a
little more in depth due to my sheer ignorance. I
created the first form and placed fields on it from my
Phone table. I placed the client ID on the main form and
proceeded to place the combobox for the phone type and
the rest of the fields. For the subform I right clicked
the primary form, copied it and then pasted it. When
promopted I entered the name subform. Back in design
view for the primary form I clicked on the subform
control botton and placed it on the form. I
selected "subform" as the source and I chose the
option "show tblphone for each record in tblphone using
clientID."

The message that I receive when I tried to place two
different phone numbers is as follows: "The changes you
requested to the table were not successful because they
would create duplicate values in the index, primary key,
or relationship. Change the data in the field or fields
that contain the duplicate data, remove the index, or
redefine the index to permit duplicate entries and try
again."

Since the fields that I am using essentially have the
same source tblPhone:Areacode, etc. What I essentially
want Access to recognize is that when I move onto the
subform I am now typing a new phone number for the same
client (therefore assigning a new PhoneID to the new
number) not modifying the one that I just entered.

If you think that you might be able to help I would
certainly appreciate any feedback that could be given.

Cameron Piper

 

[Pavel Romashkin]

I am guessing that one of your primary keys is not an Autonumber.
Could you post the data structure of your tables in the form

Field1, PK, Autonumber
Field2, Text
Field3, FK, Number

or simply put your sample database on the web and post the URL. It may
be easier for people to help that way.
Pavel

 

[Cameron Piper]

Structure is as follows:

tblClient:
ClientID, Autonumber, Primary Key

tblPhone:
ClientID, Number, Long Integer, Linked to tblClient
PhoneID, Autonumber, Primary Key
PhoneType, Text, Linked to tlkpPhoneType
PhoneAreaCode, Number, Integer
PhoneNumber, Number, Long Integer
PhoneExtension, Number Integer
PhoneNotes, Text, 50 Characters
Etc. (not used in test)

tlkpPhoneType:
PhoneType, Text, No Primary Key (just a look up table)

 

[Pavel Romashkin]

Your table structure looks correct for what you are doing in your Forms.
Could it be that you didn't go to the new record in the subform to make
another entry for the next type?

Pavel