I keep getting this problem whenever I create a Combo Box based on a FK related table. Error message; The value you entered isn't valid for this field. In the design of the main table tbl_Coords, the primary key PK is an autonumber. Similarily in the table with the related FK tbl_BMType, the PK field is autonumber.
In the main table tbl_Coords, I am using the usual Access procedure; PK(autonumber) , FK(LongInteger), which is same as the PK in the tbl_BMType. This table tbl_BMType has 3 fields; field 1 = PK autonumber, field 2 = text, field 3 = text. I have made sure the field sizes are sufficient, etc.
In the design of the main table, with the related FK, the field is formatted at LongInteger......all works fine BUT the relationship is many to one all works fine.
When in the tbl_Coords table design I select Lookup then I choose BM_Type field (long Integer) and the ComboBox; TableQuery and then Select tbl_BMType.field1.field2.field3 FROM (I am not filling in the SQL correct on purpose as cannotcopy paste - different computer) anyway the problems is the bound column, column count, column widths. I have bound column 1, count 3, widths 0cm;2cm;4cm.
You may ask why I need to show the 2 columns in my combo box, simply because I have many different types of codes, these codes are hardwired in another GIS database, which some of my guys dont use and therefore to ensure they select the correct code I need to show a description field to help them.
So in the combo box when I click the drop down, I can see the code & description, which are both text.....when I select the code I want I get the above error.
Please help.