How to change properties in a linked table?

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

On Thu, 2 Dec 2004 15:45:02 -0800, "Robert Johnson"
wrote:

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

"John Vinson" wrote in message
...
On Thu, 2 Dec 2004 15:45:02 -0800, "Robert Johnson"
wrote:

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

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

Robert

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.

--

Ken Snell
MS ACCESS MVP

"Robert Johnson" wrote in message
...

"John Vinson" wrote in message
...
On Thu, 2 Dec 2004 15:45:02 -0800, "Robert Johnson"
wrote:

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

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

Robert

On Thu, 2 Dec 2004 16:59:25 -0800, "Robert Johnson"
wrote:

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

"Ken Snell [MVP]" wrote in message
...
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.

--

Ken Snell
MS ACCESS MVP

"Robert Johnson" wrote in message
...

"John Vinson" wrote in message
...
On Thu, 2 Dec 2004 15:45:02 -0800, "Robert Johnson"
wrote:

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

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

Robert





Thanks for the reply

"John Vinson" wrote in message
...
On Thu, 2 Dec 2004 16:59:25 -0800, "Robert Johnson"
wrote:

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

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

Robert