ComboBox and AutoExpand

[MichaelSmith]

I'm encountering a problem with the AutoExpand feature in a ComboBox.

I've got a table of Countries (tblCountries) which is linked to a junction table called tblWrecks via it's primary key

tblCountries
CountryID (Primary Key)
Country Name

tblWrecks
WreckID (Primary Key)
VoyageID (Foreign Key) - unrelated for the moment
CountryID (Foreign Key)

I'm trying to create a combobox on a form for tblWrecks that allows me to type the cName field value into the form with AutoExpand working.

These are the properties I have set:

Row Source: tblCountries
Row Source Type: Table/Query
Bound Column: 1
AutoExpand: Yes
Column Count: 2
Column Width 1cm;8cm

AutoExpand is working but only if I type in numbers, how do I make it work if I type in the country's name instead?

I originally had this working because the relationship I created was via the Lookup Wizard in table design view. I've since read that establishing relationships using the Lookup Wizard can cause problems later down the line and have deleted all the original Lookups for tables and created normal relationships.

EDIT: Access version is 2013 if that helps!

 

[CJ_London]

you need to hide the first column so set column widths to 0cm;8cm

 

[MichaelSmith]

Thanks CJ_London I needed to do that and didn't realise it was necessary, I still had the issue afterwards though. This is what I needed to do to fix it completely in case anyone else has the same issue:

The list of countries I used was taken from a Wikipedia table and had some god awful formatting I didn't notice. It had a leading space of Char160 and the internal spaces for multiword countries were Char160.

Fixed the issue by copying the data into Excel and running this formula on it:

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

Then pasting over my original table.