auto populate a field based on value of other fields

auto populate a field based on value of other fields

Post by c2Ft » Fri, 26 Jun 2009 13:07:01



I have a data sheet where I want to auto populate a field (that has specific
values listed in a drop down menu) based on two other fields (both are
dropdown menus with specific values)

For Eg.:
there are 3 fields A, B, C and all these three fields have specific values
which we can select through a drop down menu. Now, Lets say values in the
fields are as follows:

A: 1, 2, 3, 4, 5
B: 1, 2, 3, 4, 5
C: a, b, c

So now, If I select '1' for A and '1' for B then I DONT want any thing to
populate in C and leave it blank.
If I select '1' for A and '2' for B then I want 'a' to be populated in C
If I select '2' for A and '1' for B then I want 'b' to be populated in C
If I select '1' for A and '1' for B then I want 'c' to be populated in C

Basically,
If the value in field A is smaller then field B then I want 'a' to be
populated in C.
If the value in field B is smaller then field A then I want 'b' to be
populated in C.
If the value in field A and B are equal then I want 'c' to be populated in C.

Hope I made it clear enough.

Thanks in Advance.
 
 
 

auto populate a field based on value of other fields

Post by Steve Scha » Fri, 26 Jun 2009 13:25:21


Sam,

In a nutshell... you should remove field C altogether. It is invalid, as it
in breach of database design principles. The reason for this is that when
you know the value of both A and B, the value of C is automatically known,
so it is not correct to store this redundantly in your table.

Instead, this value can be very easily calculated whenever you need it, for
your purposes on form or report. This can be done as a calculated field in
the query that the form or report is based on, or else in the Control Source
of a calculated control on the form or report itself.

--
Steve Schapel, Microsoft Access MVP







__________ Information from ESET Smart Security, version of virus signature database 4186 (20090624) __________

The message was checked by ESET Smart Security.

http://www.yqcomputer.com/