Update Table or Form Field Based on Previous Field

[Mike]

I'm sure this has been covered before in the newsgroup but have had no
luck locating it.

I have two tables: InstMonitors & MonModDesc

Structure of MonModDesc:
Model, Description

Structure of InstMonitors:
CompName, Make, Model, Description, Serial, PurchDate, WarrExpDate

The MonModDesc table is simply a listing of the Monitor Model Numbers
and associated Description with that Model Number.

The InstMonitors table is a tracking of activity of the specific
monitor: CompName is the name of the computer it is assigned to. Make
is the brand name of the Monitor. Model is selected from the
MonModDesc table. Serial is the serial number of the monitor.
PurchDate is the purchase date of the monitor, WarrExpDate is the date
the warranty expires on the monitor.

What I am trying to do is:
When a user is in the InstMonitors table, and they select a Model
(coming from the ModModDesc Table), I would like the Description
associated with that Model from the MonModDesc table to flood in to
the Description field in the InstMonitors table.

I am thinking from researching that this may not be able to be done in
a table and will have to be done in a form, which is absolutely fine
because a user won't be entering data directly into the table, they
will be using a form.

Thanks,
Mike

[Brian]

"Mike" <mb********@charter.net> wrote in message
news:70*************************@posting.google.co m...

I'm sure this has been covered before in the newsgroup but have had no
luck locating it.

I have two tables: InstMonitors & MonModDesc

Structure of MonModDesc:
Model, Description

Structure of InstMonitors:
CompName, Make, Model, Description, Serial, PurchDate, WarrExpDate

The MonModDesc table is simply a listing of the Monitor Model Numbers
and associated Description with that Model Number.

The InstMonitors table is a tracking of activity of the specific
monitor: CompName is the name of the computer it is assigned to. Make
is the brand name of the Monitor. Model is selected from the
MonModDesc table. Serial is the serial number of the monitor.
PurchDate is the purchase date of the monitor, WarrExpDate is the date
the warranty expires on the monitor.

What I am trying to do is:
When a user is in the InstMonitors table, and they select a Model
(coming from the ModModDesc Table), I would like the Description
associated with that Model from the MonModDesc table to flood in to
the Description field in the InstMonitors table.

I am thinking from researching that this may not be able to be done in
a table and will have to be done in a form, which is absolutely fine
because a user won't be entering data directly into the table, they
will be using a form.

Thanks,
Mike

You don't need the Description field in the InstMonitors table. It merely
duplicates data that is already in the MonModDesc table, and as such
contravenes the basic principles of data design.

So, remove that field! Then, create yourself a form bound to the
InstMonitors table. Add a text box to this form for the Description, and
set the Control Source for this text box to the following:

= Dlookup("Description","MonModDesc","Model = " & [Model])

This assumes that Model is a numeric field. If it is text, do it like this
instead:

= Dlookup("Description","MonModDesc","Model = """ & [Model] & """")

[Mike]

BEAUTIFUL. Thanks! Works Like a Dream!

"Brian" <bc**@IHATESPAMclara.co.uk> wrote in message news:<10****************@echo.uk.clara.net>...

"Mike" <mb********@charter.net> wrote in message
news:70*************************@posting.google.co m...

I'm sure this has been covered before in the newsgroup but have had no
luck locating it.

I have two tables: InstMonitors & MonModDesc

Structure of MonModDesc:
Model, Description

Structure of InstMonitors:
CompName, Make, Model, Description, Serial, PurchDate, WarrExpDate

The MonModDesc table is simply a listing of the Monitor Model Numbers
and associated Description with that Model Number.

The InstMonitors table is a tracking of activity of the specific
monitor: CompName is the name of the computer it is assigned to. Make
is the brand name of the Monitor. Model is selected from the
MonModDesc table. Serial is the serial number of the monitor.
PurchDate is the purchase date of the monitor, WarrExpDate is the date
the warranty expires on the monitor.

What I am trying to do is:
When a user is in the InstMonitors table, and they select a Model
(coming from the ModModDesc Table), I would like the Description
associated with that Model from the MonModDesc table to flood in to
the Description field in the InstMonitors table.

I am thinking from researching that this may not be able to be done in
a table and will have to be done in a form, which is absolutely fine
because a user won't be entering data directly into the table, they
will be using a form.

Thanks,
Mike

You don't need the Description field in the InstMonitors table. It merely
duplicates data that is already in the MonModDesc table, and as such
contravenes the basic principles of data design.

So, remove that field! Then, create yourself a form bound to the
InstMonitors table. Add a text box to this form for the Description, and
set the Control Source for this text box to the following:

= Dlookup("Description","MonModDesc","Model = " & [Model])

This assumes that Model is a numeric field. If it is text, do it like this
instead:

= Dlookup("Description","MonModDesc","Model = """ & [Model] & """")