Combo box

[stepone]

Hi there,

I have a combo box displaying 2 columns - drug name and BNF code. The BNF code is the field I want to store and display in the combo box. But the user wants to be able to type the first letter of the drug name to quickly move to the correct entry.

As far as I can see, the only way to get the Drug column to react to typing the first letter, is to make it the first column in the combo drop-down. Unfortunately that means the drug name will be displayed in the combo box after selection, rather than the BNF code.

Is there a clever way to do this ? (I can see a way around it having an unbound combo box selecting drug name, which then populates the field holding BNF code, but something tells me there must be a better way)

Thanks,
Peter.

 

[stepone]

Hello - is anyone able to help with the problem above. I've searched everywhere but cannot find a solution.

Basically I want to know if auto-complete can be set to work on column 2 of a combo-box, but I want column 1 to appear in the box, so I cannot set its width to 0.

 

[KenHigg]

Is the combo box bound to a field in a table?

 

[stepone]

Hi there - yes, the combo box is bound to a field. The field stores the BNF code, and the user would like to to display the BNF code. The problem is that for the auto-type thing to work on the drug name, the drug name needs to be column 1, and cannot be width zero, which means the drug name is displayed in the combo box after selection, rather than the BNF code.

Is there a way to do this ?

Thanks,
StepOne

 

[The_Tic]

Hi there - yes, the combo box is bound to a field. The field stores the BNF code, and the user would like to to display the BNF code. The problem is that for the auto-type thing to work on the drug name, the drug name needs to be column 1, and cannot be width zero, which means the drug name is displayed in the combo box after selection, rather than the BNF code.

Is there a way to do this ?

Thanks,
StepOne

Stepone,

This should work for you :

Concatenate the two fields using a dash (“-“) as a separator in the rowsource property:

Select Trim(Drug_Name) & “-“ BNF As Drug_Description
From TableName


Now, they can type the drug name and position themselves directly on that particular dru. In addition, theyn will see the BNF. It’s the best of both worlds. Finally, using the VBA Mid$() Function (place it it then after update property), you can pick extract the BNF from the Drug_Description and place it into the BFN into your table.

EX:

Assuming the drug description is 25 characters (remember the dash is in position 26) and the BNF is 4 characters

BNF = Mid$(Drug_Description,27,4)