Forms/Subforms Help (Tables Have Been Normalized? I think)

My Subform is as follows:
Source Object = Table.tblCustomerPhone
Master Field = lngzCustID
Child Field = lngzCustID

For my combo box
Control Source = lngzCustID
Row Source = SELECT tblPhoneTypes.idsPhoneTypeID, tblPhoneTypes.chrPhoneType
FROM tblPhoneTypes;
Column Count = 2
Column Width = 0";1"
Bound Column = 1

I also need to be able to add the actual phone number for each phone type.



"John W. Vinson" wrote:

On Sun, 30 Nov 2008 22:27:00 -0800, Avery
wrote:

I removed the referential integrity between the tblCustomer and the
tblCustomerPhones, then the error message goes away. Is this the right thing
to do?

No.

Referential integrity prevents you from adding a record to tblCustomerPhones
when that record doesn't link to any existing customer. You've just removed
that protection! Now you can add all the tblCustomerPhones records you like...

*but you won't have any way to identify which customer has that phone*.

Not much use having a list of phone numbers if you can't tell who they're for!

Correct the error in your form, instead.

Please post back with more information about the form and subform - what are
their Recordsources (post the SQL)? What are the Master and Child Link Field?
--

John W. Vinson [MVP]

You should not be using lngzCustID as the control source of a combo
box that is meant to select a phone type! The control source should
be lngzPhoneTypeID. The CustomerID will be managed by the
Master/Child link.

I also need to be able to add the actual phone number for each phone type.

Your tblCustomerPhone has a separate field for PhoneID, so you would
use another combo box for this purpose.

--
_________

Sean Bailey


"Avery" wrote:

My Subform is as follows:
Source Object = Table.tblCustomerPhone
Master Field = lngzCustID
Child Field = lngzCustID

For my combo box
Control Source = lngzCustID
Row Source = SELECT tblPhoneTypes.idsPhoneTypeID, tblPhoneTypes.chrPhoneType
FROM tblPhoneTypes;
Column Count = 2
Column Width = 0";1"
Bound Column = 1

I also need to be able to add the actual phone number for each phone type.



"John W. Vinson" wrote:

On Sun, 30 Nov 2008 22:27:00 -0800, Avery
wrote:

I removed the referential integrity between the tblCustomer and the
tblCustomerPhones, then the error message goes away. Is this the right thing
to do?

No.

Referential integrity prevents you from adding a record to tblCustomerPhones
when that record doesn't link to any existing customer. You've just removed
that protection! Now you can add all the tblCustomerPhones records you like...

*but you won't have any way to identify which customer has that phone*.

Not much use having a list of phone numbers if you can't tell who they're for!

Correct the error in your form, instead.

Please post back with more information about the form and subform - what are
their Recordsources (post the SQL)? What are the Master and Child Link Field?
--

John W. Vinson [MVP]

I cannot include referential integrity between the following tables...Why?

tblCustomers
idsCustID

tblCustomerPhone
lngzCustID

"Beetle" wrote:

You should not be using lngzCustID as the control source of a combo
box that is meant to select a phone type! The control source should
be lngzPhoneTypeID. The CustomerID will be managed by the
Master/Child link.

I also need to be able to add the actual phone number for each phone type.

Your tblCustomerPhone has a separate field for PhoneID, so you would
use another combo box for this purpose.

--
_________

Sean Bailey


"Avery" wrote:

My Subform is as follows:
Source Object = Table.tblCustomerPhone
Master Field = lngzCustID
Child Field = lngzCustID

For my combo box
Control Source = lngzCustID
Row Source = SELECT tblPhoneTypes.idsPhoneTypeID, tblPhoneTypes.chrPhoneType
FROM tblPhoneTypes;
Column Count = 2
Column Width = 0";1"
Bound Column = 1

I also need to be able to add the actual phone number for each phone type.



"John W. Vinson" wrote:

