Lookups in tables and combos on forms - column numbers 1

[Moss100]

Hello,

Please can someone clarify the following.

If I create a lookup for a field in table design, I think the first column in the lookup table is referred to as column 1 (which may often be an ID field)

If however I use a combo on a form as a lookup to a table, the first column is column 0.

Is the above correct or am I getting confused?

From what I read the above seems to be right, but it seems odd that one would start at 1 and the other at 0.

Thank you Mark.

 

[dhookom]

Mark,
You are confused. First IMO never use lookup fields in tables

http://access.mvps.org/access/lookupfields.htm.

When referencing the combo box properties on a form, you never want to bind to the 0 column. This is an index based on the position in the order of values. However, when referencing the column numbers in VBA, the 0 column is actually the first column. So there is a difference in the form view vs the VBA code.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Thank you - on your advice I will remove all lookups from my tables.

I read this article which seems to disagree somewhat, but your advice has
been excellent to date.

Thank you Mark.

http://improvingsoftware.com/2009/10/02/blog-response-lookup-fields-in-access-are-evil/

 

[dhookom]

Regarding lookup fields, I have seen too many questions by users who had implemented lookup fields but had multiple issues understanding what was actually stored and how to get the title/text field to display in reports, queries, forms,... They also have issues creating filters on the lookup fields.

I personally want to see the actual values stored in the fields. Also, I typically work with SQL Server tables that don't support lookup fields defined in tables.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Well, personally I use look-up tables (for domains). They form part of my CDM / ERD plus as my back-end database is MS SQL, domains are not supported and so lookup tables are the only way to create domains for data set values. (you could hard code check constraints, but that can get ugly and hard to manage over time)

At least if you design the CDM incorporating domains / look-up tables with clearly defined relationships, you can see what is going on and code your application accordingly, I don't think I would obfuscate the relationship in the manner lookup-fields in access does.

If Duane says not to use it, it's usually best to leave it alone!


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music