Populate combo box 2 based on combo box 1

I have researched this in the knowledge base and found answers but I am having trouble applying them.
The first  goal is to have a Part Name selected in my form and when that is chosen the corresponding part number shows in another combo box.
FYI, this is part one of this question once this works I want to know if you can go both ways. Pick a part number and it populates part name or part name and it populates part number.
So far for Goal 1 there is
frmISParts  it has
cboPartsName with row source of:
SELECT [tblPartsList].[plID], [tblPartsList].[plPartName] FROM tblPartsList ORDER BY [plPartName];
and
AfterUpdate event of:
Private Sub cboPartName_AfterUpdate()
Me.cboPartNo.Requery
End Sub

then we have cboPartNo
with a row source of:
SELECT ptPartNumber FROM tbPartsList WHERE ptPartNumber=Forms!frmISParts!cboPartName;

What am I missing to reach my first goal?
Thanks for your help!!
DeniseOlsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SheilsCommented:
Pretty loaded question for 250 points. Anyway try this:

Private Sub cboPartName_AfterUpdate()

Me.cboPartNo.RowSource="SELECT ptPartNumber FROM tbPartsList WHERE plPartName=" & Me.cboPartName
Me.cboPartNo.Requery

End Sub

0
koutnyCommented:
Is your form data bound? What I mean by that is do you store the selected partID in an underlying table?
If you do, then what you want to achieve is pretty easy: create two identical combo boxes bound to the same field (plID). Set the control source to pIID as well. Set the column widths so that the first combo box would show the ID while the second one shows the part name. When you make a change in one combo box the other one reflects the change automatically.
The row source of both combo boxes would remain the same as you have it now:
SELECT [tblPartsList].[plID], [tblPartsList].[plPartName] FROM tblPartsList ORDER BY [plPartName];
You would just use different sort order for the one showing IDs.

The first Column Widths property for the first combo box would be something like 2,544cm;0cm (or you can omit the partname field in the row source of this combo box), while the column widths for the second combo box would be 0cm;

If your form is not data bound, it is still quite easy. Use the same row source for both combo boxes as before (with just different sorting) and make then both bound fo the plID field (column 1). Set the column widths so that one combo box shows the ID while the second one shows the name. Then create On Click event handlers for both combo boxes in which you set the value for the other combo box. Something like this:

Private Sub cbo1_Click()
    cbo2 = cbo1
End Sub

Private Sub cbo2_Click()
    cbo1 = cbo2
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DeniseOlsonAuthor Commented:
Hi,
Koutny, thanks! The data is bound and your first option worked!
Denise
0
DeniseOlsonAuthor Commented:
Thanks for your help!
0
koutnyCommented:
Thanks for the points, I am glad you got it working.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.