POPULATE a textbox based on combo box selection -- QUESTION

[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 are:
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!!!

 

[PJFry]

Couple of things:

If MANUF1 and MANUF1_PN are in the same table, why do you want to have
MANUF1_PN stored again?

If you look in the properties for your second combo box, under Format you
will see a section called Column Widths. It will looks something like
0";1";0". Assuming the MANUF1_PN is in your rowsource, you just need to find
which column it is that is set to 0 and change it 1 (or some approrpiate
length).


Now, if you still want to store that text value:

Assuming the MANFU1_PN is in your combo box, set the value of the text box
based on the column number where the _PN extension resides.

Me.txtBox = MANUF.Column(1) where the (1) is the appropriate colum.

If the _PN extension is not in you combo box, you can simply add the _PN to
the end of the expression.

Me.txtBox = MANUF.Column(1) & "_PN"

Hope this helps
PJ

 

[Beetle]

PJ addressed your question, but that doesn't address the underlying
(and more critical) problem of your table structure. If your table
has fields like;

MANUF1
MANUF2
MANUF3
MANUF1_PN
MANUF2_PN
MANUF3_PN

then your table design is flawed, and no amount of coding/query/form
manipulation
is going to solve it. It is always going to cause you problems. The main one
being
that as soon as you need to add another manufaturer, you need to add more
fields
to your table, and subsequently go back and re-design every query, form and
report
that is based on that table!

Based on what you posted, it's hard to make a suggestion as to what your
table
structure *should* be, but if you want to post back with some more info
about the nature of your data, someone shoul be able to offer more advice.

_________

Sean Bailey