Find & Replace in linked field (2)

[Lars Brownies]

Like in the Northwind database I've made a combobox in a table's field. The
field is ID_country but in datasheet/tableview you see the actual
Countryname (which in fact resides in another table). I'm trying to do a
Find & Replace action on this field but I can't get it to work. When I
search on the actual text value, it cannot be found. When I search on the ID
number, it ís found, but the replacement (another ID number) doesn't take
place. When I hit replace Access says the text cannot be found. Can't I use
Find & Replace in this setting or am I doing something wrong? Other options?

Thanks,

Lars

 

[Jeff Boyce]

Lars

I believe you've uncovered another reason why the consensus in these
newsgroups is that the Lookup data type in tables is ... confusing. Believe
me when I tell you that "confusing" is the mildest description you'll find!

The confusion (and problem) happens because Access stores one value (the
ID), but displays another (the looked-up value).

The confusion is also due to the fact that you are trying to work directly
in the tables instead of via the forms. Access tables store data, Access
forms display it. Use the forms, Lars!

And to get a "lookup" in a form, just use a combobox control.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Like in the Northwind database I've made a combobox in a table's field. The
field is ID_country but in datasheet/tableview you see the actual
Countryname (which in fact resides in another table). I'm trying to do a
Find & Replace action on this field but I can't get it to work. When I
search on the actual text value, it cannot be found. When I search on the ID
number, it ís found, but the replacement (another ID number) doesn't take
place. When I hit replace Access says the text cannot be found. Can't I use
Find & Replace in this setting or am I doing something wrong? Other options?

You are seeing some of the reasons a lot of us dislike Lookup Fields:
http://www.mvps.org/access/lookupfields.htm
has some critiques. One thing to consider is that a lookup field is NEVER
necessary! It makes it easier to use a Table Datasheet for data interaction,
but most of us would never let a user even *see* a table datasheet; it's just
too easy to put a combo box on a Form to make the hassles of a Lookup
worthwhile.

That said... an Update query updating your table to insert the numeric ID
number would be your best bet. Find and Replace is limited; Lookup Fields are
limited; and their limitations overlap in this regard.

 

[aaron.kempf]

I disagree with parts A, B and C of the whole 'avoid lookups' argument
sorry.

I might shy away from using Lookup Wizard datatypes-- but they are by
far-- one of my favorite features of Access
Access without lookups is called Excel

I'd recommend to the user is to use a replace statement in a update
query.

-Aaron

 

[Lars Brownies]

Thanks for your comments.

I noticed that a combobox on a form is confusing as well. It will allow a
Filter and a Find action on the external value but it won't allow the
replace action. Also it won't allow the Find option and the Replace option
for the actual ID field value.

I'll do these replacement via an update query.

Lars

 

[Larry Linson]

I might shy away from using Lookup Wizard
datatypes-- but they are by far-- one of my
favorite features of Access

I'm not surprised, Mr. Kempf -- your perceptive and intuitive nature is
legendary.



__________ Information from ESET Smart Security, version of virus signature database 3979 (20090331) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[a a r o n _ k e m p f]

well it's silly that you guys don't use Access 'just becaue someone
told you not to'.

I disagree with the premise that lookups are bad.. yes, the lookup
wizard is kinda silly..
but if I want to display a column (in a table) with both the combobox
_AND_ the plain valu.. it's easy to do this

lookups are just about the only thing in Access that I like.

-Aaron