Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In a form I am creating for an MS Access 2010 database, one of the fields represents a foreign key. I would like this field to be displayed as a text box, rather than as a combo or list box as is usual for foreign keys (the main reason being that the lists would become far too long and uncomfortable to use, and I would rather have users enter the key directly). However, I cannot get it to work so that I can directly enter a foreign key in that text box. Instead of taking the foreign key I entered and following the reference to the existing tuple in the other relation, Access always seems to try and create a new tuple in the other relation, which of course fails since a tuple with an identical key already exists (i.e., the one I tried to reference).

To put it more practically, the problem I am having is this: I have a row with ID 100 in table A. I want to create a new row in table B, with a foreign key of 100 used to refer to that row in table A. The table relation is correctly set up in Access. If I edit table B in spreadsheet view, I can directly enter the foreign key of 100, and everything works as I want it to. However, if I do the same thing on the form Access complains about a duplicate key, because instead of simply storing the foreign key of 100 in table B, it also tries to create an entirely new row with the now duplicate ID of 100 in table A. I hope this is more or less understandable.

I tried declaring the foreign key field in table B as a combo lookup field, but unless I also change the field on the form from a text to a combo box, the problem persists. Googling also led me to try declaring the following as the data source for the foreign key text box on the form:

=DlookUp("[ID]", "[TableA]", "[ID]=" & Forms![Form2]![txtID1])

but I only ever get a syntax error out of this. Also, the Access help files only cover the DlookUp function up to version 2007—is it possible that this function is no longer available in Access 2010?

How can I allow users to enter a foreign key directly into a text field on a form, without Access trying to create a new row in the referenced table? Would the data source code above solve my problem if I could get it to work?

share|improve this question
It sounds to me that your record source for the form is pointing to table A when it should be using Table B – wbeard52 Jul 23 '12 at 20:00
Thanks for the suggestion. I just went to check, but the record source of the form is correctly set to relation B. – Daniel Saner Jul 24 '12 at 13:02
Which table are you trying to add data to? Is it table A or table B? – wbeard52 Jul 24 '12 at 19:11
Table B. I would like the new tuple in B to reference an existing tuple in A via a foreign key. As stated in the question, this works if I enter the foreign key directly in B's table view, but not if I enter it into the same field using a text box on a form. – Daniel Saner Jul 25 '12 at 13:57
up vote 1 down vote accepted

Ok. I understand what you are trying to do now. There are two options available to you depending on what you want to do.

Option 1: I think this is the more elegant solution. Create a query that joins the two tables together. If allowed, the new query will be editable and you can pull out the information you seek in Table A and place directly into the textbox. See this website to determine why your new query may be read only.

Option 2: Continue to use the combo box on the form and create a picture that will mask the down arrow of the combo box.

share|improve this answer
Option 1 is a good workaround. I still think it should be possible to make the form's textbox behave like the cell in spreadsheet view, but I can use this method to accomodate end-users. Thank you! – Daniel Saner Oct 16 '12 at 4:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .