Office 97

[Bruce]

I have an Access 97 database with two Tables on has only two Fields
"Officer" "Location" I use this Table in a Combo Box for the Officer to
enter their name into the second maindata table. What I now need to do is
that when the Officer selects his name from the first table I want the
infornmation of thet're Location to be automatically put into the Location
Field in the maindata table.

Can anyone help?


Best Wishes
Bruce

 

[Vincent Johns]

I have an Access 97 database with two Tables on has only two Fields
"Officer" "Location" I use this Table in a Combo Box for the Officer to
enter their name into the second maindata table. What I now need to do is
that when the Officer selects his name from the first table I want the
infornmation of thet're Location to be automatically put into the Location
Field in the maindata table.

Can anyone help?


Best Wishes
Bruce

I probably woudn't do it this way (preferring to select names from an
approved list, instead of typing them in), but here's one way to
proceed. It's not perfect -- the names must already exist in [Maindata]
for the locations there to be updated. But I think it does pretty much
what you describe.

Suppose [Locations] contains only the fields [Officer] and [Location],
both of which are text fields, and that [Maindata] contains those fields
among others. Then your Form might contain a Combo Box whose Control
Source property is the [Officer] field, and a Text Box whose Control
Source property is the [Location] field and whose After Update Event is
the [M_UpdateLocation] Macro, defined as follows:

SetWarnings: No
OpenQuery: Q_UpdateMaindata
SetWarnings: Yes

and the Query is defined as

[Q_UpdateMaindata] SQL:
UPDATE Maindata
SET Maindata.Location = [Forms]![F_Locations]![Location]
WHERE (((Maindata.Officer)=[Forms]![F_Locations]![Officer]));

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Pat Hartman\(MVP\)]

It is generally not necessary to store non-key data. All you should need to
store is the officer's ID. Then you can join the tables on OfficierID to
obtain the "lookup" data. In the off chance it is necessary to store the
location also, it only takes a single line of code but first you need to
modify the RowSource of the officer combo to make sure it includes Location.
Then in the AfterUpdate event of the combo:
Me.Location = Me.YourCombo.Column(1)

The columns of a combo are a zero based array, so .Column(0) refers to the
first column, (1) to the second, (2) to the third etc. So if Location isn't
the second column, be sure to change the column index. Also, if you needed
to add a column to the RowSource, don't forget to fix the column count and
column widths properties of the combo.

You can determine if storing the location is necessary by answering a single
question. What do you want to happen if the location associated with the
officer changes? If you want the location to change in the "other" table,
then you must NOT store the location. You would always use a query so you
can obtain the most recent location. If you always want the location to be
whatever the location was at the time the record was saved, then you would
need to duplicate the data.