Multiple lookups to the same table and field

[Kevin Donaker-Ring]

My apologies in advance as I'm an Access newbie. I've been doing light
database work for a couple of decades -- mostly in Paradox for DOS -- and I'm
now moving to Access. But I'm running across something I would like to do and
I can't figure out how to get it done.

Here's the situation. I'm dealing with international students. Four of the
fields in the Students table have values that refer to a country. Each one
uses a two-letter code that is defined by the US Department of State. There
is a lookup table that contains the full country name, with a primary index
using the two letter code. It is common for at least one of the four fields
to have a different value from the other three, and sometimes each field will
be different -- one field refers to the country where our partner office is
located, a second field refers to the country where the student legally
resides, a third field refers to the country where the student was born, and
a fourth field refers to the student's nationality.

First of all, I would like to be able to display the full country name to
the end users when they are looking at student information. Secondly (and I
have not yet started on this form), I would like to have lookups defined for
these four fields.

I added the CountryList table to the Relationships four times and created
four 1:M links -- one for each field in the Students table going to the
primary key in each instance of the CountryList table.

But I cannot seem to find a way to add the fields to the form I'm creating.
At least not four times. The table still only shows up once.

It's likely that I'm using the wrong approach entirely. In Paradox for DOS I
would have actually done this programatically with a calculated field and a
dynamic array.

Thanks in advance for any advice!

--Kevin

 

[Kevin Donaker-Ring]

First of all, I would like to be able to display the full country name to
the end users when they are looking at student information. Secondly (and I
have not yet started on this form), I would like to have lookups defined for
these four fields.

.. . . the "Secondly" part was in reference to creating a Data Entry form.

--Kevin

 

[Boyd Trimmell aka HiTechCoach]

On the form, I woudl use a combo box to select the country. It can even be
set up to store the country code, but dsiplay the country's full name.

If you want the user to select the two letter code, I woudl still use a
combo box and include the full name as the second column. Then in a text box
next to the combo box, set the control source to display the full name using
something like:

=MyComboBox.Column(1)

 

[Kevin Donaker-Ring]

Thanks, Boyd, but unfortunately that doesn't solve the problem. Yes, it works
for one. But try to link four different fields to the one table, each showing
a different country. So far, I can't get it to work. It will only allow me to
link to one field.

Here's a radically simplified structure for the Students table:

SdtID : AutoInc
SdtLastName : Text
SdtFirstName : Text
OverseasPartner : Text
CountryOfResidence : Text
CountryOfBirth : Text
CountryOfNationality : Text

Each of the last four fields contain a two-letter code pertaining to the
appropriate country.

The Countries table looks like this:

CtryCode : Text (Primary Key)
CtryFullName : Text

Now let's say I have a student Albert Schweizer who was born in Austria,
whose nationality is German, is living in Italy, and who applied through our
partner office in Switzerland.

In the table it would show

Schweizer Albert SZ IT AS GM

But I want the form to show

Student: Albert Schweizer
Applied through: SWIZERLAND
Resides in: ITALY
Born in: AUSTRIA
Citizen of: GERMANY

I can get Access to only show ONE of the above relationships, not all four.

Any ideas?

--Kevin

 

[Clif McIrvin]

The Countries table looks like this:

CtryCode : Text (Primary Key)
CtryFullName : Text

Now let's say I have a student Albert Schweizer who was born in Austria,
whose nationality is German, is living in Italy, and who applied through our
partner office in Switzerland.

In the table it would show

Schweizer   Albert   SZ   IT   AS   GM

But I want the form to show

Student:              Albert Schweizer
Applied through: SWIZERLAND
Resides in:          ITALY
Born in:                AUSTRIA
Citizen of:            GERMANY

<snip>

Untested:

Use Combo Box controls for your country name fields.

Instead of setting up table relationships, use the Row Source property
of the combo boxes on your form (set Row Source Type to Table/Query):


If this is view only, try

SELECT Countries.CtryFullName
FROM Countries
WHERE Countries.CtrytCode = Me.CountryOfBirth ;

