How do I add a COLUMN to combo box

[kealaz]

I have two combo boxes on a form. The first combo box gets info from
tblPOTODO using a select query and the following code.

SELECT tblPOTODO.PART_NO, tblPOTODO.NAME, tblPOTODO.MANUF1,
tblPOTODO.MANUF1_PN, tblPOTODO.MANUF2, tblPOTODO.MANUF2_PN,
tblPOTODO.MANUF3, tblPOTODO.MANUF3_PN FROM tblPOTODO;

The second combo box is populated when a selection is made of the first
combo box. The selections of the second combo box will be:
MANUF1
MANUF2
MANUF3

Once a selection is made on that second combo box, I would like to populate
a text box [MANUF_PN] with the corresponding MANUF_PN.

I'm using the following code to populate the second combo box from the
selection made on the first combo box.

*****************************************************
With Me.PART_NO

If IsNull(.Value) Then
Me.MANUF.RowSource = ""
Else

For I = 2 To 6 Step 2

strValue = .Column(I) & vbNullString

If Len(strValue) > 0 Then
strRowSource = strRowSource & ";" & Q & strValue & Q
End If

Next I

Me.MANUF.RowSource = Mid$(strRowSource, 2)

End If

End With
*****************************************************

My second combo box currently has these options.
MANUF1
MANUF2
MANUF3

How can I change the above code so that my combo box has two columns? Like...

MANUF1 | MANUF1_PN
MANUF2 | MANUF2_PN
MANUF3 | MANUF3_PN


If I can make this happen, then, would I be able to use the value of the
second column of the line selected as the "value" of my text box? I think
this would work.

Please help me make this happen. Thanks SO MUCH!!!

 

[Steve Sanford]

First I have to tell you that "NAME" is not a good name for a field. Besides
not being very descriptive ("Name" of *what*?? Your dog? The month? A
vehicle??), it is a reserved word in Access. For a list of "Bad words", see:

http://allenbrowne.com/AppIssueBadWord.html


But the main issue is that you have committed "Spreadsheet"!! Your table is
not normalized.

These are your fields:

PART_NO
NAME
MANUF1
MANUF1_PN

MANUF2
MANUF2_PN

MANUF3
MANUF3_PN


What are you going to do when you have to add a 4th manufacturer?? You will
have to edit your table(s), forms, queries and reports!!

Read up on Normailzation. It will save you huge amounts of time. If you
continue with your current table design, you are guaranteed to have major
headaches.

Your code can be modified to do what you want, but first, please read about
normalization.