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

[Avery]

I need to know what my next step is...

I need a Customer form that allows me to view customers along with their
contacts at that customer. The same goes for the Facility which has several
contacts in several departments/titles. I have created the relationships with
the referential integrity...now I am stuck again. HELP!!!!!

I have created the following tables: (will I need to create a query,
subforms, etc?)

(tblCustomers)
*
idsCustID
chrContactTypeID
chrCustomerName
chrCustomerSize
chrCustomerSpecialty
blnCustomerStatus
memCustNotes
hlkCustomerWebsite
dtmDateCreate
dtmDateUpdate

(tblCustomerAddress)
*
idsCustAddressID
lngzCustID
lngzAddressID

(tblAddress)
*
idsAddressID
chrAddress
chrSuite
chrCity
chrStateAbbrev
chrZip

(tblCustomerPhone)
*
idsCustomerPhoneID
lngzCustID
lngzPhoneID
lngzPhoneTypeID

(tblPhoneTypes)
*
idsPhoneTypeID
chrPhoneType
1 CELL
2 BUSINESS
3 DIRECT
4 FAX
5 PAGER

(tblPhone)
*
idsPhoneID
chrPhone
(!\(999\)000\-0000;0;_--Input Mask)

(tblCustomerContact)-each customer may have several contacts and each
contact may have several phone number...they do not require a separate
address, but other types of contacts do.
*
idsCustomerContactID
lngzCustID
chrTitle
lngzNameID
lngzPersonalInfo
lngzContactLog

(tblDistrictSalesMgr)
*
idsDistrictSalesMgrID
lngzFacilityID
lngzNameID
chrDSMTerritory
lngzPersonalInfo
dtmDateCreate
dtmDateUpdate

(tblDistrictSalesMgrPhone)
*
idsDistrictSalesMgrPhoneID
lngzDistrictSalesMgrID
lngzPhoneID
lngzPhoneTypeID

(tblDSMAddress)
*
idsDSMAddressID
lngzDSMID
lngzAddressID

(tblFacility)--there are four manufacturing facilities, and each
facility has several contacts that have their own phone lines and extensions
and faxes.
*
idsFacilityID
chrContactTypeID
chrFacilityName
dblJoistCapacity
dblDeckCapacity
memFacilityNotes
hlkFacilityWebsite
dtmDateCreate
dtmDateUpdate

(tblFacilityAddress)
*
idsFacilityAddressID
lngzFacilityID
lngzAddressID

(tblFacilityPhone)
*
idsFacilityPhoneID
lngzFacilityID
lngzPhoneID
lngzPhoneTypeID

(tblFacilityContact)
*
idsFacilityContactID
lngzFacilityID
chrFacilityContactType(lookup from another table)
lngzNameID

(tblFaciltyContactPhone)
*
idsFacilityContactPhoneID
lngzFacilityContactID
lngzPhoneID
lngzPhoneTypeID

 

[John W. Vinson]

I need to know what my next step is...

I need a Customer form that allows me to view customers along with their
contacts at that customer. The same goes for the Facility which has several
contacts in several departments/titles. I have created the relationships with
the referential integrity...now I am stuck again. HELP!!!!!

I have created the following tables: (will I need to create a query,
subforms, etc?)

Well, without going through your long list of tables (I'll keep the message
downloaded for future reference), I'll just say that a Form for the "one" side
of a relationship with a Subform - commonly a continuous subform, though you
can use a Datasheet or Single view form if it's more appropriate - for the
"many" side is common and convenient.

 

[Avery]

My problem is I don't know where(at all) to go from here...new to Access.
Example, with the Phone Table, Phone Type Table, and the Customer Phones
Table. How would I create the form? Do I need to create a query that will
that will join the tables on a form. I just this one example, and I think I
can get it from there.

Please help. I have read books, but I am clueless about this one step.

 

[John W. Vinson]

My problem is I don't know where(at all) to go from here...new to Access.
Example, with the Phone Table, Phone Type Table, and the Customer Phones
Table. How would I create the form? Do I need to create a query that will
that will join the tables on a form. I just this one example, and I think I
can get it from there.

No, you *don't* need a query joining the tables! Instead, base your main form
on the Customers table, and a Subform on the Customer Phones Table using
lngzCustID as the master/child link field; on that subform put a combo box
based on tblPhones, allowing you to select a lngzPhoneID.

Please help. I have read books, but I am clueless about this one step.

Try some of the examples and tutorials at these sites:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Avery]

Thanks John, I think I have got it. The only problem I am having now is that
I am getting an error because the lngzPhoneID field is a number and the row
source I am trying to select are text.

 

[John W. Vinson]

Thanks John, I think I have got it. The only problem I am having now is that
I am getting an error because the lngzPhoneID field is a number and the row
source I am trying to select are text.

Post the RowSource (the SQL of the query), the Bound Column, and the Control
Source of the combo box. It appears that you're pulling one field from the
combo and trying to store it into an incompatible field.

 

[Avery]

Control Source: lngzCustID (from tblCustomerPhones)

Row Source: SELECT tblPhoneTypes.idsPhoneTypeID, tblPhoneTypes.chrPhoneType
FROM tblPhoneTypes;

My new Error Message Reads: (You cannot add or change a record because a
related record is required in table 'tblCustomer'.)

 

[John W. Vinson]

Control Source: lngzCustID (from tblCustomerPhones)

Row Source: SELECT tblPhoneTypes.idsPhoneTypeID, tblPhoneTypes.chrPhoneType
FROM tblPhoneTypes;

My new Error Message Reads: (You cannot add or change a record because a
related record is required in table 'tblCustomer'.)

Hrm. It does help if you post the error message! You said previously

Thanks John, I think I have got it. The only problem I am having now is that
I am getting an error because the lngzPhoneID field is a number and the row
source I am trying to select are text.

which has absolutely nothing to do with the error you're getting.

You're getting an error because you're apparently attempting to create a
record in tblCustomerPhones for which no record exists in tblCustomer. I
suspect you don't have the Master/Child Link Field set up correctly, or that
your main form and subform are not appropriately set up.

 

[Avery]

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

 

[John W. Vinson]

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?

 

[Avery]

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.

 

[Beetle]

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.

 

[Avery]

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

tblCustomers
idsCustID

tblCustomerPhone
lngzCustID

 

[Beetle]

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.

 

[Avery]

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,

 

[John W. Vinson]

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.

 

[Avery]

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.

 

[Beetle]

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;

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

 

[John W. Vinson]

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.

 

[Avery]

Thanks guys, I will keep plugging away.

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