[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

ID or description

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
DatabaseDek
Asked:
DatabaseDek
3 Solutions
 
unknown_routineCommented:
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
 
IrogSintaCommented:
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
 
hnasrCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DatabaseDekAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
DatabaseDekAuthor Commented:
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
 
PatHartmanCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
DatabaseDekAuthor Commented:
Thank you all. As concise as usual
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now