Auto Fill field - No answer yet

[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. the 2 fields are from different lookup
tables and no relation between the two tables. the tables are related with
the main table.

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...

 

[Linq Adams via AccessMonster.com]

Assuming that all "values" are text:

Private Sub ProductFamily_AfterUpdate()

Select Case Me.ProductFamily

Case "value1", "value2", "value3"
Me.ProductCat = "valueA"

Case "value4", "value5", "value6"
Me.ProductCat = "valueB"
Case Else
Me.ProductCat = ""

End Select

End Sub

 

[Mike Painter]

You have defined a relationship between them when you say that if one field
has a value x then other must have some value Y.
You should relate ProductFamily and ProductCat with a one to many and relate
the one side of that to your main table.

 

[Fahad]

Dear Linq Adams & Mike

thank you very much for your help...

the code provided works fine.

many thanks