Solved

ID or description

Posted on 2014-04-20
9
174 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 36

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

820 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