On Sun, 30 Nov 2008 22:27:00 -0800, Avery
wrote:

I removed the referential integrity between the tblCustomer and the
tblCustomerPhones, then the error message goes away. Is this the right thing
to do?

No.

Referential integrity prevents you from adding a record to tblCustomerPhones
when that record doesn't link to any existing customer. You've just removed
that protection! Now you can add all the tblCustomerPhones records you like...

*but you won't have any way to identify which customer has that phone*.

Not much use having a list of phone numbers if you can't tell who they're for!

Correct the error in your form, instead.

Please post back with more information about the form and subform - what are
their Recordsources (post the SQL)? What are the Master and Child Link Field?
--

John W. Vinson [MVP]

Could be because you removed the referential integrity previously,
then proceeded to start storing PhoneTypeID numbers in the
lngzCustID field in your tblCustomerPones (because of the way you
had your combo box set up). Now you have FK values in the junction
table that don't match any PK values in the Customers table,
therefore referential integrity cannot be established.
--
_________

Sean Bailey


"Avery" wrote:

I cannot include referential integrity between the following tables...Why?

tblCustomers
idsCustID

tblCustomerPhone
lngzCustID

"Beetle" wrote:

You should not be using lngzCustID as the control source of a combo
box that is meant to select a phone type! The control source should
be lngzPhoneTypeID. The CustomerID will be managed by the
Master/Child link.

I also need to be able to add the actual phone number for each phone type.

Your tblCustomerPhone has a separate field for PhoneID, so you would
use another combo box for this purpose.

--
_________

Sean Bailey


"Avery" wrote:

My Subform is as follows:
Source Object = Table.tblCustomerPhone
Master Field = lngzCustID
Child Field = lngzCustID

For my combo box
Control Source = lngzCustID
Row Source = SELECT tblPhoneTypes.idsPhoneTypeID, tblPhoneTypes.chrPhoneType
FROM tblPhoneTypes;
Column Count = 2
Column Width = 0";1"
Bound Column = 1

I also need to be able to add the actual phone number for each phone type.



"John W. Vinson" wrote:

On Sun, 30 Nov 2008 22:27:00 -0800, Avery
wrote:

I removed the referential integrity between the tblCustomer and the
tblCustomerPhones, then the error message goes away. Is this the right thing
to do?

No.

Referential integrity prevents you from adding a record to tblCustomerPhones
when that record doesn't link to any existing customer. You've just removed
that protection! Now you can add all the tblCustomerPhones records you like...

*but you won't have any way to identify which customer has that phone*.

Not much use having a list of phone numbers if you can't tell who they're for!

Correct the error in your form, instead.

Please post back with more information about the form and subform - what are
their Recordsources (post the SQL)? What are the Master and Child Link Field?
--

John W. Vinson [MVP]

Fixed...

Now that I have this out of the way, I'm trying to input a combo box that I
can input data, how do I get the combo box to accept the phone number and
link up date with the phone type:

Example

cboPhoneType (Complete--will drop down and allow the selection of phone type)

cboPhone (Control source = lngzPhoneID, but I need to be able to input a
phone number and make it update with the phone type field)...the program will
not allow me to input phone numbers only the id, nor will it update with the
phone type.

I don't mean to be a pest, but I need help...Thanks,

"Beetle" wrote:

Could be because you removed the referential integrity previously,
then proceeded to start storing PhoneTypeID numbers in the
lngzCustID field in your tblCustomerPones (because of the way you
had your combo box set up). Now you have FK values in the junction
table that don't match any PK values in the Customers table,
therefore referential integrity cannot be established.
--
_________

Sean Bailey


"Avery" wrote:

I cannot include referential integrity between the following tables...Why?

tblCustomers
idsCustID

tblCustomerPhone
lngzCustID

"Beetle" wrote:

You should not be using lngzCustID as the control source of a combo
box that is meant to select a phone type! The control source should
be lngzPhoneTypeID. The CustomerID will be managed by the
Master/Child link.