change the Me.CountryOfBirth for each of the four controls.

If the user needs to be able to choose the country you can use the
Bound
Column and Column Widths properties to control both what is displayed
and stored (you can display and select on the full name and store only
the
short code if you wish).

in this case you would change the SELECT statement to

SELECT Countries.CtryCode, Countries.CtryFullName
FROM Countries
ORDER BY Countries.CtryFullName ;

(The ORDER BY clause is optional, and could be either field.)



I think this is what Boyd was suggesting.

 

[Clif McIrvin]

I forgot to add that if you click on the Expression Builder button
(...) next
to the Row Source property Access will show you a query design grid;
you
don't need to code the SQL manually.

 

[Kevin Donaker-Ring]

Thanks, Clif, I'll give it a go. It may take me a bit to get it all figured
out (as I said, I'm a newbie), but I'll let you know the results.

 

[Kevin Donaker-Ring]

Clif,

Well, it *almost* worked. But the combo box doesn't show a value. It's
blank. The dropdown does show the full country name when I click the control,
but that's it. Of course, if I click to drop down and then click on the one
value there, the value then shows in the box.

Obviously, you put me on the right track. Maybe some sort of event coding to
force the combo box to display the value?

Hmmm, and as I tested this, I decided to navigate to other records. The
values from the initial record stay with the combo boxes. It looks as though
the queries are not being performed on each record, only the first one.

Thanks for any thoughts, tips, or pointers in the proper direction.

--Kevin

 

[Clif McIrvin]

Clif,

Well, it *almost* worked. But the combo box doesn't show a value. It's
blank. The dropdown does show the full country name when I click the control,
but that's it. Of course, if I click to drop down and then click on the one
value there, the value then shows in the box.

Obviously, you put me on the right track. Maybe some sort of event coding to
force the combo box to display the value?

Hmmm, and as I tested this, I decided to navigate to other records. The
values from the initial record stay with the combo boxes. It looks as though
the queries are not being performed on each record, only the first one.

Thanks for any thoughts, tips, or pointers in the proper direction.

--Kevin

Yes, I pointed you to an 'unbound control' ... not to be confused with
the 'bound column' property of the combo box! <g>

The form current event would be a good place to place your code; you
can even put in a test for new record in case you want different
behavior. (type 'newrecord property' in the VBA help search box.)

I set unbound combo boxes with a statement like

Me.myComboBox = SomeValue

or, following up on Albert Kallal's comments about explicit coding,

Me.myComboBox.Value = SomeValue

There are *very* good reasons to explicitly invoke the .value property
when setting another variable to the value of a combo box, as

SomeVariable = Me.myComboBox.Value

Good luck!

 

[Kevin Donaker-Ring]

Thanks again, Clif. One last question. I note that in the code shown in
several messages, when referencing the current table, the prefix of 'Me.' is
used. Is that just common shorthand in the community to say "put your table
name here" or is it an actual Access shorthand that can be used in code to
refer to the current table?

Best,

--Kevin

 

[Pete D.]

Good question http://www.mvps.org/access/forms/frm0031.htm
it is a real keyword to reference, link above has always been a keeper for
me, especially late at night when I'm foggy.

 

[Clif McIrvin]

Kevin, "Me." is a defined Access term pointing to the current form
(not table).

I still have trouble calling up the specific help topic I want, but it
is getting easier; except right now I couldn't lay my fingers on the
topic that explains that.

It took me a good while - many attempts over many days - to begin to
get comfortable with using the MS help files that are built into
Access, but if one is serious about doing their own Access development
it is well worth the effort! In my case, I found it helpful to Browse
the help Tables of Contents several times.

Back to "Me." --- when you are refering to an object that exists in
the form that contains the code module you are writing you can use me.
to refer to the form object. If you need to refer to a different form
you need the more general

forms![formname]

syntax given in the "form object" help topic.

(Be aware that you get different help topics from the Access window
than you get from the Visual Basic Editor window.)