INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Lookups in tables and combos on forms - column numbers

Lookups in tables and combos on forms - column numbers

(OP)
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.

RE: Lookups in tables and combos on forms - column numbers

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

RE: Lookups in tables and combos on forms - column numbers

(OP)
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-respo...

RE: Lookups in tables and combos on forms - column numbers

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

RE: Lookups in tables and combos on forms - column numbers

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close