I also need to be able to add the actual phone number for each phone type.

Your tblCustomerPhone has a separate field for PhoneID, so you would
use another combo box for this purpose.

--
_________

Sean Bailey


"Avery" wrote:

My Subform is as follows:
Source Object = Table.tblCustomerPhone
Master Field = lngzCustID
Child Field = lngzCustID

For my combo box
Control Source = lngzCustID
Row Source = SELECT tblPhoneTypes.idsPhoneTypeID, tblPhoneTypes.chrPhoneType
FROM tblPhoneTypes;
Column Count = 2
Column Width = 0";1"
Bound Column = 1

I also need to be able to add the actual phone number for each phone type.



"John W. Vinson" wrote:

On Sun, 30 Nov 2008 22:27:00 -0800, Avery
wrote:

I removed the referential integrity between the tblCustomer and the
tblCustomerPhones, then the error message goes away. Is this the right thing
to do?

No.

Referential integrity prevents you from adding a record to tblCustomerPhones
when that record doesn't link to any existing customer. You've just removed
that protection! Now you can add all the tblCustomerPhones records you like...

*but you won't have any way to identify which customer has that phone*.

Not much use having a list of phone numbers if you can't tell who they're for!

Correct the error in your form, instead.

Please post back with more information about the form and subform - what are
their Recordsources (post the SQL)? What are the Master and Child Link Field?
--

John W. Vinson [MVP]

On Mon, 1 Dec 2008 10:25:02 -0800, Avery
wrote:

Fixed...

Now that I have this out of the way, I'm trying to input a combo box that I
can input data, how do I get the combo box to accept the phone number and
link up date with the phone type:

Example

cboPhoneType (Complete--will drop down and allow the selection of phone type)

cboPhone (Control source = lngzPhoneID, but I need to be able to input a
phone number and make it update with the phone type field)...the program will
not allow me to input phone numbers only the id, nor will it update with the
phone type.

You'll need to use some VBA code in the Combo Box's NotInList event to pop up
a data entry form for the Phones table. See
http://www.mvps.org/access/forms/frm0015.htm
for sample code.
--

John W. Vinson [MVP]

I am sorry, but I still do not get it...

Do I even need the tblPhone? if so, What is the code that will allow me to
select the Phone Type(i.e. Business) and a box pops up that will allow me to
input the actual phone number (masked input) and make it unique to that
particular record?

I am really sorry, and I do understand code, but I don't see any that is
valid to this particular instance. If the NotInList code is valid, I can not
see it. Sorry.

"John W. Vinson" wrote:

On Mon, 1 Dec 2008 10:25:02 -0800, Avery
wrote:

Fixed...

Now that I have this out of the way, I'm trying to input a combo box that I
can input data, how do I get the combo box to accept the phone number and
link up date with the phone type:

Example

cboPhoneType (Complete--will drop down and allow the selection of phone type)

cboPhone (Control source = lngzPhoneID, but I need to be able to input a
phone number and make it update with the phone type field)...the program will
not allow me to input phone numbers only the id, nor will it update with the
phone type.

You'll need to use some VBA code in the Combo Box's NotInList event to pop up
a data entry form for the Phones table. See
http://www.mvps.org/access/forms/frm0015.htm
for sample code.
--

John W. Vinson [MVP]

The way your tables are currently structured, tblPhone and
tblPhoneTypes appear to be two separate, unrelated entities.
You probably should remove lngzPhoneTypeID from
tblCustomerPhone and put it in tblPhone as a foreign key
to tblPhoneTypes.

You could then use the Not In List code to open a separate
pop up form where the users could select (via a combo box)
the correct phone type for the new phone number they are
adding.

