Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ID or description

Posted on 2014-04-20
9
Medium Priority
?
186 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 800 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 31

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
Technology Partners: 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 600 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 39

Assisted Solution

by:PatHartman
PatHartman earned 600 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 85
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 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