Default Text field to Lookup field

[Guest]

I was given a table that I am redesigning. The orig table had a City text
field and I added a popup City2 field that goes against a new Cities table.
There are 10,000 records and I want to populate my new City2 field with the
contents of the orig City text field. How to do it?

 

[jahoobob via AccessMonster.com]

With the information you have given the answer is:
one record at a time!
If there is no correlation between the current City text and the new Cities
table I don't see a way to do this other than going record by record and
adding the new City2.
BTW, it is not a good idea to have a popup field in a table. Use the popup

 

[Guest]

Hi Jahoobob,

The first part of your answer SEEMS unlikely. Suppose the City field in
record x has a value of Phoenix. There is a Cities table containing, among
other values, a record with Phoenix as its City value. The Cities table is
related to the City2 field in the first table. It is easy enough to pull
done the Cities2 popup to get Phoenix to popup and select. But there are
10,000 records and the City field is populated. I cannot imagine that in
2007 this is not possible with a Default expression, perhaps a SQL
expression, or with a macro. Access cannot be THAT bad!!

To part 2 of your answer, how then shall I define the Cities2 field. I want
it to relate to the Cities table and I want it never to contain the value of
Phoenis, rather 0000062 for the Phoenix record.

Thanks in advance, again!

 

[Rick Brandt]

Hi Jahoobob,

The first part of your answer SEEMS unlikely. Suppose the City field
in record x has a value of Phoenix. There is a Cities table
containing, among other values, a record with Phoenix as its City
value. The Cities table is related to the City2 field in the first
table. It is easy enough to pull done the Cities2 popup to get
Phoenix to popup and select. But there are 10,000 records and the
City field is populated. I cannot imagine that in 2007 this is not
possible with a Default expression, perhaps a SQL expression, or with
a macro. Access cannot be THAT bad!!

An update query that uses both tables as inputs can do this easily.

To part 2 of your answer, how then shall I define the Cities2 field.
I want it to relate to the Cities table and I want it never to
contain the value of Phoenis, rather 0000062 for the Phoenix record.

Use a ComboBox on a FORM that is used for editing records. No need for the
drop-down in the table itself.