Combo Box look up

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box "Supervisor" to look up "Last Name", First Name", "MI",
and "Rank" from a query accessing the same table. I am doing this in a form
and to populate "Supervisor". It populate the table but only with the "Last
Name" and nothing else. What do I have to do to populate it with other three
columns?
 
I have a combo box "Supervisor" to look up "Last Name", First Name", "MI",
and "Rank" from a query accessing the same table. I am doing this in a form
and to populate "Supervisor". It populate the table but only with the "Last
Name" and nothing else. What do I have to do to populate it with other three
columns?
Click to expand...

Ummmm...

You shouldn't populate any other table with ANY of these fields.

If you just want to *display* the supervisors name, I'd suggest using
a query which concatenates the full name:

SELECT EmployeeID, [LastName] & ", " & [FirstName] & (" " + [MI]) & ":
" & [Rank]

This will make the combo box itself display "Gates, Bill: CEO" (or
whatever)...

John W. Vinson[MVP]
 
Ummmm...
You shouldn't populate any other table with ANY of these fields.
If you just want to *display* the supervisors name, I'd suggest using
a query which concatenates the full name:
SELECT EmployeeID, [LastName] & ", " & [FirstName] & (" " + [MI]) & ":
" & [Rank]
This will make the combo box itself display "Gates, Bill: CEO" (or
whatever)...
John W. Vinson[MVP]
Click to expand...

This is the code in the Row Source in the properties:

SELECT [mafb_alpha Query1].[LAST NAME], [mafb_alpha Query1].[FIRST NAME],
[mafb_alpha Query1].MI, [mafb_alpha Query1].RANK FROM [mafb_alpha Query1];

and it display all info we want, but when you click in it only shows the
last name in the form. How do we have it show Last name first name mi rank
in other words the rest of the info.

TSgt Teofilo J. Herrera
514 MXS/CCQ (AFRC)
 
and it display all info we want, but when you click in it only shows the
last name in the form. How do we have it show Last name first name mi rank
in other words the rest of the info.
Click to expand...

If you change the query to

SELECT [mafb_alpha Query1].RANK & " " & [mafb_alpha Query1].[LAST
NAME] & ", " & [mafb_alpha Query1].[FIRST NAME] &
[mafb_alpha Query1].MI FROM [mafb_alpha Query1] ORDER BY [Last Name],
[First Name];

it will show "Sgt. Mills, Janet A." in the combo box. Is that what you
have in mind, or do you want to display each part of the name in a
separate textbox on the form?

John W. Vinson[MVP]
 
John:
I am working on a form. it only works with names that are short, long
names give the following error on the form: The field is to small for the
amount of data.

Do you know what is the sytax for the "Bound Column" row in the Combo Box
settings; so it will show more than one column when you click on it?

John Vinson said:
and it display all info we want, but when you click in it only shows the
last name in the form. How do we have it show Last name first name mi rank
in other words the rest of the info.
Click to expand...

If you change the query to

SELECT [mafb_alpha Query1].RANK & " " & [mafb_alpha Query1].[LAST
NAME] & ", " & [mafb_alpha Query1].[FIRST NAME] &
[mafb_alpha Query1].MI FROM [mafb_alpha Query1] ORDER BY [Last Name],
[First Name];

it will show "Sgt. Mills, Janet A." in the combo box. Is that what you
have in mind, or do you want to display each part of the name in a
separate textbox on the form?

John W. Vinson[MVP]
Click to expand...
 
John:
I am working on a form. it only works with names that are short, long
names give the following error on the form: The field is to small for the
amount of data.

Do you know what is the sytax for the "Bound Column" row in the Combo Box
settings; so it will show more than one column when you click on it?
Click to expand...

I guess I'm not tracking what you are doing.

Are you trying to copy these four fields from one table into another
table? Why? Storing names redundantly is a bad idea, generally!

What is the Recordsource for the Form? What is the Control Source of
this combo box? What are you trying to store into it? Does your table
of people's names have a unique Primary Key (if this is the military,
the person's Serial Number)?

John W. Vinson[MVP]
 
John:
I am working on a db that someone else created, that person knows a lot more
than me on how access works.
we have an menu form with a block for the supervisor. It was created to
populate the table under supervisor column. the block on the form was
created to be type, by many users, people was writing it in many different
formats, I changed to a combo box looking at the last name, first name, mi,
and rank. the query I did looks the information but when I click at the info
it only put the last name in the block, remember I still working in the form.
if I change the bound column number from 1 to 2 I get the first name, if I
change to 3 I get the MI, if I change to 4 I get the Rank.

