Auto Fill field

[Fahad]

Hi

I'm using access 2007 and I have a form to enter the products info. the name
of the form is "frmProductInfo".

I have two fields that I want the 2nd field to be filled automatically if
the 1st field have certian value.
example:

1st field name: ProductFamily
2nd field name: ProductCat

if the 1st field value equal one of the following (value1 or value2 or
value3) then the 2nd field should have this value (value A)
if the 1st field value equal one of the following (value4 or value5 or
value6) then the 2nd field should have this value (value B)

1- could you please give me the code I should use to fill the 2nd field
automatically?
2- where should I put that code (i.e. on which field & Active should I put
it)? on 1st field on After Update action or on the 2nd field.

thanks...

 

[Mike Painter]

Hi

I'm using access 2007 and I have a form to enter the products info.
the name of the form is "frmProductInfo".

I have two fields that I want the 2nd field to be filled
automatically if the 1st field have certian value.
example:

1st field name: ProductFamily
2nd field name: ProductCat

if the 1st field value equal one of the following (value1 or value2 or
value3) then the 2nd field should have this value (value A)
if the 1st field value equal one of the following (value4 or value5 or
value6) then the 2nd field should have this value (value B)

1- could you please give me the code I should use to fill the 2nd
field automatically?
2- where should I put that code (i.e. on which field & Active should
I put it)? on 1st field on After Update action or on the 2nd field.

thanks...

No code is needed. The values for the second field should come from a
related table.

 

[Fahad]

Hi Mike

thanks for your reply...

actually the 2 fields are from different lookup tables and no relation
between the two tables. the tables are related with the main table.

how can I automate that?

 

[Mike Painter]

The value from one lookup table depends on the value from another lookup
table.
If you wish to ignore this relationship then you have a couple choices.
1 Add a field to one table and fill in "value B" for each of the rows.
2. Write code to do this on your form.

Select case is probably the best way.
Select Case Me!Field1
Case thisvalue, thatvalue, somethingelse
Me!Field2 = someValue
Case X, Y, Z
Me!Field2 = someotherValue
....
End Select

Using a proper realtional model will allow you to ask questions such as,
"What values in ProductFamily are of type ProductCat"
"How many of each type?"