Lookup tables

[Martino165]

I have a table where I need specific lookup values. I am able to
lookup a single table, but am not able to conditional lookup multiple
tables.

Example:

tbl_Study_Info
Published_Flag (1 or 2) for "published" or "unpublished"
Reference_Info (if Published_Flag = 1 [published] lookup values in
table "lkptbl_Publish_Journal_list"; and if Published_Flag = 2
[unpublished]lookup values in lkptbl_Laboratory_Name")

Hope that is enough desc to get the point accross.. thanks

 

[John Vinson]

I have a table where I need specific lookup values. I am able to
lookup a single table, but am not able to conditional lookup multiple
tables.

Example:

tbl_Study_Info
Published_Flag (1 or 2) for "published" or "unpublished"
Reference_Info (if Published_Flag = 1 [published] lookup values in
table "lkptbl_Publish_Journal_list"; and if Published_Flag = 2
[unpublished]lookup values in lkptbl_Laboratory_Name")

Hope that is enough desc to get the point accross.. thanks

Well... first off, most serious developer NEVER use lookup fields in
tables, period.

See http://www.mvps.org/access/lookupfields.htm for a critique.

Secondly, table datasheets are of VERY limited utility and should not
be used for routine data interaction. Use Forms instead.

Finally, you can do this pretty easily on a Form (though you cannot in
a Table). In the AfterUpdate event of the PublishedFlag control (I'd
use an Option Group though a combo would work well also), you can
progrmatically assign the RowSource property of the second combo box:

Private Sub optPublishedFlag_AfterUpdate()
Select Case Me!optPublishedFlag
Case 1
Me!cboReferenceInfo.RowSource = "lkptbl_Publish_Journal_List"
Case 2
Me!cboReferenceInfo.RowSource = "lkptbl_Laboratory_Name"
Case Else
' reserved for possible expansion, e.g. web publishing
End Select
End Sub

You'll need similar code in the Form's Current event to select the
right combo box rowsource for existing records.

John W. Vinson[MVP]