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!!!

 

[Beetle]

It's rarely necessary to post to multiple groups, but if you are going
to do so you should cross-post to multiple groups at the same time.

That way an answer in one group is displayed in the other groups and
the people who volunteer their time here aren't duplicating their efforts.

 

[kealaz]

Jim,

strRowSource = strRowSource & Q & .Column(I) & Q & ";" & Q & .

There is a compile error in the above line. What should I change to make
it work?

THANK YOU SO MUCH FOR YOUR HELP!!!

First off, set the ColumnCount property via the form properties dialog to 2.
Set the column widths to whatever you want them to be, set the bound column
value property to 2. Then to define your rowsource:

strRowSource = vbnullstring
With Me.PART_NO

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

For I = 2 To 6 Step 2

If not isnull(.Column(I)) Then
If strRowSource <> vbnullstring then
strRowSource = strRowSource & ";"
End IF
strRowSource = strRowSource & Q & .Column(I) & Q & ";" & Q & .
Column(I+1) & Q
End If

Next I

Me.MANUF.RowSource = strRowSource

End If

End With

I think that should do it.

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!!!

 

[kealaz]

I'm sorry. I didn't realize. I will refrain from doing this in the future.
Thank you for cluing me in on proper etiquette.

 

[kealaz]

Jim,

THIS WORKED PERFECTLY. I figured out that one of the lines had been
"wrapped". I fixed it. Thanks so much for your help!!!