Hi everyone,



First, thanks for the feedback on autonumbers. Coincidentally, the

February edition of the Smart Acces newletter

www.smartaccessnewsletter.com) has an article, "Everything About 

Autonumbers", with a neat, downlaodable, bug-free add-in that allows you

to establish seed and increment values for autonumbers at will (plus

change autonumbers to long integers, if needed).



Need some clarification around using combo boxes vs. look-up fields (in

tables). Bear with me. Here's the problem:



Say I have a basic combo box on a form with a two column validation table

(TableA) as it's record source. Column1 contains a numeric value (say, an 

autonumber primary key), and column two contains a text value. If my combo 

box binds Column1 to its control source in TableB, a numeric value is 

stored in the respective field of TableB. But when I create a report that 

prints said field in TableB, the report prints the meaningless numeric 

value, instead of it's related (meaningful) text value.  So the solutions 

are:



1. Bind Column2 of the combo box to it's control source in TableB. This 

will store the text value which the report will then print.  However, 

storing the text value is less memory efficient than storing the numeric 

value. Do I care about that?



2. Use a Look-up field (i.e., embed as query) in TableB. This will bind a 

numeric value, while allowing the report to print the related text value. 

However, some folks feel that to embed queries (i.e., use Lookup fields)

in tables is to sin against generally accepted relational database design 

best practics.



3. Something I've completely overlooked (code, perhaps) that will allow 

TableB to store a memory efficient numeric value AND allow the associated

report to print the relevant text value.



What's the optimal solution?

Why not base your report on a query? Link table A and B by the numeric 

field and then display the text from table B.



Brian

One other way to go is use a combo box on your report that is set up the

same way as the one on your form.



I'm intrigued by the statement in the original post that some people regard

lookup fields as a "sin against generally accepted relational database

design best practices.".  I've not come accross any criticism of lookup

fields--can someone give me a thumbnail sketch of the argument?



Thanks!



-Roy



-----Original Message-----

From: brian_skelton@o... [mailto:brian_skelton@o...]

Sent: Friday, March 02, 2001 1:44 AM

To: Access

Subject: [access] Re: Combo Boxes vs. Lookup Fields (in Tables)





Why not base your report on a query? Link table A and B by the numeric 

field and then display the text from table B.



Brian

> I'm intrigued by the statement in the original post that some people 

regard lookup fields as a "sin against generally accepted relational 

database design best practices." I've not come accross any criticism of

lookup fields--can someone give me a thumbnail sketch of the argument?



Roy,



I had in mind a remark by Roger Jennings in Que's "Special Edition Using 

Microsoft Access 2000". Jenning's observes:



"The lookup feature has generated controversy among seasoned database 

developers. Relational database purists object to embedding queries as 

table properties. Another objection to the use of forign-key, dropdown 

lists is that it is easy for uninitiated users to inadvertanly change data 

in a table after opening the list."



He goes on to state that:



"Access 2000's lookup feature, however, is a useful toll, especially for 

new database users."



Being a relatively new database user, I was wondering if lookup fields 

might have unanticipated adverse consquences as databases become more 

complex.



Do you, or others, tend to use lookup fields regularly? 

Hey Bob,



Thanks for passing that on--it's interesting.  My sense is that the second

criticism (that dropdowns make it easy to inadvertantly change data) applies

equally to dropdowns on bound forms & so I'm personally not very swayed.

IMHO if you need to protect your data that carefully, you're not going to

expose the raw tables in the first place.  As for the "purists" objections,

without knowing more of the substance of those objections, it's hard to know

how much weight to give them.



I can't speak for others, but I do tend to use lookup fields a bunch--in

general I try to put as much info into the field definitions as possible, so

as to make form development (& re-development) easier.



I can say that one down side to hiding the bound column on these types of

fields (e.g., where you're storing a numeric code, but showing a descriptive

text label) is that using Access' Find feature can become confusing to

users--Find will look only in the numeric data that are actually getting

stored.  For this reason I will sometimes show both the stored value and the

descriptive text in the drop-down.  So for instance I'd make the RowSource

something like:



SELECT Purpose, Purpose & " (" & PurpDesc & ")" AS Description FROM Purposes

;



One other potential for complications is when you use a hard-coded list of

values (e.g. a "Value list") for your combo box values rather than a SELECT

statement.  Unfortunately, as soon as you drop that field onto a form you

get a second copy of the value list, which has to be maintained separately.

So if you ever have to add a category you either have to change it both on

the field directly and on any forms/reports where it appears, or else suffer

the inconsistency.  Strictly speaking the same is true of SELECT statements

(e.g. if you ever have to edit the SELECT statement itself you'll have to do

so on every control bound to that field separately) but in my experience I

just about never need to do that--I'm usually just adding or deleting values

& those can be done centrally in the table to which the SELECT statement

refers.



Cheers,



-Roy



-----Original Message-----

From: Bob Bedell [mailto:bdbedell@m...]

Sent: Friday, March 02, 2001 4:22 PM

To: Access

Subject: [access] Re: Combo Boxes vs. Lookup Fields (in Tables)





> I'm intrigued by the statement in the original post that some people 

regard lookup fields as a "sin against generally accepted relational 

database design best practices." I've not come accross any criticism of

lookup fields--can someone give me a thumbnail sketch of the argument?



Roy,



I had in mind a remark by Roger Jennings in Que's "Special Edition Using 

Microsoft Access 2000". Jenning's observes:



"The lookup feature has generated controversy among seasoned database 

developers. Relational database purists object to embedding queries as 

table properties. Another objection to the use of forign-key, dropdown 

lists is that it is easy for uninitiated users to inadvertanly change data 

in a table after opening the list."



He goes on to state that:



"Access 2000's lookup feature, however, is a useful toll, especially for 

new database users."



Being a relatively new database user, I was wondering if lookup fields 

might have unanticipated adverse consquences as databases become more 

complex.



Do you, or others, tend to use lookup fields regularly?