Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ID or description

Posted on 2014-04-20
9
Medium Priority
?
189 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
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 40

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

885 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