ms access combobox related help

[rashu123]

hi
I am creating an application where in there are two comboboxes.one contains the customer name and the other thecustomer number.I want the comboboxes to work in such a way that if the user wish to search using customer name it should automaticaly fetch the customer number and display in other combobox and vice versa.I have succedded in fetching the corresponding customer number when the user enters customer name but i am unable to fetch the customer name when the user enters number.I have to do it simultaneously that is if the user wishes to enter or search through either the customer number or customer name the other data shoud appear automaticaly in the other combobox.plz help me with this.

 

[TheAceMan1]

How ae ya rashu123 . . .

What are the names of the comboboxes?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

the easiest way to to this is to have the same row source for both combos.

select customerID, customerName, .....


The first combo has its properties
column count:2
boundcolumn: 1
column widths:0;1

second combo is

column count:2
boundcolumn: 1
column widths:1;0

on the after update of each combo
me.combo1 = me.combo2
or
me.combo2 = me.combo1

For your search you pull the appropriate value from your combo by using the combo's column property.

 

[TheAceMan1]

Howdy MajP . . .

Just a heads-up ...

Your method was the 1st thing I tried. Problem is there's no way to set the value of the alternate combo. There's no [blue]Selected[/blue] property like that for a listbox and the [blue]ListIndex[/blue] property is [purple]read only[/purple]. Also, writing to the [blue]textbox portion[/blue] of the alternate combo raises an error (this is after setting focus to the alternate).

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

MajP's suggestion worked for me. You don't have to worry about a selected property.

I used MajP's solution but bound different columns of the Employee table in Northwind. This code kept the two combo boxes in synch and displayed either the EmployeeID or the LastName.

Private Sub cboEmployeeID_AfterUpdate()
    Me.cboLastName = Me.cboEmployeeID.Column(1)
End Sub

Private Sub cboLastName_AfterUpdate()
    Me.cboEmployeeID = Me.cboLastName.Column(0)
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

dhookom . . .

Not working in 2003 ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Got it working . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[rashu123]

hi all..
It works fine but the problem is that the customer name and customer number are in two different tables which have a common field called custmerID.I completed this using the double click and on click event of the two combo boxes such that if the name combobox is double clicked then it will show all the names so that user can select a name and corresponding number is displayed in the other combobox and similarly when customernumber combobox is double clicked it will display all the custmeraccount numbers and on selecting /inputting an account number corresponding name will be displayed in the first combobox.I am not sure whether this is the right way of doing it as i am completely new to access.Please suggest.

 

[MajP]

Does not matter if they come from two tables. If customer ID is the bound column then that is the value of the combobox, regardless of what is displayed. What I described originally will do this.