Linking Data

[JamesJ]

Hi. I have a table I use to track my appointments and a table
use to track contacts. When creating or editing an appointment
I would like to select a contact related to the appointment
directly from one form.
Currently I have a command button which opens a contacts
form but I must select the contact and the phone number
and copy and paste it into the appointments notes field.
I've tried creating a sub form with a combo box based on
the contacts table but never inserted it into the appointments
form 'cause of problems occurring with the data.(another story).

Any help will be appreciated.
James

 

[Rick Brandt]

Hi. I have a table I use to track my appointments and a table
use to track contacts. When creating or editing an appointment
I would like to select a contact related to the appointment
directly from one form.
Currently I have a command button which opens a contacts
form but I must select the contact and the phone number
and copy and paste it into the appointments notes field.
I've tried creating a sub form with a combo box based on
the contacts table but never inserted it into the appointments
form 'cause of problems occurring with the data.(another story).

Any help will be appreciated.
James

Your appointments table and form should have a field for ContactID (whatever the
Primary Key is from the Contacts table). All you need to do is make that a
ComboBox so you can select a contact from the drop down list.

This ContactID field is the ONLY field that you should be storing in the
appointment record. All other data related to the contact should be *displayed
only* by using a lookup or a subform.

 

[JamesJ]

Do I use the ContactID filed from contacts in the reminders form or
create a new field in the reminders table and create a new relationship?

 

[Rick Brandt]

Do I use the ContactID filed from contacts in the reminders form or
create a new field in the reminders table and create a new
relationship?

What is the reminders form? The basic rule is that you only include foreign
keys in your related records then use the table relationships to pull whatever
data you need from all the related tables. This way you aren't storing the same
data redundantly in multiple tables.

The exception to this is if the related table contains dynamic data. For
example if I am entering a sales order and have the following tables...

Sales
SalesLineItems
Products

....the SalesLineItems table will include the PK value from Sales and the
SalesLineItems table will include the PK value from Products for each line item
entered. However if the UnitPrice is also found in the Products table then I
need to copy that value to the SalesLineItems table because I need to "capture"
the price at the time of the order and that price will almost certainly change
at some point in the future.

If a user goes back and reviews sales orders from last year he doesn't want to
see today's prices so a Lookup from the Products table is not appropriate in
that context. However; if I am displaying a ProductDescription on the form I
would want to pull that from the Products table with a lookup mechanism rather
than copying it. That way when I look at older orders I am always seeing the
current description of the product.

 

[JamesJ]

Ok. I've created a new field (ContactID) in the tblReminders
and have created a new relationship between that new field
and the ContactID in tblContacts. I insert the new field onto
frmReminders and it is now a combo box (cboContactID.
But I'm not sure where to go from here. I would have the
contact name (LIsting) and Phone being displayed in a subform?

James

 

[Rick Brandt]

Ok. I've created a new field (ContactID) in the tblReminders
and have created a new relationship between that new field
and the ContactID in tblContacts. I insert the new field onto
frmReminders and it is now a combo box (cboContactID.
But I'm not sure where to go from here. I would have the
contact name (LIsting) and Phone being displayed in a subform?

If you wanted a complete address displayed a subform would be a good way to go.
If you just want a few fields and/or would like the ability to not necessarily
have them all displayed in a group then you can use the follwoing lookup method.

Add additional columns to your ComboBox that include the additional data you
want to see from the Contacts table. Make them hidden by setting the column
width to zero. Then you can place unbound TextBoxes on your form that have
ControlSource entries of...

=ComboBoxName.Column(1)
=ComboBoxName.Column(2)

These will display the second and third columns from the ComboBox respectively
(column numbers start at zero).

 

[JamesJ]

cboContactsID displays no data. Keep getting Enter parameter value for
Listing and Phone.
I based the row source of cboContactsID as ContractID, Listing and Phone
from tbleContacts.
Do I need to add these field from tblContacts to Record Source of
frmReminders.

James

 

[Rick Brandt]

cboContactsID displays no data. Keep getting Enter parameter value for
Listing and Phone.
I based the row source of cboContactsID as ContractID, Listing and
Phone from tbleContacts.
Do I need to add these field from tblContacts to Record Source of
frmReminders.

No. The Rowsource of a ComboBox or ListBox need not have anything to do with
the RecordSource of the form where the control is being used. If your ComboBox
is displaying no data then your RowSource has something wrong with it. Post the
SQL you are using.

 

[JamesJ]

Oops. Wrong table name.brb

No. The Rowsource of a ComboBox or ListBox need not have anything to do
with the RecordSource of the form where the control is being used. If
your ComboBox is displaying no data then your RowSource has something
wrong with it. Post the SQL you are using.

 

[JamesJ]

The cbo is now displaying the columns.How do I get
the contact data to be saved with ther reminders record.

James

 

[JamesJ]

Hi again. I've tried a few things on my own with no success.
I understand the contact data does not get saved to the reminder's
record but when I open the particular reminder record the contact
data is no longer displayed. Is there a way to display the contact
info in the reminder record?

Thanks,
James

 

[Rick Brandt]

Hi again. I've tried a few things on my own with no success.
I understand the contact data does not get saved to the reminder's
record but when I open the particular reminder record the contact
data is no longer displayed. Is there a way to display the contact
info in the reminder record?

Is the ContactID still displayed in the ComboBox? If not then you forgot to
bind the control to a field in your table. If it is then you did something else
wrong because if Column(0) of the ComboBox is displaying a ContactID then the
other columns of the ComboBox simply must hold the other data and if you have a
TextBox with a ControlSource of =ComboBoxName.Column(1) then the data from that
column will be displayed.

 

[JamesJ]

The ContactID filed is an AutoNumber. The Row Source for the ComboBox
(cboContactID) is:
SELECT ContactID, Listing, Phone FROM tblContacts. The Bound
Column is 1. I'm finding this a bit odd: I set
ControlSource=cboContactID.Column(1)
for the text box and it displays the second column. Shouldn't it display the
first
column?

James