Combo Box Record Source from the Same Table as the Form

  • Thread starter Thread starter Larry Lutz
  • Start date Start date
L

Larry Lutz

I'm using Access 2003, but have to keep in 2000 file format for
compatibility purposes. I have a field in a table that needs to store the ID
(autonumber) of one and only one other record in the same table. The ideal
thing would be to use a combo box, but try as I might, Access will not let
me put a combo box on a form whose record source is the same as the table
the form is built on. I've tried building the combo box on a query and on a
SQL string; I've tried building the form on a query. No matter what I do,
Access catches me and refuses to allow a combo box record source that has
anything to do with a table involved with the form.

This is a strictly one-to-one relationship, a kind of self-join, so using a
subform just seems way to clumsy, especially for a form that already has its
share of subforms.

Is there any way to build such a combo box, one that refers back to the
table on which the form is built?

Larry
 
Presumably you have a field in your table which is a foreign key to the
primary key of the same table?

If so, there should be no problem setting the RowSource of the combo to the
same table as the RecordSource of the form. Naturally it's ControlSource
will be the foreign key field, not the primary key field.

Not sure what you mean by Access is stopping you from doing this. Perhaps
you need to release the Wizard in the Toolbox, so that when you go to place
the combo on the form, there is no Wizard trying to figure out how to do
this for you.

You could also try creating a query that returns just the fields you need
for your combo, and use that as the RowSource, instead of the table name.
 
Back
Top