How do I get values from two fields in a combo box?

[Marc]

My question is probably worded wrong, but here is what I'm trying to do:

On a form, I want the user to be able to drop down a list of names and
military ranks. I've got the combo box selecting both fields on the drop
down box, but even though it displays both the name and the rank, it will
only fill in the person's name.

I need to have both the name and the rank associated with that name in the
field. Putting both the name and rank in one field on the reference table
won't work, because there are times when we need to filter by rank, not just
by name.

Is there any way to either have both the name and rank display in one field
or have the name display in one field while the rank auto displays in another?

 

[Tom Lake]

My question is probably worded wrong, but here is what I'm trying to do:

On a form, I want the user to be able to drop down a list of names and
military ranks. I've got the combo box selecting both fields on the drop
down box, but even though it displays both the name and the rank, it will
only fill in the person's name.

I need to have both the name and the rank associated with that name in the
field. Putting both the name and rank in one field on the reference table
won't work, because there are times when we need to filter by rank, not
just
by name.

Is there any way to either have both the name and rank display in one
field
or have the name display in one field while the rank auto displays in
another?

Sure. If your combo box control is called cb and looks like this:

Name Rank

then Name is cb.Column(0) and Rank is cb.Column(1)
You can use those to fill in any other fields on the form
in cb's AfterUpdate event. Example: fill in the Name and Rank fields:

Sub cb.AfterUpdate()
Me.Name = cb.Column(0)
Me.Rank = cb.Column(1)
End Sub

Tom Lake

 

[Marc]

Okay, I've got that entered in, but it still only populates the first field.
Is the other field (rank) supposed to be a combo box as well? I've got it as
a text box right now. Or is there another step I'm missing?

 

[John W. Vinson]

Okay, I've got that entered in, but it still only populates the first field.
Is the other field (rank) supposed to be a combo box as well? I've got it as
a text box right now. Or is there another step I'm missing?

Are you trying to COPY the name (firstname, lastname, middlename...?) and the
rank from one table into another table?

If so... DON'T. The table of personnel should contain all those fields, and
the person's unique ID as a primary key. *ONLY* that ID field should exist in
any other table.