I tried the code you send me earlier and it give the information in the
block but it only worked with small names. I can used this if I could make
the field or block bigger. if you would like to help me directly my email is
teofilo dot herrera at AMC dot AF dot Mil.

Thank you, again

Teofilo J. Herrera, TSgt, USAFR
 
John:
I am working on a db that someone else created, that person knows a lot more
than me on how access works.
we have an menu form with a block for the supervisor. It was created to
populate the table under supervisor column.
Click to expand...

What is the datatype and size of the supervisor column? What
information do you want put into that column? If you're storing a
name, how do you want to handle the problems that will arise when
there are two people with the same name (Lt. Jim Jones, the one in
Division G, or Lt. Jim Jones, the guy at HQ)?
the block on the form was
created to be type, by many users, people was writing it in many different
formats, I changed to a combo box looking at the last name, first name, mi,
and rank. the query I did looks the information but when I click at the info
it only put the last name in the block, remember I still working in the form.
if I change the bound column number from 1 to 2 I get the first name, if I
change to 3 I get the MI, if I change to 4 I get the Rank.
Click to expand...

That's how a Combo Box works. It takes a single field (the Bound
Column) from its Row Source - the table or query upon which it is
based - and stores it into a single field, its Control Source.

Normally, the combo box will store one field but display other
information.

Let me ask again:

What is the Recordsource for the Form? What is the Control Source of
this combo box? What are you trying to store into it? Does your table
of people's names have a unique Primary Key (if this is the military,
the person's Serial Number)?

Could you open the Form in design mode, view its Properties, and look
at the properties of the combo box?
I tried the code you send me earlier and it give the information in the
block but it only worked with small names. I can used this if I could make
the field or block bigger. if you would like to help me directly my email is
teofilo dot herrera at AMC dot AF dot Mil.
Click to expand...

Private EMail support is for paying customers. I'm a freelance
consultant volunteering my time on the newsgroups for public benefit.
If you would like private support please contact me offline at jvinson
<at> wysardofinfo <dot> com.

John W. Vinson[MVP]
 
What is the datatype and the size of the supervisor column?
Text, 20 which I changed to 30.
What information do you want put into that column?
Rank Last Name, First Name MI.
If you're storing a name, how do you want to handle the problems that will
arise when there are two people with the same name (Lt. Jim Jones, the one in
Division G, or Lt. Jim Jones, the guy at HQ)?
This is no a problem for us, so I couldn't know.
the problem I have is if I change the supervisor rank this field will not
change using the expresion you give me earlier. after I increased the field
size at the table it worked. But now is the rank issue.

Do you know what is the syntax for the Bound Column in a Combo Box?
 
What is the datatype and the size of the supervisor column?
Text, 20 which I changed to 30.
What information do you want put into that column?
Rank Last Name, First Name MI.
Click to expand...

Well... don't.

What if after Lt. Sylvia Terrazas gets married, changing her name to
Sylvia Brown, she gets a promotion to Captain and also decides that
she'd prefer to be called by her middle name Janet? She's not Capt.
Janet Brown and your table is WRONG.

Storing names twice (as you're doing here) IS VERY RISKY. Names are
not unique; names are not stable. They should be stored ONCE, in a
table of "people"; if you need to refer to a person in some other
context (i.e. as a supervisor), you don't store the name, you store a
*link* to the name.
If you're storing a name, how do you want to handle the problems that will
arise when there are two people with the same name (Lt. Jim Jones, the one in
Division G, or Lt. Jim Jones, the guy at HQ)?
This is no a problem for us, so I couldn't know.
the problem I have is if I change the supervisor rank this field will not
change using the expresion you give me earlier. after I increased the field
size at the table it worked. But now is the rank issue.
Click to expand...

Suggestion:

Store a unique PersonID (the supervisor's military ID, or a long
integer linked to an Autonumber primary key). Store the name and rank
in the table that you're linked to.
Do you know what is the syntax for the Bound Column in a Combo Box?
Click to expand...

It's an integer 1 to 10, the position of the column in the combo's Row
Source query which should be stored in the Control Source field.

John W. Vinson[MVP]
 
Back
Top