Lookup

[Morpheseus]

I am trying to develop an inmate database to control a few things at
work. In my tblPerformance I have a lookup field that gets the inmate
name from tblInmates. I have banged around now for four hours to try
to get it to show the Inmate Lastname, his Living Unit, and his Id #.
however, when I click on the combo box, I can see all three items, but
only his ID# is displayed after I make the selection. Here is the sql
expression from from combo box:

SELECT tblInmates.CSNo, tblInmates.LastName, tblInmates.LivingUnit
FROM tblInmates ORDER BY [LastName], [LivingUnit];

How do I get the field "Inmate" from tblPerformance to display the
full three items together?

Nick.

 

[JSand42737]

I am trying to develop an inmate database to control a few things at
work. In my tblPerformance I have a lookup field that gets the inmate
name from tblInmates. I have banged around now for four hours to try
to get it to show the Inmate Lastname, his Living Unit, and his Id #.
however, when I click on the combo box, I can see all three items, but
only his ID# is displayed after I make the selection. Here is the sql
expression from from combo box:

SELECT tblInmates.CSNo, tblInmates.LastName, tblInmates.LivingUnit
FROM tblInmates ORDER BY [LastName], [LivingUnit];

How do I get the field "Inmate" from tblPerformance to display the
full three items together?

Nick.

Nick

A combo box can only display one field at a time. If you want all three fields
displayed at once, then you have two choices.

Firstly, you can join all the fields together. If you need to store the ID
number in another table, then set the number of columns to be 2. Set the column
width of the first column to 0, so that the user cannot see it, and set the
other column to be whatever width is required. Then, your SQL should look like:

SELECT CSNo, CSNo & " - " & LastName & " - " & Living Unit AS Details
FROM tblInmates
ORDER BY LastName, LivingUnit;

The second option is to use text boxes to display the information from the
other columns. Just add two more text boxes to the form, and set the
controlsource of them to something like:

=cboName.Column(1)

Which will display the data from the second column of the combo called cboName
- note that combo box columns are 0-indexed, so the first column has an value
of 0, the second 1, and so on.

 

[Morpheseus]

Excellent. Thank you. I emailed your response to my work and will do
in in the morning. Thanks for your patience!

Nick.

 

[John Vinson]

I am trying to develop an inmate database to control a few things at
work. In my tblPerformance I have a lookup field that gets the inmate
name from tblInmates. I have banged around now for four hours to try
to get it to show the Inmate Lastname, his Living Unit, and his Id #.
however, when I click on the combo box, I can see all three items, but
only his ID# is displayed after I make the selection. Here is the sql
expression from from combo box:

SELECT tblInmates.CSNo, tblInmates.LastName, tblInmates.LivingUnit
FROM tblInmates ORDER BY [LastName], [LivingUnit];

How do I get the field "Inmate" from tblPerformance to display the
full three items together?

Change the query to

SELECT tblInmates.CSNo, tblInmates.LastName & " " &
tblInmates.LivingUnit & " (" & tblInmates.CSNo & ")" AS InmateName
FROM tblInmates ORDER BY LastName, LivingUnit;

and use two columns rather than three.

Note that you might have two inmates named Jones - does the CSNo
distinguish them sufficiently to prevent confusion? You could include
the Firstname as well.

 

[Morpheseus]

This is excellent. Thank you John. You wouldn't happen to know an
Access tutor in the Abbotsford BC area would you? I have to get
better at this and quickly. I have the Keystone CDs and Video tapes
.... but I am sick of Northwind Database!

I hired a local Access developer to help write me a database but he
was so smart he couldn't communicate. He banged off what he thought
was what I wanted but it did nothing for me. He was unable to HEAR my
needs for this project. He disigned a nice interface for it, but in
the end, it was not flexible enough to do anything for my employer.
It appeared to me as though he did not want me to know who to work on
the structure of VBA in the database. He would gloss over my
questions without explaing it to me. I should have just burned the
cash. At least I would have gotten some warmth!

If you do know anyone who has time to tutor... I can be reached at
(e-mail address removed)

Thanks...
N.

I am trying to develop an inmate database to control a few things at
work. In my tblPerformance I have a lookup field that gets the inmate
name from tblInmates. I have banged around now for four hours to try
to get it to show the Inmate Lastname, his Living Unit, and his Id #.
however, when I click on the combo box, I can see all three items, but
only his ID# is displayed after I make the selection. Here is the sql
expression from from combo box:

SELECT tblInmates.CSNo, tblInmates.LastName, tblInmates.LivingUnit
FROM tblInmates ORDER BY [LastName], [LivingUnit];

How do I get the field "Inmate" from tblPerformance to display the
full three items together?

Change the query to

SELECT tblInmates.CSNo, tblInmates.LastName & " " &
tblInmates.LivingUnit & " (" & tblInmates.CSNo & ")" AS InmateName
FROM tblInmates ORDER BY LastName, LivingUnit;

and use two columns rather than three.

Note that you might have two inmates named Jones - does the CSNo
distinguish them sufficiently to prevent confusion? You could include
the Firstname as well.

 

[John Vinson]

This is excellent. Thank you John. You wouldn't happen to know an
Access tutor in the Abbotsford BC area would you? I have to get
better at this and quickly. I have the Keystone CDs and Video tapes
... but I am sick of Northwind Database!

<g> You're not alone. I'm not THAT far from BC myself (Parma, Idaho)
but I don't know any active BC tutors. You might try posting an
opening at http://www.justaccessjobs.com and/or searching for Access
experts there; there are quite a few other job-search sites around as
well.

I hired a local Access developer to help write me a database but he
was so smart he couldn't communicate. He banged off what he thought
was what I wanted but it did nothing for me. He was unable to HEAR my
needs for this project. He disigned a nice interface for it, but in
the end, it was not flexible enough to do anything for my employer.
It appeared to me as though he did not want me to know who to work on
the structure of VBA in the database. He would gloss over my
questions without explaing it to me. I should have just burned the
cash. At least I would have gotten some warmth!

A hard lesson for you, and a strong warning to developers - *listen to
your client*!!! Unfortunately, it can be hard to tell ahead of time
how the developer/client relationship is going to work out; sorry this
one didn't cut the mustard.

If you do know anyone who has time to tutor... I can be reached at
(e-mail address removed)

Good luck. Just a warning; it's probably still unwise to post your
real email address in the public newsgroups. Swen viruses and spammers
routinely harvest the groups for them.