How to cross-reference using a lookup field/table?

[greeni91]

I am currently in the process of creating a database to record problems at my work.

I am trying to AutoFill a field (Material Type) if another field (Material) has a number exactly like the primary key in my lookup table. e.g. If I input the number "6046" in the Material field I want it to populate the Material Type field with "Stainless Steel".

I have setup my lookup table with the number relating to the material type as the primary key... This is a Rolls Royce code which is an MSRR number relating to a specific material.

I was wondering if anyone could help, I have tried several methods to no avail, some even causing referential damage to the database.

Any help is greatly appreciated.

/Sandy

[missinglinq]

If Material is defined as a Number Datatype:

Private Sub Material_AfterUpdate()
 Me.MaterialType = DLookup("MaterialType", "MaterialTableName", "[Material] = " & Me.Material)
End Sub

If it's defined as Text:

Private Sub Material_AfterUpdate()
 Me.MaterialType = DLookup("MaterialType", "MaterialTableName", "[Material] = '" & Me.Material & "'")
End Sub

Linq ;0)>

[NeoPa]

If you are truly talking about a field and not a control then I recommend you do not store it separately at all. This is related to the very basics of Normalisation. See Normalisation and Table structures.

[greeni91]

Thanks Missinglinq,

I used your code for Material defined as a number and changed the MaterialTableNa me and it worked no bother...