List Box Displaying ID Numbers instead of text

  • Thread starter Thread starter rkeefover
  • Start date Start date
R

rkeefover

I have on my form a combo box which allows the user to enter in an address
and then it will find the record associated with that address and display it
in List boxes containing the City, State, Zip and so on. The problem is that
my City and State list boxes are displaying the number ID rather than the
word. So for Wichita, its showing 613, which is the ID number, rather than
Wichita. I'm sure this is any easy thing to solve but i cant seem to figure
it out and i'm kinda new to this. Any help would be appreciated :)
 
Like combo boxes, list boxes can have multiple columns. If, e.g. your record
source for the list box is SELECT CityID, CityName FROM Cities, you will get
two columns back. The first column (which should be the bound column) will be
CityID, the 2nd column will be CityName.

Set the Column Count to 2, Bound Column to 1, and Column Widths to 0";1"
(which will hide the CityID, and give a 1" width for displaying the
CityName).....
 
It has to do with how your combo box properties are set up.
First, if you have two columns ID ans Word then you need to set the Column
Count property to 2.
If ID is the first column (it should be), then the Bound Column should be 1
Then in the Column Widths property, set it to 0";3.0" The 0" will make the
ID column (first column) invisible. The 3.0" can be however wide it is you
need to show the entire address.

Then your combo will show only the second column, the one the user should
see, but when you use the value of the combo to do a search, it will return
the value of the bound column.

So the user would see Wichita, but if you look at the value in the combo it
will be 613.

X = Me.MyCombo

x will be 613

y = Me.MyCombo.Column(1)

y will be Wichita

Column numbers start with 0
 
rkeefover,
Let's just work with City. (ex. cboCity)
I'll assume these columns in cboCity
CityID City
613 Wichita

Given that example, your combo properties should be...
ControlSource = [CityID] (or can be unbound)
NoOfCols = 2
ColumnWidths = 0"; .75"
Bound Column = 1

This allows the user to select a city by name.
The combo "displays" that name.
But the table field bound to cboCityID is capturing the CityID value, as
it should.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Well you both were right about all the properties, but i tried the settings
you suggested and it didnt change anything... Actually the presets already
matched the suggested 2 columns, bound column 1, and width 0";1" I tried
enlarging the width to 3 and then even 10 just to see, but i still see the ID
number. Perhaps my code is pulling the wrong column?

This is the combo boxes code: SELECT [Inspection Addresses].ID, [Inspection
Addresses].City FROM [Inspection Addresses];

Inspection Adresses is the table and its pulling from that tible the .ID
first, and the .City second... as you said it should. So im not sure why it
isnt working...
 
Well you both were right about all the properties, but i tried the settings
you suggested and it didnt change anything... Actually the presets already
matched the suggested 2 columns, bound column 1, and width 0";1" I tried
enlarging the width to 3 and then even 10 just to see, but i still see the ID
number. Perhaps my code is pulling the wrong column?

This is the combo boxes code: SELECT [Inspection Addresses].ID, [Inspection
Addresses].City FROM [Inspection Addresses];
Click to expand...

Speculation here: might City be a Lookup Field in the definition of the
[Inspection Addresses] table? If so, you're seeing what's actually in the
table (the foreign key to the City lookup table), rather than the city name,
which exists only in the City table.

If you have a lookup field based on a table Cities, try

SELECT [Inspection Addresses].ID, [Cities].City
FROM [Inspection Addresses] INNER JOIN Cities
ON Cities.CityID = [Inspection Addresses].[CityID]
ORDER BY [Cities].[City];
 
You're right it is a lookup and that makes sense then! However now with the
code that you suggested it brings up an input box at the form startup asking
for me to enter a variable for: Cities.CtyID.
 
You're right it is a lookup and that makes sense then! However now with the
code that you suggested it brings up an input box at the form startup asking
for me to enter a variable for: Cities.CtyID.
Click to expand...

That's because I cannot see your screen, cannot see your field definitions,
and have no way to know what the name of the fields in your table might be.

Please use my suggestion AS A SUGGESTION, look at your *own* database (which
you can hopefully see, although I cannot) and adapt my suggestion to your
actual situation.
 
Hey thanks for the tip, i HAD customized the code, not just copied it, but i
spelled one thing wrong which is why i was confused. That got my list boxes
working correctly but if you wouldnt mind one last question, i am displaying
that information in text boxes at the point and since they are also drawing
information from the table that uses a lookup the text boxes are displaying
the numbers as well instead of text. Is there a way to fix that or should i
use something else to display the results? And sorry it seems like i am just
using all ur ideas instead of creating it myself, but i actually did quite a
bit to set the rest of the thing up this is just some of the finishing
touches, as i said i am relatively new to this and not an expert as yourself
so i appreciate the help alot guys :)
 
Hey thanks for the tip, i HAD customized the code, not just copied it, but i
spelled one thing wrong which is why i was confused.
Click to expand...

Well... you didn't SAY that... <g>

Sorry for snapping at you, but I can only go by what you post!
That got my list boxes
working correctly but if you wouldnt mind one last question, i am displaying
that information in text boxes at the point and since they are also drawing
information from the table that uses a lookup the text boxes are displaying
the numbers as well instead of text. Is there a way to fix that or should i
use something else to display the results?
Click to expand...

Set the control source of the textbox to an expression looking in the actual
table where the information resides, or use a Combo Box based on that table
(bound to the numeric ID but displaying the text).

And... TURN OFF ALL YOUR LOOKUP FIELDS. As you can see, they are often far
more hassle than benefit!
And sorry it seems like i am just
using all ur ideas instead of creating it myself, but i actually did quite a
bit to set the rest of the thing up this is just some of the finishing
touches, as i said i am relatively new to this and not an expert as yourself
so i appreciate the help alot guys :)
Click to expand...

You're welcome.... hope you can understand how hard it can be to know what you
have or haven't done, given the narrow bandwidth of the newsgroup medium!
 
Back
Top