Combo Box Concentantion - Searching

[egrant]

Does anyone know if you can search on a concentation of a combox.

I have a database with Customer Names and thier Companies and I want one combo box to have both of the fields so maybe I can do this in the query...

EXP: [NAME] & " " & [COMPANY]

I want to be able to start typing in my combo box and the autoexpand will find not only the NAME but the COMPANY aswell. For intance... if I have a customers name "JOHN SMITH" and another customers company called "JOHNSON PLUMBING" I want the combo box to find both of the records when I start typing becuase they both begin with JOHN.

Thanks if anyone can help me.

Emma

 

[LittleSmudge]

If you set your combo box up with :-


RowSource "SELECT SupplierId, Name, Company FROM tblSupplier ORDER BY Name"
ColumnCount = 3
ColumnWidth = 0;
BoundColumn = 1
AutoFill = True

Then you can dropdown the list and start typing the [Name] the combo will auto fill the name and display the company name beside it as well
When the combo box is collapsed it will just display the [Name] part. However, the 'value' that the control appears to hold will be the SupplierId

You could also get the Company info displayed on an adjacent text box control it you want to by adding code to the comboBox's AfterUpdate event like this:-

Private Sub cboPickSupplier_AfterUpdate()
txtCompanyName = cboPickSupplier.Column(2)
End Sub

REM : In Column(Index) the Index is a Zero based counter.



'ope-that-'elps.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[egrant]

HMMMM... Thanks for your info but I don't think that I explained what it is that I am trying to do clearly...


I want the combo box to have CUSTOMER NAME and COMPANY in the same column so that I can search on either one of them. Even though they are in the table as seperate fields.

so if I start type JOHN the combo box starts displaying...

Johnson Plumbing <--- COMPANY
Johnsone Hardware <--- COMPANY
John Smith <--- CUSTOMER NAME
John Doe Mowing <--- COMPANY
Joker Valance <--- CUSTOMER NAME

etc...

Thanks for your reply LittleSmudge

 

[egrant]

oops I just realised... JOKER does not contain JOHN but you get what I mean I am sure

- Emma

 

[LittleSmudge]

Then make the RowSource

RowSource &quot;SELECT SupplierId, Name & &quot; &quot; & Company As NameCo FROM tblSupplier ORDER BY Name&quot;
and the Column Count will now be 2 rather than 3



'ope-that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.