• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

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!!
0
DeniseOlson
Asked:
DeniseOlson
  • 2
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now