By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

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

greeni91
P: 61
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
Mar 4 '10 #1

✓ answered by missinglinq

If Material is defined as a Number Datatype:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Material_AfterUpdate()
  2.  Me.MaterialType = DLookup("MaterialType", "MaterialTableName", "[Material] = " & Me.Material)
  3. End Sub
If it's defined as Text:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Material_AfterUpdate()
  2.  Me.MaterialType = DLookup("MaterialType", "MaterialTableName", "[Material] = '" & Me.Material & "'")
  3. End Sub
Linq ;0)>

Share this Question
Share on Google+
3 Replies


missinglinq
Expert 2.5K+
P: 3,532
If Material is defined as a Number Datatype:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Material_AfterUpdate()
  2.  Me.MaterialType = DLookup("MaterialType", "MaterialTableName", "[Material] = " & Me.Material)
  3. End Sub
If it's defined as Text:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Material_AfterUpdate()
  2.  Me.MaterialType = DLookup("MaterialType", "MaterialTableName", "[Material] = '" & Me.Material & "'")
  3. End Sub
Linq ;0)>
Mar 4 '10 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 4 '10 #3

greeni91
P: 61
Thanks Missinglinq,

I used your code for Material defined as a number and changed the MaterialTableName and it worked no bother...
Mar 8 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.