i have a database access 2007, with external tables on the server that i need to make a lookup field in...i want to refer to a query but can't work it - in the external tables or the main userinterface...what to you do here?

i have a database access 2007, with external tables on the server that i need to make a lookup field in...i want to refer to a query but can't work it - in the external tables or the main userinterface...what to you do here?

What we do is NOT use lookups at table level. You use them on FORMS using combo boxes or list boxes, but you don't define them at table level.

See here for why. (http://www.mvps.org/access/lookupfields.htm)

so i create an unbound combo and fill it with a sql/vba query?

so i create an unbound combo and fill it with a sql/vba query?

not an unbound combo, you bind it to the field (ID) that you are storing in the table and the combo's rowsource is from whatever other table you are looking something up (selecting the ID and the description and then having the bound field be the ID field but the part that shows is the description).

i managed to do that, control source is the table field, rowsource is the query, it runs fine, lets you enter the data and fails to save to the table, wierd?!? i mean, the control source it the table field, any ideas?

i managed to do that, control source is the table field, rowsource is the query, it runs fine, lets you enter the data and fails to save to the table, wierd?!? i mean, the control source it the table field, any ideas?

Make sure that the query that is the record source for the form itself is updateable. See here (http://allenbrowne.com/ser-61.html) for what can cause that. If you still can't figure it out, perhaps you can post a copy of the database here for us to look at.

lol made an extra field to link to when one was there already. going to wrong field, thanks for all the help.