Solved

ID or description

Posted on 2014-04-20
9
180 Views
Last Modified: 2014-04-21
If a combobox has two columns, a description ID, and a description (Text) and it's value is used for a condition in a query grid, is it acceptable to use the description rather than the description ID.

The reason I ask is that If someone creates a description and a description ID and we use the ID I would then have to make it impossible for the user to change the description associated with the Description ID. I have found IDs to be more reliable than Text values in queries so not sure which is best.
0
Comment
Question by:DatabaseDek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 15

Expert Comment

by:unknown_routine
ID: 40011408
You can make combobox read-only, So user cannot change description ID.

Using both ID and description ID are Equal. Both can be bind to a Grid column depending how the respective column in the Grid is defined.

If per your application logic user should be able to change the description in the combobox then using ID's could be better as per business logic 2 duplicate descriptions may be allowed,
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 200 total points
ID: 40011443
Yes is it acceptable to use the description rather than the description ID.  Which one to use depends on the situation.  For instance, with a table of Customers, a query that returns all the people having a last name that is displayed in a combo box needs to refer to the text field rather than the ID field.  

Ron
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40011822
Adding:
Assuming combo Box: cbo1, then you may use cbo1.Column(0) for ID, and cbo1,Column(1) for description.

Using cbo1 without column property refers to the bound column.

It is customary to use the description in a scenario like this: Assuming description is "abc"

Where ID equals the id of the record whose Description = "abc"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DatabaseDek
ID: 40012234
Thank you all.  Access 2002

The problem would not be with using a read only combo but the form that creates the combo's values. I force the user to create combo values in a form and then select them from the combo this stops users typing "Plot1" one moment and  "Plot 1" the next into a record,

I have has a situation where filtering on text caused the following problem:
Data was copied from a spreadsheet and pasted into an access table. But the data from the spreadsheet had text with trailing blank spaces. So if I selected "Bill" from the combobox the filter did not find "Bill  " with trailing empty spaces. This is extremely frustrating because the user can see "Bill  " in the table but not in the query results.

Should I run a routine to clear all spaces from all tables or is there a fix for this problem
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
ID: 40012300
When you're dealing with a combo, generally the "descriptions" are write-once, and cannot be changed (unless they're changed at the source). If the user enters "Bill   " then they cannot undo that, unless they go back to the table where "Bill    " is stored and alters it there.

If you need to cleanup those values before storing them, then do so - but be sure the user does not actually want to store "Bill    " instead of "Bill". While I cannot think of a scenario where the former would be needed, I suppose it could happen.

FWIW, here's where your real trouble comes from:
Data was copied from a spreadsheet and pasted into an access table
That should not happen. Access is not a spreadsheet, and although you can do this, you shouldn't. Instead, you should create import routines that allow the user to import data into the Access database, and you can then validate that data (to remove spaces, fix capitalization, etc) before you store it in the live database.
0
 

Author Comment

by:DatabaseDek
ID: 40012372
Thanks Scott

I would be very interested in how the data is validated. How would you clean up trailing spaces? and how do you check things like capitalization, that sounds like a nightmare to me? Is there an existing function for capitalization?
0
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 150 total points
ID: 40012644
You can remove leading/trailing spaces by using the various Trim() functions
Trim()---both
LTrim()---left only
RTrim()---right only

Create an update query that will trim the values.  Make sure you back up the database first.  it is always dangerous to run an action query when you haven't tested it.  Better be safe than sorry.

Update SomeField Set SomeField = Trim(SomeField);
0
 
LVL 84
ID: 40012748
There is no existing function to check capitalization. Often you use Regular Expressions for things like this, although if you have firm rules, you can sometimes just use VBA.
0
 

Author Closing Comment

by:DatabaseDek
ID: 40013081
Thank you all. As concise as usual
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question