Populate a Combo Box

[rollover99 via AccessMonster.com]

I have a bound fill-in type form with that will use 2 unbound combo boxes
from a query to populate some of the forms information. The first combo box
is CustomerID and the second is CustomerName. Some end users will enter the
ID and others will enter the Name. When they select say ID the Name will
fill with the correct name. The combo boxes both have the same fields ID and
Name, just that only the one I want to see is showing (width set to zero). I
know this is an AfterUpdate function but I am not to savy in VB to perform
this.

Any help from the Access monster would be helpful.

 

[Guest]

Provided both combo boxes are both bound to the ID column, I think you would
just need something like:

Private Sub Combo1_AfterUpdate()
Combo2=Combo1
End Sub

Private Sub Combo2_AfterUpdate()
Combo1=Combo2
End Sub

Jon.

 

[rollover99 via AccessMonster.com]

I placed the code into the AfterUpdate for both combo boxes.

When I select the first combo box info it places the ID in the Name box.

I am using this for the row source and the bound column is 1 for ID and 2 for
Name. Is it correct or should i being doing it different.

SELECT qryCompany.COMPANYID, qryCompany.COMPANYNAME FROM qryCompany ORDER BY
[COMPANYID];

SELECT qryCompany.COMPANYID, qryCompany.COMPANYNAME FROM qryCompany ORDER BY
[COMPANYNAME];

So as of right now the combo1 is truely equal to combo2. I need to select
the ID and the Name populates in the combo2 or I select the Name from combo2
and then ID populates.

Thanks

 

[Guest]

Hi again,

From the symptoms you have described it sounds as though the Bound Column
property for Combo2 is set to 2 instead of 1. I would recommend binding both
combo boxes to the ID column, (i.e. set the Bound Column to 1 for both of
them). However, if you actually need Combo2 to be bound to the name column
then you should modify your code to read:

Private Sub Combo1_AfterUpdate()
Combo2=Combo1.Column(1)
End Sub

Private Sub Combo2_AfterUpdate()
Combo1=Combo2.Column(0)
End Sub

Regards,

Jon.

 

[AccessVandal via AccessMonster.com]

rollover99,

For a non-continuous form, I don’t see the need of having a
second combo box, since “COMPANYID” is unique and
is assigned to it.

Just change the second combo box to textbox.

And use the control Combo1 to populate the second.

Private Sub Combo1_AfterUpdate()
Me.Combo1=Combo1.Column(0) ‘CompanyID
Me.Combo2=Combo1.Column(1) ‘CompanyName
End Sub

In the control properties of Combo1 – Format Tab, set
Column Count = something like 2 - columns Company ID and Name
Column Width = something like 0.5;1 which is column/field size
List Width = something like 2” which is the view size

 

[rollover99 via AccessMonster.com]

Jon Ley

Works great. Thanks for all your help on this.