Access 2000 Auto Lookup

[Ginger Myers]

I have created a query so that data will automatically be
filled in in the foreign table (many side relationship)
from the primary table (one-side relationship). It works
very well, however, I want the option to change the data
in the foreign table without it updating the primary
table. I do not have referential integrity, cascading
updates or cascading deletes selected in the
relationship. Is there a way to do this?

 

[Arvin Meyer]

I have created a query so that data will automatically be
filled in in the foreign table (many side relationship)
from the primary table (one-side relationship). It works
very well, however, I want the option to change the data
in the foreign table without it updating the primary
table. I do not have referential integrity, cascading
updates or cascading deletes selected in the
relationship. Is there a way to do this?

In Access, everything takes place using events. There are no events on
tables. You must use forms, and/or controls on forms, to execute the events
you need to accomplish your goal.

There are several ways you can accomplish your goal. The easiest, IMHO, is
to use an unbound combo box for the lookup, with multiple columns in the
combo. Then use the AfterUpdate event of the combo to push the data into the
textboxes bound to the form which is bound to your second table. The column
index is 0 based, so a combo with the following information would look like:


cboPerson
Column(0) PersonID
Column(0) PersonID
Column(0) PersonID

 

[Arvin Meyer]

Excuse the premature deployment of the Send button. I'll repeat (and
correct) the previous information:

In Access, everything takes place using events. There are no events on
tables. You must use forms, and/or controls on forms, to execute the events
you need to accomplish your goal.

There are several ways you can accomplish your goal. The easiest, IMHO, is
to use an unbound combo box for the lookup, with multiple columns in the
combo. Then use the AfterUpdate event of the combo to push the data into the
textboxes bound to the form which is bound to your second table. The column
index is 0 based, so a combo with the following information would look like:


cboPerson
Column(0) PersonID
Column(1) PersonName
Column(2) PersonPhone

The AfterUpdate event would look like:

Sub cboPerson_AfterUpdate()

Me.txtPersonName = cboPerson.Column(1)
Me.txtPersonPhone= cboPerson.Column(2)

End Sub

Now, txtPersonName and txtPersonPhone are fully editable without changing
the table underlying cboPerson, and if you reselect cboPerson, it will reset
the data.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access