How to change properties in a linked table?

[Robert Johnson]

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

 

[John Vinson]

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

 

[Robert Johnson]

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

 

[Ken Snell [MVP]]

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.

 

[John Vinson]

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

 

[Robert Johnson]

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.

Thanks for the reply

 

[Robert Johnson]

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