Double Dependant Lookup

[zorro19s]

I am new at access but I am trying to get a double lookup.
What I mean is that on one table I have Part# and Part Name
On the other table I want to have the option to lookup eather the Part# or Part Name but if I select Part# the Part Name will appear automatically and if I select Part Name the Part# will show up automatically.

I would really appriciate if someone would help me

 

[AccessGuru]

You can use the LinkChildField property in code to put up a connection to either the part # when that is selected, or part name when that is selected. How:

You put two combo boxes on the main form (not subform), and a subform on the main form below with the list you need. Connect them, say, by part number first: on the form, click once on the subform, go to properties of the subform, enter the Control name (not column or field name) to link to in the LinkMasterFields property, and LinkChildFields property. (use semi-colons for multiple fields, but this case you only need one).

Then you let the user select the part no. and display matching results (it will be auto with the linked forms). When they select a part name, on the before update property, use code to chg the LinkChildFields property, and LinkMasterFields property to partno instead, and then the matching part names will be listed. Don't forget, when they select part # again, you must link the form back!

If you encounter a problem, use a button to do the switching of the linked forms, like "Search by part. No" and when they click that, set the correct link required (by part no) and then allow them to access the part no combo and hide the other part name combo, and do the query.

Another easier way is to use two forms, search by a or b. Less coding, simpler maintenance, but I find sub-form linking (although not recommended in Access manuals) a very fast search and match method, and when used carefully, makes up for the missing "Seek" command which is only available in not-linked tables (very limiting, most Access databases use linked tables)