Combo Box FK Data Type Error

[southcraven]

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.

[Rainlover]

I think your problem is the Control Source of the Combo Box.

I believe it should be the FK of the table you are writing to. i.e. The FK from the Control Source of the Form.

[southcraven]

The form is based on the tbl_coords table, the combo box is controlled by the SELECT tbl_BMark_Type.Benchmark_Code,tbl_BMark_Type.Bench mark_Code,tbl_BMark_Type.Benchmark_Survey_Method FROM tbl_BMark_Type;

Now even I can see the error here, tbl_BMark_Type.Benchmark_Code is repeated twice.

The first tbl_BMark_Type.Benchmark_Code should be tbl_BMark_Type.BM_TypeID

Which gives the select query as
SELECT tbl_BMark_Type.BM_TypeID,tbl_BMark_Type.Benchmark_ Code,tbl_BMark_Type.Benchmark_Survey_Method FROM tbl_BMark_Type;

The question is what did I do wrong inthe first place!!!!

[Rainlover]

The Control Source of the Combo must be from the same Record Source as the form.

The Control Source should not be a SQL statement. It should be the FK from the Form's Record Source.

The Row Source is where you place the SQL that looks up the Value in the other Table.

[Rainlover]

Qusetion

Have you set up a lookup in your Table? If so then get rid of it.

[southcraven]

Rainlover; even though I have found my error could you please explain your last post......

why should I NOT create a lookup in my table, using the FK? Then using a wizard to base a form on this table.

what is the difference from this and creating a combo box in a form from scratch? Surely the SQL to find the value is the same?

And thanks for your help.

[Rainlover]

Have a look at this link.

It explains things quite nicely. Then you can make up your own mind from there.

[Rainlover]

http://access.mvps.org/access/lookupfields.htm