Adding a field linked to another database

[Guest]

I have a master table consisting of a column of unique ID numbers and a
column of comments. This is populated by queries linked to several other
databases.

Within each individual database there is a table with the unique ID number &
columns containing other information.

The individual databases are regularly cleared and repopulated, thus they
cannot contain the comments information as it would be lost during clearing.

Within each individual database I'm trying to build a form which contains
the table information and uses the unique ID number to bring in the
corresponding comments column from the linked master table.

I have tried this as a subform without success and as a text box with the
master table comments field as the control source and a validation rule that
the IDs must be equal. However, the information in the master table was not
displayed in the form, nor could I edit the field (it was not locked).

If anyone can walk me through setting this up, or has any advice, it would
be much appreciated, thanks!

 

[Guest]

Build the query first, before building the form that displays the information
you want to view and edit.

First, create a new query and type something like the following into the SQL
View pane:

SELECT tblMaster.Comments, tblStuff.OtherInfo, tblStuff.MoreInfo
FROM tblStuff INNER JOIN tblMaster ON tblStuff.ID = tblMaster.ID;

.. . . where tblMaster is the name of the master table, tblStuff is the name
of the table that holds the other information, ID is the unique ID that is
common to both tables, Comments is the name of the field holding comments in
the master table, and OtherInfo and MoreInfo are the names of fields in the
tblStuff table. (Feel free to add as many fields from the tblStuff table as
you need. Also feel free to use the QBE designer in Design View to drag the
necessary fields into the query grid.)

Save the query and name it. Next, create a new form using the Form Wizard.
Use this query as the data source. The Wizard will create fields on the form
bound to each of these fields, and they'll be editable.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Unfortunately I'm unable to do it this way as I need to add the editable
field to a pre-existing form.