Also, JMHO, but I would dump the phone input mask (I hate input
masks - they're just a PITA if you ask me) and just use a
format like;

(@@@) @@@-@@@@
--
_________

Sean Bailey


"John W. Vinson" wrote:

On Mon, 1 Dec 2008 10:25:02 -0800, Avery
wrote:

Fixed...

Now that I have this out of the way, I'm trying to input a combo box that I
can input data, how do I get the combo box to accept the phone number and
link up date with the phone type:

Example

cboPhoneType (Complete--will drop down and allow the selection of phone type)

cboPhone (Control source = lngzPhoneID, but I need to be able to input a
phone number and make it update with the phone type field)...the program will
not allow me to input phone numbers only the id, nor will it update with the
phone type.

You'll need to use some VBA code in the Combo Box's NotInList event to pop up
a data entry form for the Phones table. See
http://www.mvps.org/access/forms/frm0015.htm
for sample code.
--

John W. Vinson [MVP]

On Mon, 1 Dec 2008 11:51:01 -0800, Avery
wrote:

I am sorry, but I still do not get it...

Do I even need the tblPhone? if so, What is the code that will allow me to
select the Phone Type(i.e. Business) and a box pops up that will allow me to
input the actual phone number (masked input) and make it unique to that
particular record?

Sure, you need tblPhone, if you want to record telephone numbers. It's the
only table you display which CONTAINS the telephone number!

The way your tables are set up, each record in tblCustomers is related to
zero, one, two or more records in tblCustomerPhones; also, each record in
tblPhones is related to zero, one or more records in tblCustomerPhones. This
many-to-many relationship allows each customer to have multiple phone numbers,
and for each phone to be shared by multiple customers (i.e. family members or
employees at a single location).

The code I posted is not designed to enter phone numbers with phone types.
It's *generic* code as an example of how to use the VBA NotInList event to add
new records to a table; you'll need to *modify and adapt* it to your
circumstances, not just copy and paste it.

It may be that your table design is actually more elaborate than you need. If
you are willing to have just one phone number per customer, and to repeat that
value if customers share a phone, then you only need a Phone field in
tblCustomers. A step better would be to have only tblPhones (not
tblCustomerPhones), with a CustomerID field as a foreign key; this would allow
you to enter multiple phones per customer, but has the disadvantage that you
would need to enter the same phone number multiple times if multiple customers
share it. The advantage would be that you could use a Subform based on
tblPhones (using the CustomerID as the master/child link) and enter or edit
phone numbers directly in the subform - which you cannot do using the
many-to-many relationship.
--

John W. Vinson [MVP]

Thanks guys, I will keep plugging away.

My phone numbers nor addresses should not be duplicated. Could I do this to
the tables?

"Beetle" wrote:

The way your tables are currently structured, tblPhone and
tblPhoneTypes appear to be two separate, unrelated entities.
You probably should remove lngzPhoneTypeID from
tblCustomerPhone and put it in tblPhone as a foreign key
to tblPhoneTypes.

You could then use the Not In List code to open a separate
pop up form where the users could select (via a combo box)
the correct phone type for the new phone number they are
adding.

Also, JMHO, but I would dump the phone input mask (I hate input
masks - they're just a PITA if you ask me) and just use a
format like;

(@@@) @@@-@@@@
--
_________

Sean Bailey


"John W. Vinson" wrote:

On Mon, 1 Dec 2008 10:25:02 -0800, Avery
wrote:

Fixed...

Now that I have this out of the way, I'm trying to input a combo box that I
can input data, how do I get the combo box to accept the phone number and
link up date with the phone type:

Example

cboPhoneType (Complete--will drop down and allow the selection of phone type)

cboPhone (Control source = lngzPhoneID, but I need to be able to input a
phone number and make it update with the phone type field)...the program will
not allow me to input phone numbers only the id, nor will it update with the
phone type.

You'll need to use some VBA code in the Combo Box's NotInList event to pop up
a data entry form for the Phones table. See
http://www.mvps.org/access/forms/frm0015.htm
for sample code.
--

John W. Vinson [MVP]