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?