Using Lookups and Display in Forms

[Marc]

I'm back in the Access development mode and seem to have forgotten how to do
2 things that should be simple. I have a database with a table of contacts,
companies and activities. The idea is that I'll put in a list of companies,
a bunch of contacts that work at each company, and an activities form that
allows me to input every activity I do for each contact.

The activities table includes a lookup field (also named contactID) for
contactID field in the contact table so that each activity is assigned to
only one contact by selecting the contact's contactID. In the contacts table
I have done the same with the companyID so that each contact is assigned a
company by using the companyID. The lookup allows me to see the contact name
in the first example and the company name in the second example so that
selection is easy. Here's what I wanted to do in the activities form:

1) I have a lookup table for the contactID which looks up the contact's name
in the contact table so that "firstname, middlename, lastname" appears in
the dropdown insead of just the contactID number. Unfortunately 2 things
occur: (a) I see the firstname, middlename, lastname fields but I want them
sorted by the lastname field -- it sorts by firstname; (b) when I finish
using the dropdown, all I see is the firstname field and the other two
disappear. I'd like to have all 3 fields appear.

2) I'd like to have a field which merely displays the companyname field from
the company table on this form. Since I have selected a contactID, I have a
companyID lookup in the contact table. I'm not sure how to set up this query
and a field that shows the results of the query in the form. Obviously this
does not have to be stored because it already has been stored and it is used
just for dispay purposes only...

Thanks all so much in advance...

Marc

 

[Pieter Linden]

First off, lookups are AWFUL...
use a combobox, columncount = 2. widths 0;1 rowsource: "SELECT
Firstname & " " & MiddleName & " " & LastName FROM tblContacts ORDER
BY LastName;"...

Or better... "SELECT LastName & ', '& FirstName & ' '& MiddleName As
CustName
FROM Contacts ORDER BY LastName, FirstName, MiddleName;"

2) I'd like to have a field which merely displays the companyname field from
the company table on this form. Since I have selected a contactID, I have a
companyID lookup in the contact table. I'm not sure how to set up this query
and a field that shows the results of the query in the form. Obviously this
does not have to be stored because it already has been stored and it is used
just for dispay purposes only...

if you have this in a combobox, you could include the company name in
the combobox's rowsource:

"SELECT ContactID, ContactName, Company.."
then have 3 columns, with column(0) and Column(2) having zero width.
Then you could set your company textbox's rowsource =
cboContact.Column(2) and it would show up...

 

[Albert D. Kallal]

1) I have a lookup table for the contactID which looks up the contact's
name

in the contact table so that "firstname, middlename, lastname" appears in
the dropdown insead of just the contactID number. Unfortunately 2 things
occur: (a) I see the firstname, middlename, lastname fields but I want them
sorted by the lastname field -- it sorts by firstname; (b) when I finish
using the dropdown, all I see is the firstname field and the other two
disappear. I'd like to have all 3 fields appear.

Since the combo box is based on a query, then simply bring up the combo box,
click on the sql data source, and set the sort for last name to be
descending. You should probably thus make the combo box have CntactId,
LastName, middle, First. (Make whatever field you want to search by the 2nd
field to the contact id). Once the user selects the name, then contactId is
set. You can either display Middle, first etc by placing additional text
boxes on the screen, and using dlookup's as the source for each text box.
However, that is a bit messy, so you can consider basing the form on a sql
that joins in those additonal fields. Even more cool is to consider using a
sub-form. I explain how to do this at:
http://www.attcanada.net/~kallal.msn/Articles/fog0000000005.html

2) I'd like to have a field which merely displays the companyname field from
the company table on this form. Since I have selected a contactID, I have a
companyID lookup in the contact table. I'm not sure how to set up this query
and a field that shows the results of the query in the form.

Yes, this would be double lookup. So, yea, I would use my sub-form idea, and
build a query that displays the contactId, Contact Name, and Contact
Company. You then base the sub-form on this query and you thus get to
display all the contact info, and you ONLY need to supply the contactId in
you main form to display all of this stuff! (use the link master/child in
the sub-form based on contactId).