How to change properties in a linked table?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert Johnson - 02 Dec 2004 23:45 GMT

Hi all. I have several tables in my database and some are linked. I want
to modify Table1, field4 to have a lookup to another table I created
(Table2). However, the Table1will not let me make any changes to any of the
fields saying that you cannot modify a linked table.

So how do I do this?

TIA

Robert

Reply to this Message

John Vinson - 03 Dec 2004 00:34 GMT

>Hi all. I have several tables in my database and some are linked. I want
>to modify Table1, field4 to have a lookup to another table I created
>(Table2). However, the Table1will not let me make any changes to any of the
>fields saying that you cannot modify a linked table.
>
>So how do I do this?

Short answer: Open the backend database directly and make the change
there.

Longer answer. Don't. Lookup Fields are of *very* limited value and
have many disadvantages:

http://www.mvps.org/access/lookupfields.htm

Instead, create a Form bound to Table1, and put a Combo Box based on
Table2 on the form to edit and display the looked-up value. It is not
necessary to change the table design to do this.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Reply to this Message

Robert Johnson - 03 Dec 2004 00:59 GMT

> >Hi all. I have several tables in my database and some are linked. I want
> >to modify Table1, field4 to have a lookup to another table I created
[quoted text clipped - 19 lines]
> Tuesday 11am EDT - Thursday 3:30pm EDT
> http://community.compuserve.com/msdevapps

OK, how do I get the data from the combo box to the field in table1 that I
want to update?

Robert

Reply to this Message

Ken Snell [MVP] - 03 Dec 2004 01:59 GMT

The field that is to be edited should be in the query that serves as the
form's RecordSource. The combo box should use a Row Source query that
returns the different values that can be selected.

Signature

Ken Snell
<MS ACCESS MVP>

> > >Hi all. I have several tables in my database and some are linked. I
> want
[quoted text clipped - 26 lines]
>
> Robert

Reply to this Message

Robert Johnson - 03 Dec 2004 16:32 GMT

> The field that is to be edited should be in the query that serves as the
> form's RecordSource. The combo box should use a Row Source query that
[quoted text clipped - 36 lines]
> >
> > Robert

Thanks for the reply

Reply to this Message

John Vinson - 03 Dec 2004 04:09 GMT

>OK, how do I get the data from the combo box to the field in table1 that I
>want to update?

A Combo Box *gets* data from its Rowsource - Table2, or a query
sorting the records in Table2; and it *puts* data from the selected
record into its ControlSource field. If you have a Form based on
table1, just include the field that you want to update in the Form's
recordsource query (it will be there automatically if the table is the
recordsource); just use the Combo Box wizard to set the combo box's
control source to that field. No code or other action is needed.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Reply to this Message

Robert Johnson - 03 Dec 2004 16:32 GMT

> >OK, how do I get the data from the combo box to the field in table1 that I
> >want to update?
[quoted text clipped - 11 lines]
> Tuesday 11am EDT - Thursday 3:30pm EDT
> http://community.compuserve.com/msdevapps

Thanks for the reply... I understand now how it works.

Robert

Reply to this Message